MySQL Forums :: Newbie :: Query performance question (using index slows down?)


Advanced Search

Query performance question (using index slows down?)
Posted by: Jeff Peff ()
Date: January 22, 2009 08:03AM

Hi, I have the following query processed in my app and was wondering if there is any way to make it faster.

SELECT * from city where last_sync > '1900-01-01 00:00:00' AND last_sync < CURRENT_TIMESTAMP limit [FILLED_IN_BY_APP],[FILLED_IN_BY_APP];

limit increments in steps of 500:
limit 0, 500
limit 500,500
limit 1000,500
etc.

explain returns:

+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+
| 1 | SIMPLE | city | range | sync_dates | sync_dates | 8 | NULL | 138456 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+

I've observed that the higher the limit goes (starting from limit 100000, 500) the querytime substantially increases. Note that my table has roughly 300K records. Because they have been inserted all at once, the result at the first time running this query will be everything (300k).

However I've checked within my app how long it takes to execute the query and when -NOT- using an index the query times go to maximally 500ms while when I am using an index the max lies at 1 second.

My questions:
1) Why is limit(0,500) (30ms) executed faster than "limit (200000, 500)" (600ms)
2) Why is not using an index actually faster than using an index in this case?

Thank you very much.

Options: ReplyQuote


Subject Written By Posted
Query performance question (using index slows down?) Jeff Peff 01/22/2009 08:03AM
Re: Query performance question (using index slows down?) Rick James 01/23/2009 12:38AM


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.