Hi, I assume it's because MySQL returns more than 30% of the totally of the rows. On one of my table, with 4.0.6-gamma :
mysql> SELECT COUNT(*) FROM news; +----------+ | COUNT(*) | +----------+ | 4985 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM news WHERE datec< '2000-12-31' order by datec DESC; +----------+ | COUNT(*) | +----------+ | 778 | +----------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT * FROM news WHERE datec< '2000-12-31' order by datec DESC; +-------+-------+---------------+-------+---------+------+------+----------- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+-------+---------+------+------+----------- --+ | news | range | datec | datec | 8 | NULL | 761 | Using where | +-------+-------+---------------+-------+---------+------+------+----------- --+ 1 row in set (0.00 sec) < 30 %, so it uses index mysql> SELECT COUNT(*) FROM news WHERE datec< '2001-12-31' order by datec DESC; +----------+ | COUNT(*) | +----------+ | 2998 | +----------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT * FROM news WHERE datec< '2001-12-31' order by datec DESC; +-------+------+---------------+------+---------+------+------+------------- ----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+------------- ----------------+ | news | ALL | datec | NULL | NULL | NULL | 4985 | Using where; Using filesort | +-------+------+---------------+------+---------+------+------+------------- ----------------+ 1 row in set (0.00 sec) > 30%, so no index used So the result of your explain seems normal for me. Regards, Jocelyn ----- Original Message ----- From: "Mpu Gondrong" <[EMAIL PROTECTED]> To: "Jeremy Zawodny" <[EMAIL PROTECTED]> Sent: Tuesday, December 17, 2002 1:13 AM Subject: Re[2]: No descending index ? > Selasa, 17/12/2002 7:47:38, Jeremy menulis: > > JZ> I'm not quite sure what you're after, but MySQL 4.0 has support > JZ> for reading indexes in reverse order in some circumstances. This > JZ> greatly speeds up some queries that were slow in 3.23. > > I'm using MySQL 4.0.4-beta. For example: > > EXPLAIN SELECT * > FROM news > where date <= '2002-12-31' > order by date desc > > table type possible_keys key key_len ref rows Extra > news ALL date NULL NULL NULL 14138 where used > > Or is that normal ? Any sample 'circumstances' ? TIA. > > Tertanda, > Oguds [36856104] > > > > --------------------------------------------------------------------- > 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
