Poor performance on field_attach_query()

yched beat me to it opening the issue, subscribing.

Before:

mysql> EXPLAIN SELECT t.bundle AS bundle, t.entity_id AS entity_id, t.revision_id AS revision_id, e.type AS type FROM field_data_taxonomy_tags_2 t INNER JOIN field_config_entity_type e ON t.etid = e.etid WHERE (taxonomy_tags_value = 1) AND (type = 'node') AND (deleted = 0) ORDER BY t.etid ASC, t.entity_id ASC;
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+-------+-----------------------------+
| id | select_type | table | type  | possible_keys               | key     | key_len | ref   | rows  | Extra                       |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | e     | const | PRIMARY,type                | type    | 767     | const |     1 | Using index; Using filesort |
|  1 | SIMPLE      | t     | ref   | PRIMARY,taxonomy_tags_value | PRIMARY | 4       | const | 17683 | Using where                 |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+-------+-----------------------------+
2 rows in set (0.03 sec)

After:

mysql> EXPLAIN SELECT t.bundle AS bundle, t.entity_id AS entity_id, t.revision_id AS revision_id, e.type AS type FROM field_data_taxonomy_mila_4 t INNER JOIN field_config_entity_type e ON t.etid = e.etid WHERE (taxonomy_mila_value = 1) AND (t.etid = 2) AND (deleted = 0) ORDER BY t.etid ASC, t.entity_id ASC;
+----+-------------+-------+-------+------------------------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys                            | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+------------------------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | e     | const | PRIMARY                                  | PRIMARY | 4       | const |    1 |             | 
|  1 | SIMPLE      | t     | ref   | PRIMARY,etid,deleted,taxonomy_mila_value | PRIMARY | 4       | const | 7424 | Using where | 
+----+-------------+-------+-------+------------------------------------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Better, still not using an index though.

I did a re-install of field_sql_storage.module, then upgraded my taxonomy data with the latest patch from that issue - so all the indexes should be there afaik, although I'll double check.

It's possible we need a composite index covering both the where and order by, although if the primary key covers that I dunno.

mysql> SHOW INDEXES FROM field_data_taxonomy_mila_4;
+----------------------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                      | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| field_data_taxonomy_mila_4 |          0 | PRIMARY             |            1 | etid                | A         |           3 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          0 | PRIMARY             |            2 | entity_id           | A         |       27624 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          0 | PRIMARY             |            3 | deleted             | A         |       27624 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          0 | PRIMARY             |            4 | delta               | A         |       27624 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          0 | PRIMARY             |            5 | language            | A         |       27624 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | etid                |            1 | etid                | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | bundle              |            1 | bundle              | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | deleted             |            1 | deleted             | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | entity_id           |            1 | entity_id           | A         |       27624 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | revision_id         |            1 | revision_id         | A         |       27624 |     NULL | NULL   | YES  | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | language            |            1 | language            | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| field_data_taxonomy_mila_4 |          1 | taxonomy_mila_value |            1 | taxonomy_mila_value | A         |        6906 |     NULL | NULL   | YES  | BTREE      |         | 
+----------------------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.02 sec)

I thought it might be the join, but I get where in extra without that too.

@yched - from the show indexes, it looks like there's no cardinality in the table you're running the explain on. Could you try with some dummy content and confirm if it's the same?

OK let's do this and look at multiple-column index support either as a follow-up in this issue or a new one, it's already an improvement.

Schema changes are apparently API changes - while this would have to go in post-freeze anyway, it'll also help some other patches out.

+++ modules/field/modules/field_sql_storage/field_sql_storage.module	29 Aug 2009 01:09:51 -0000
@@ -461,6 +469,20 @@
+    // Translate entity types into numeric ids, to avoid adding conditions on
+    // the external field_config_entity_type table.

I wonder if we could be a little bit more verbose here.

This review is powered by Dreditor.

Committed to CVS HEAD. Thanks.