Understanding MySql optimization of Queries

[roy kachouh]

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.