You can try to force an index with: SELECT * FROM Posts USE INDEX(index4) WHERE Board = 'board1' ORDER BY Board DESC, Number DESC
Mike -----Original Message----- From: Michael Bonfert [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Problem with index, where clause and order by Hi, assume a table as follows: CREATE TABLE Posts ( Board varchar(100) NOT NULL, Number int(11) unsigned NOT NULL auto_increment, Posted int(11) unsigned DEFAULT '0' NOT NULL, Subject text, Body text, PRIMARY KEY (Number), KEY index1 (Number, Board), KEY index2 (Board), KEY index3 (Posted, Board), KEY index4 (Board, Number) ); and followind SQL statement: SELECT * FROM Posts WHERE Board = 'board1' ORDER BY Board DESC, Number DESC according to the manual http://www.mysql.com/doc/O/R/ORDER_BY_optimisation.html => SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC the statement should use index4, instead index2 is used. The usage of 'limit' or deleting index2 does not effect the index usage. Surprisingly the statement SELECT * FROM Posts WHERE Board <> 'board1' ORDER BY Board DESC, Number DESC uses index4. Why isn't index4 used in the first SQL statement. How can I transform the statement that index4 will be used. Thanks Michael --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
