Mysql查询大数据表的执行时间优化

Question

I am executing a mysql query for searching car information from a table having 530399 records

for Executing query it is taking so much time

SELECT c.* FROM CarInfo as c WHERE (c.Vehicle_Year<='2014') and c.Vehicle_Age_Type='USED' limit 0,15 . 

I need all the fields from table so using * .

My table have 36 columns . Is there any way to optimize this query .

After adding index it is loading fast with limit but its taking time when trying to use total count

SELECT count(*) as total FROM CarInfo as c WHERE (c.Vehicle_Year<='2014') and c.Vehicle_Dealer_Zip in(85320,85354,85541) and (c.Vehicle_age_type='New' or c.Vehicle_age_type='Used' or c.Vehicle_age_type='Certified Used')

Dealer_Zip may contain so may values.

Thanks in advance.

Answer 1

Looks like your table is missing the indexes and if yes you need to add them first.

Before adding the index first check if its already there using the following command

show indexes from CarInfo

From the above command see if Vehicle_Year and Vehicle_Age_Type is having index and since you mentioned the query needs optimization I guess you are missing the indexes.

Next step add the index as

alter table CarInfo add index type_year_idx (Vehicle_Age_Type,Vehicle_Year);

NOTE : You must take a backup of the table before adding the index

Then re-frame the query as

SELECT c.* 
FROM CarInfo as c 
WHERE c.Vehicle_Age_Type='USED' 
AND c.Vehicle_Year<='2014' limit 0,15 ;

In addition when you feel the query is taking long time you should always use EXPLAIN to see what this query is up to so you can plan for the optimization. The syntax looks like below for your current query.

EXPLAIN  
 SELECT c.* 
    FROM CarInfo as c 
    WHERE 
    c.Vehicle_Year<='2014'
    AND c.Vehicle_Age_Type='USED' 
limit 0,15 ;