Hi, I wonder which index will be the most efficient for this query :
SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; I tested a compound index on (pseudo,date,numreponse), and explain return : mysql> EXPLAIN SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; +-------------------+-------+---------------+--------+---------+------+----- -+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------------+-------+---------------+--------+---------+------+----- -+-------------------------+ | searchhardwarefr7 | range | pseudo | pseudo | 43 | NULL | 1 | where used; Using index | +-------------------+-------+---------------+--------+---------+------+----- -+-------------------------+ 1 row in set (0.01 sec) But I also tested an index on (pseudo,numreponse,date), and explain return : mysql> EXPLAIN SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; +-------------------+------+---------------+----------+---------+-------+--- ---+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------------+------+---------------+----------+---------+-------+--- ---+-------------------------+ | searchhardwarefr7 | ref | pseudo_2 | pseudo_2 | 35 | const | 1 | where used; Using index | +-------------------+------+---------------+----------+---------+-------+--- ---+-------------------------+ 1 row in set (0.00 sec) It seems ref type is better than range type, but I was thinking the (pseudo,date,numreponse) index was better the second one, as it matched exactly the query (or perhaps explain simply doesn't write the first ref match made before the range match). It's why I need in this case some clarification about indexes :) Thank you in advance, Jocelyn --------------------------------------------------------------------- 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
