Lorsqu'un site qui utilise le module views a un grand nombre d'articles (>1 000 000) relatifs à une nombre de sujets restreints (< 1000) la requête générée par le module views posent un problème de tenue en charge à cause de la génération de fichiers temporaires, ce qui limite bloque au niveau des I/O :

mysql> EXPLAIN SELECT node. nid AS nid, node.created AS node_created
    -> FROM cms_node node
    -> LEFT JOIN cms_term_node term_node ON node.vid = term_node.vid
    -> INNER JOIN cms_term_data term_data ON term_node.tid = term_data.tid
    -> WHERE (node.type in ('destination'))
    -> AND (node.status <> 0) AND (node.language in ('fr')) AND (term_data.name = 'wrephidislut')
    -> ORDER BY node_created ASC LIMIT 0, 10;
+----+-------------+-----------+--------+--------------------------------+---------+---------+----------------------------+--------+----------------------------------------------+
| id | select_type | table     | type   | possible_keys                  | key     | key_len | ref                        | rows   | Extra                                        |
+----+-------------+-----------+--------+--------------------------------+---------+---------+----------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | term_node | index  | PRIMARY,vid                    | PRIMARY | 8       | NULL                       | 802023 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | term_data | eq_ref | PRIMARY                        | PRIMARY | 4       | poc_linagora.term_node.tid | 1      | Using where                                  |
|  1 | SIMPLE      | node      | eq_ref | vid,node_status_type,node_type | vid     | 4       | poc_linagora.term_node.vid | 1      | Using where                                  |
+----+-------------+-----------+--------+--------------------------------+---------+---------+----------------------------+--------+----------------------------------------------+
3 rows in set (0.80 sec)

Avec la correction disponible dans le patch ci-joint, le nobmre d'enregistrement parcouru est bien limité et il n'y a plus création de fichiers temporaires :

mysql> EXPLAIN SELECT * FROM (
    -> SELECT node. nid AS nid, node.created AS node_created
    -> FROM cms_node node
    -> LEFT JOIN cms_term_node term_node ON node.vid = term_node.vid
    -> INNER JOIN cms_term_data term_data ON term_node.tid = term_data.tid
    -> WHERE (node.type in ('destination'))
    -> AND (node.status <> 0) AND (node.language in ('fr')) AND (term_data.name = 'wrephidislut')
    -> ) as t
    -> ORDER BY node_created ASC LIMIT 0, 10;
+----+-------------+------------+--------+--------------------------------+---------+---------+----------------------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys                  | key     | key_len | ref                        | rows | Extra                    |
+----+-------------+------------+--------+--------------------------------+---------+---------+----------------------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                           | NULL    | NULL    | NULL                       | 1920 | Using filesort           |
|  2 | DERIVED     | term_data  | ALL    | PRIMARY                        | NULL    | NULL    | NULL                       | 434  | Using where              |
|  2 | DERIVED     | term_node  | ref    | PRIMARY,vid                    | PRIMARY | 4       | poc_linagora.term_data.tid | 8020 | Using where; Using index |
|  2 | DERIVED     | node       | eq_ref | vid,node_status_type,node_type | vid     | 4       | poc_linagora.term_node.vid | 1    | Using where              |
+----+-------------+------------+--------+--------------------------------+---------+---------+----------------------------+------+--------------------------+
4 rows in set (0.02 sec)

This contribution has been submitted on 2009-12-02 by Sebastien BAHLOUL .
This patch has been made for version 6