Search code examples
mysqlquery-optimization

Bad query performance. Where do I need to create the indexes?

I have a relativelly simple query, but it's performance is really bad.

I'm currently at something like 0.800 sec. per query.

Is there anything that I could change to make it faster?

I've already tried indexing the columns, used in where statement and join, but nothing works.

Here's the query that I'm using:

SELECT c.bloqueada, c.nomeF, c.confirmadoData
FROM encomendas_c_linhas el1
LEFT JOIN encomendas_c c ON c.lojaEncomenda=el1.lojaEncomenda
WHERE (el1.artigoE IN (342197) OR el1.artigoD IN (342197))
AND el1.desmembrado = 1

Here's the EXPLAIN: enter image description here

As Bill Karwin asked, here follows the Query used to create the table:

Table "encomendas_c_linhas" https://pastebin.com/73WcsDnE

Table "encomendas_c"

https://pastebin.com/yCUx3wh0

Solution

  • In your EXPLAIN, we see that it's accessing the el1 table with type: ALL which means it's scanning the whole table. The rows field shows an estimate of 644,236 rows scanned (this is approximate).

    In your table definition for the el1 table, you have this index:

      KEY `order_item_id_desmembrado` (`order_item_id`,`desmembrado`),

    Even though desmembrado appears in an index, that index will not help this query. Your query searches for desmembrado = 1, with no condition for order_item_id.

    Think of a telephone book: I can search for people with last name 'Smith' and the order of the phone book helps me find them quickly. But if I search for people with the first name 'Sarah' the book doesn't help. Only if I search on the leftmost column(s) of the index does it help.

    So you need an index with desmembrado as the leftmost column. Then the search for desmembrado = 1 might use the index to select those matching rows.

    ALTER TABLE encomendas_c_linhas ADD INDEX (desmembrado);

    Note that if a large enough portion of the table matches, MySQL skips that index anyway. There's no advantage in using the index if it will match a large portion of rows. In my experience, MySQL's optimizer's judgement is that it avoids the index if the condition matches > 20% of the rows of the table.

    The other conditions are in a disjunction (terms of an OR expression). There's no way to optimize these with a single index. Again, the telephone book example: Search for people with last name 'Smith' OR first name 'Sarah'. The last name lookup is optimized, but the first name lookup is not. No problem, we can make another index with first name listed first, so the first name lookup will be optimized. But in general, MySQL will use only one index per table reference per query. So optimizing the first name lookup will spoil the last name lookup.

    Here's a workaround: Rewrite the OR condition into a UNION of two queries, with one term in each query:

    SELECT c.bloqueada, c.nomeF, c.confirmadoData
FROM encomendas_c_linhas el1
LEFT JOIN encomendas_c c ON c.lojaEncomenda=el1.lojaEncomenda
WHERE el1.artigoD IN ('342197')
AND el1.desmembrado = 1 
UNION
SELECT c.bloqueada, c.nomeF, c.confirmadoData
FROM encomendas_c_linhas el1
LEFT JOIN encomendas_c c ON c.lojaEncomenda=el1.lojaEncomenda
WHERE el1.artigoE IN ('342197')
AND el1.desmembrado = 1;

    Make sure there's an index for each case.

    ALTER TABLE encomendas_c_linhas 
  ADD INDEX des_artigoD (desmembrado, artigoD),
  ADD INDEX des_artigoE (desmembrado, artigoE);

    Each of these compound indexes may be used in the respective subquery, so it will optimize the lookup of two columns in each case.

    Also notice I put the values in quotes, like IN ('342197') because the columns are varchar, and you need to compare to a varchar to make use of the index. Comparing a varchar column to an integer value will make the match successfully, but will not use the index.

    Here's an EXPLAIN I tested for the previous query, that shows the two new indexes are used, and it shows ref: const,const which means both columns of the index are used for the lookup.

    +----+--------------+------------+------+-------------------------+---------------+---------+------------------------+------+-----------------------+
| id | select_type  | table      | type | possible_keys           | key           | key_len | ref                    | rows | Extra                 |
+----+--------------+------------+------+-------------------------+---------------+---------+------------------------+------+-----------------------+
|  1 | PRIMARY      | el1        | ref  | des_artigoD,des_artigoE | des_artigoD   | 41      | const,const            |    1 | Using index condition |
|  1 | PRIMARY      | c          | ref  | lojaEncomenda           | lojaEncomenda | 61      | test.el1.lojaEncomenda |    2 | NULL                  |
|  2 | UNION        | el1        | ref  | des_artigoD,des_artigoE | des_artigoE   | 41      | const,const            |    1 | Using index condition |
|  2 | UNION        | c          | ref  | lojaEncomenda           | lojaEncomenda | 61      | test.el1.lojaEncomenda |    2 | NULL                  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL                    | NULL          | NULL    | NULL                   | NULL | Using temporary       |
+----+--------------+------------+------+-------------------------+---------------+---------+------------------------+------+-----------------------+

    But we can do one step better. Sometimes adding other columns to the index is helpful, because if all columns needed by the query are included in the index, then it doesn't have to read the table rows at all. This is called a covering index and it's indicated if you see "Using index" in the EXPLAIN Extra field.

    So here's the new index definition:

    ALTER TABLE encomendas_c_linhas 
  ADD INDEX des_artigoD (desmembrado, artigoD, lojaEncomenda),
  ADD INDEX des_artigoE (desmembrado, artigoE, lojaEncomenda);

    The third column is not used for lookup, but it's used when joining to the other table c.

    You can also get the same covering index effect by creating the second index suggested in the answer by @TheImpaler:

    create index ix2 on encomendas_c (lojaEncomenda, bloqueada, nomeF, confirmadoData);

    We see the EXPLAIN now shows the "Using index" note for all table references:

    +----+--------------+------------+------+-------------------------+-------------+---------+------------------------+------+--------------------------+
| id | select_type  | table      | type | possible_keys           | key         | key_len | ref                    | rows | Extra                    |
+----+--------------+------------+------+-------------------------+-------------+---------+------------------------+------+--------------------------+
|  1 | PRIMARY      | el1        | ref  | des_artigoD,des_artigoE | des_artigoD | 41      | const,const            |    1 | Using where; Using index |
|  1 | PRIMARY      | c          | ref  | lojaEncomenda,ix2       | ix2         | 61      | test.el1.lojaEncomenda |    1 | Using index              |
|  2 | UNION        | el1        | ref  | des_artigoD,des_artigoE | des_artigoE | 41      | const,const            |    1 | Using where; Using index |
|  2 | UNION        | c          | ref  | lojaEncomenda,ix2       | ix2         | 61      | test.el1.lojaEncomenda |    1 | Using index              |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL                    | NULL        | NULL    | NULL                   | NULL | Using temporary          |
+----+--------------+------------+------+-------------------------+-------------+---------+------------------------+------+--------------------------+