Mysql query execution time optimization for large data table

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.

I'm not aware of any ways to "optimize" a SELECT * statement — domdomcodecode
– domdomcodecode, Commented May 20, 2014 at 7:35
You'll need to include the CREATE TABLE statement for your CarInfo table. If you don't have it, you can get it by running SHOW CREATE TABLE CarInfo — Cully
– Cully, Commented May 20, 2014 at 7:36
@d_ominic Ummm...using indexes would be one example. * isn't the bottle neck in the query, it's usually the conditions of the WHERE clause. — Cully
– Cully, Commented May 20, 2014 at 7:37

Abhik Chakraborty · Accepted Answer · 2014-05-20 07:52:33Z

2

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 ;

edited May 20, 2014 at 7:52

answered May 20, 2014 at 7:39

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tarangini Over a year ago

Thanks for you reply.. Table is given by client we are not supposed to edit that table. Is Adding index effects existing data? Where condition parameters are selected by user..@ Abhik Chakraborty

Cully Over a year ago

@user3519361 Adding indexes won't change the data itself, but it will change the table, in a sense. What it does is create index files for the columns you define in the index. These files are used to speed of searches on those columns. Adding indexes is pretty much the easiest way to speed up most queries.

Abhik Chakraborty Over a year ago

Well adding index in the table does not change the data but it will add book mark for the columns so that when the query executes the optimizer looks at the bookmark and instead of doing full table scan it directly goes for the values against the indexed columns.

tarangini Over a year ago

Thanks for your Response..Got it working.. @ Abhik Chakraborty

tarangini Over a year ago

With limit it is loading fast but i have to get total matched records when i am trying to use count its taking long time.. Is there anyway to work it out.?

|

Collectives™ on Stack Overflow

Mysql query execution time optimization for large data table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related