Hi!
>>>>> "Fournier" == Fournier Jocelyn <Fournier> writes:
Fournier> Hi,
Fournier> I've just pointed up a problem with the efficienty of SQL_CALC_FOUND_ROWS :
<cut>
mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS
Fournier> titre,numeropost,auteur,icone,nbrep,maxnumrep,date,vue,ouvert,lastauteur,son
Fournier> dage FROM forumconthardwarefr7 ORDER BY maxnumrep DESC LIMIT 120, 160;
Fournier> +----------------------+------+---------------+------+---------+------+-----
Fournier> --+----------------+
Fournier> | table | type | possible_keys | key | key_len | ref | rows
Fournier> | Extra |
Fournier> +----------------------+------+---------------+------+---------+------+-----
Fournier> --+----------------+
Fournier> | forumconthardwarefr7 | ALL | NULL | NULL | NULL | NULL |
Fournier> 26823 | Using filesort |
Fournier> +----------------------+------+---------------+------+---------+------+-----
Fournier> --+----------------+
Fournier> 1 row in set (0.04 sec)
<cut>
Fournier> It seems there is something wrong here : why does the SQL_CALC_FOUND_ROWS
Fournier> query not use index ??
No, nothing is wrong.
When you have to sort a big part of the whole table, it's in MySQL
faster to sort the table by doing a table scan instead of using the
index, which would cause a lot of extra disk seeks.
When using LIMIT without SQL_CALC_FOUND_ROWS MySQL knows that it
doesn't have to sort the whole table and can because of this use an index.
Regards,
Monty
