Hello all, I'm working on a photo album engine where a few MySQL queries find which thumbnails that should be around the image queried (in order of time). I ended up starting with a query to get the time, filename, etc., of the requested image, and then one query for images before and one query for images after, like so:
select * from image_data where name = 'p0012345.jpg'; select name from image_data where time < '2001-11-01 20:10:57' order by time desc limit 4; select name from image_data where time >= '2001-11-01 20:10:57' order by time limit 4; This all works quite nicely, but the second query doesn't use my index on "time", and ends up taking 0.52 seconds to execeute (~8500 rows in image_data). The first and last queries use the indexes and are fast. I tried creating another index on "time desc" (which appeared to be accepted as valid syntax), but it didn't make any difference. If I change "<" to "<=", there is no difference. If I change "<" to "=", the index works, but I already know the result. If I change "<" to ">" or ">=", the index works, but this isn't what I want. If I remove "desc" from "order by time desc" to "order by time", the index always works too, but again this isn't what I want. MySQL appears to be having problems walking backwards on the index. On the web documentation, it looks like this is supposed to work: * Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC. ...But this likely refers to grouping, not a range on the initial match. I don't understand why I can walk forward with the index but not the other way, though. Does anybody have any ideas? :) MySQL 3.23.46, by the way. Thanks, Simon- [ Stormix Technologies Inc. ][ NetNation Communications Inc. ] [ [EMAIL PROTECTED] ][ [EMAIL PROTECTED] ] [ Opinions expressed are not necessarily those of my employers. ] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
