1. customer表定义如下
CREATE TABLE 'customer'(
'custid' int(10)NOT NULL,
'custname' varchar(100) NOT NULL,
'date' datetime default NULL,
'money' int(10)default NULL,
PRIMARY KEY ('custid'),
KEY 'index_customer_custname' ('custname),
KEY 'index_customer_custname_union' ('money', 'date', 'custname')
)
secondinfo表定义如下:
CREATE TABLE 'secondinfo'(
'secid' int(10) NOT NULL,
'firstid' int(10) NOT NULL,
'custid' int(10) default NULL,
PRIMARY KEY('secid')
KEY 'Index_secondinfo_custid' ('custid')
下列SQL执行速度比较慢,请分析原因并做优化(20分)
SELECT * FROM customer WHERE substring(custname, 1, 4)='beizhi';
SELECT * FROM customer WHERE money/30<1000;
SELECT * FROM customer WHERE custname=3721;
SELECT FROM customer WHERE custname <>'mike';
SELECT * FROM secondinfo s WHERE s.custid NOT IN(SELECT c.custid FROM customer c);
select * from customer where money<1000 union select * from customer where date >'20080101';
|