Hi, > With the following query if I it returns 2 results it's fast .04s, if > it has less results than the limit it takes 1minute. > > Query: > select * from hub_dailies_sp where active='1' and date='2010-08-04' > order by id desc LIMIT 2; > > Show create table: > http://pastebin.org/447171 > > 27,000 rows in table/view > > Can anyone shed any light?
Isn't it so that it firstly order the rows by id (index'ed?) and then scan it to pick the rows which satisfy the where clause? It stops when the result reaches the limit, otherwise scans the whole (27, 000 rows scan). Then the response time with 2 rows limit by 2 can really depend. If the ma tching 2 rows reside at the very beginning of the id ordered result, it ca n be very fast (2 rows scan), but if they are located at the end, it can t ake as long as the full table scan. Correct me if I'm wrong. Regards, Eigo > Cheers, > Kristian > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ei...@pop01.odn.ne > .jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
