How to optimize mysql query based on explain. (Type: ALL

Question

I run the following mysql query and see the type as ALL for the first query.

   mysql> EXPLAIN  
           SELECT one.language_id as filter_id, 
                  one.language_name as filter_name, 
                  two.count as count 
           FROM   books_f9_languages one 
           INNER JOIN (SELECT language_id, 
                       count(*) as count 
                       FROM link_f9_books_lists 
                       WHERE books_list_id IN (1691,1,2,3,4,6,7,8,9,10,11,12,13,14,17,18,19,20,21,22,23,24,25,26,28,29,30,31,32,33,34,35,36,37,43,44,47,51,54,57,58,59,68,71,76,77,86,88,93,94,99,120,125,126,127,133,146,147,148,257,260,261,262,263,264,266,267,268,269,270,271,272,275,276,286,767,768,769,771,772,774,777,779,783,785,786,790,792,799,808,811,813,814,815,819,825,828,829,847,850,852,853,855,856,857,858,862,863,866,869,873,875,882,891,900,907,917,925,930,935,1092,1531,1532,1533,1534,1535,1536,1537,1538,1540,1541,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1556,1557,1558,1560,1561,1563,1564,1565,1567,1568,1569,1570,1571,1572,1574,1575,1576,1577,1578,1579,1580,1581,1582,1583,1584,1586,1588,1589,1590,1591,1592,1595,1597,1599,1600,1601,1603,1604,1605,1606,1607,1608,1609,1610,1612,1613,1614,1615,1616,1617,1620,1621,1622,1623,1624,1625,1627,1628,1629,1630,1632,1636,1637,1638,1639,1640,1642,1643,1644,1645,1646,1648,1649,1651,1652,1653,1654,1659,1660,1662,1665,1675,1677,1679,1680,1689,1692,1693,1696,1698,1699,1702,1703,1705,1711,1713,1714,1716,1717,1719,1720,1726,1728,1729,1732,1734,1735,1736,1738,1743,1744,1753,1754,1755,1756,1759,1786,1787,1788,1789,1790,1791,1792,1793,1794,1795,1797,1837,827) GROUP BY language_id) two 
          WHERE one.language_id = two.language_id 
          ORDER BY filter_name;

This is the output,

+----+-------------+---------------------+--------+---------------+---------------+---------+-----------------+------+-----------------------------------------------------------+
| id | select_type | table               | type   | possible_keys | key           | key_len | ref             | rows | Extra                                                     |
+----+-------------+---------------------+--------+---------------+---------------+---------+-----------------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2>          | ALL    | NULL          | NULL          | NULL    | NULL            |    5 | Using temporary; Using filesort                           |
|  1 | PRIMARY     | one                 | eq_ref | PRIMARY       | PRIMARY       | 4       | two.language_id |    1 |                                                           |
|  2 | DERIVED     | link_f9_books_lists | range  | books_list_id | books_list_id | 4       | NULL            |  273 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+---------------------+--------+---------------+---------------+---------+-----------------+------+-----------------------------------------------------------+
3 rows in set (0.01 sec)

Which column I have to index in order to make that ALL, use the index.

Thanks.

Marcus Adams · Answer 1 · 2012-06-07T12:50:58.780

1

Derived temporary tables can't utilize indexes.

Removing the ORDER BY clause or ordering by language_id should take care of that issue.

Alternatively, you could try adding a compound index on language_id and language_name on the books_f9_languages table.

To improve the inner query, add a compound index on book_list_id and language_id for the link_f9_books_lists table.

edited Jun 07 '12 at 12:50

answered Jun 06 '12 at 20:17

Marcus Adams

53,009
9
91
143

I did this, no change..mysql> alter table books_f9_languages add index (language_id,language_name); Query OK, 16 rows affected (0.30 sec) Records: 16 Duplicates: 0 Warnings: 0 – beck03076 Jun 06 '12 at 20:24
@beck03076, You might still see ALL in the type, but are the `Using temporary; Using filesort` gone? That's what's killing you. It will always be `ALL` for a derived table. Sorry if that doesn't help. – Marcus Adams Jun 06 '12 at 20:47
@beck03076 Bummer! The query may just be too complex for MySQL to utilize the index for ordering. I'd try moving your WHERE clause into an ON clause to see if it helps. – Marcus Adams Jun 06 '12 at 20:58

How to optimize mysql query based on explain. (Type: ALL

1 Answers1