List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:November 9 2001 4:40am
Subject:Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0
View as plain text  
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
Thread
Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0Fournier Jocelyn [Presence-PC]8 Nov
  • Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0Michael Widenius9 Nov
    • Re: Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0Steve Meyers9 Nov
  • Re: Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0Fournier Jocelyn [Presence-PC]9 Nov
    • Re: Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0Sinisa Milivojevic9 Nov
      • Re: Efficienty issue with SQL_CALC_FOUND_ROWS in mysql 4.0Michael Widenius12 Nov