MySQL Forums :: Newbie :: Understanding MySql optimization of Queries


Advanced Search

Understanding MySql optimization of Queries
Posted by: roy kachouh ()
Date: May 13, 2013 12:37PM

I was hoping you guys can help me understand the performance implications of the following queries.

I'm querying a table with approx 4 million rows my initial query performs a full table scan and is described as follows:

select count(1)
from large_table lt
where lt.id >= 0 -- this is a workaround to allow for easier building of a query
and date(lt.start_time) = '2013-05-10';

For my own reasons not really applicaable to this question, I wanted to avoid indexing the start time column.

I was able to successfully optimize the query without adding an index using the following:

select count(1)
from large_table lt
where lt.id >= (select id from large_table lt2 where date(lt2.start_time) = '2013-05-09' order by id desc limit 1)
and date(lt.start_time) = '2013-05-10';

This improved my query results in approx 500%. My question revolves around the inner query used in the above query.

Can someone please explain why the first query below runs much slower than the second query:

select id from large_table je where date(je.start_time) = '2013-05-09' order by id asc limit 1;

select id from large_table je where date(je.start_time) = '2013-05-09' order by id desc limit 1;

id is a primary key and is AUTO_INCREMENTED. The explan plan on the two queries above are identical, the second one runs much faster. Does the optimizer take into account the order and limit clauses, and to what extent.

Any other general advice about the queries listed above would be appreciated.

Options: ReplyQuote


Subject Written By Posted
Understanding MySql optimization of Queries roy kachouh 05/13/2013 12:37PM
Re: Understanding MySql optimization of Queries John Smith 05/13/2013 08:45PM
Re: Understanding MySql optimization of Queries Rick James 05/14/2013 09:42PM
Re: Understanding MySql optimization of Queries Eugene Wolfson 06/14/2013 08:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.