MySQL Performance – Query to Join Two Subsets of Table Data Faster

join;MySQLperformancequery-performance

TABLE1:

+-----+----------------------+------+-------+
| id  |        value         |weight|  stat |
+-----+----------------------+------+-------+
|  5  | OB_4772FB7E65        |    5 |     1 |
|  6  | OB_A339546708        |    6 |     1 |
|  7  | OB_6CA632C6BC        |    7 |     1 |
|  8  | OB_58757E6E9C        |    8 |     1 |
|  9  | OB_8FF337BE76        |    9 |     1 |
|  10 | OB_CCA8FEC422        |   10 |     1 |
+-----+----------------------+------+-------+

TABLE2 (it's a translation table, where there is always an entry with lang_id=5 and there may or may not be a translation in other languages (here I show only one another lang_id=10). The key keycode here is the same as value in TABLE1) :

+---------------+-----------------+--------+
| id  | lang_id  |    keycode     | teksts |
+---------------+-----------------+--------+
| 940 |  5       | OB_4772FB7E65  | suga1  |
| 941 |  10      | OB_4772FB7E65  | spec1  |
| 946 |  5       | OB_A339546708  | suga2  |
| 947 |  10      | OB_A339546708  | spec2  |
| 949 |  5       | OB_6CA632C6BC  | suga3  |
| 955 |  5       | OB_CCA8FEC422  | suga4  |
| 956 |  10      | OB_CCA8FEC422  | spec4  |
+---------------+-----------------+--------+

The needed result (get all keycodes with stat=1 and their translation or an empty string if there is no translation. The results must be sorted first by weight and then by basis language):

+----+----------------+-----------+
| id |   keycode      | teksts_ln |
+----+----------------+-----------+
|  5 | OB_4772FB7E65  | spec1     |
|  6 | OB_A339546708  | spec2     |
|  7 | OB_6CA632C6BC  |           |
| 10 | OB_CCA8FEC422  | spec4     |
+----+----------------+-----------+

I have managed to write a mysql query, it works ok but it is slow. Maybe I have overdone and it can be simplified?

SELECT glv.id, glv.keycode, gln.teksts_ln FROM
(SELECT t1.id, t1.weight, t1.value AS keycode, t2.teksts AS teksts_lv FROM TABLE1 AS t1 LEFT JOIN TABLE2 AS t2 ON t1.value = t2.keycode AND t2.lang_id=5 WHERE t1.stat="1") AS glv
INNER JOIN
(SELECT t1.value AS keycode, t2.teksts AS teksts_ln FROM TABLE1 AS t1 LEFT JOIN TABLE2 AS t2 ON t1.value = t2.keycode AND t2.lang_id=10 WHERE t1.stat="1") AS gln
ON glv.keycode = gln.keycode ORDER BY glv.weight ASC, glv.teksts_lv ASC;

My main concern is how to make the query work faster.

Best Answer

A few things that can improve your query:

  • There is no need for the derived tables (subqueries). You can have a 4-table join in the FROM clause.

  • Once we do the above, it's obvious that you join table1 to itself on a column that seems to be UNIQUE: (ON glv.keycode = gln.keycode). We can remove this join, so the result is a 3-table join.

The query:

SELECT 
    t1.id, 
    t1.value  AS keycode, 
    ln.teksts AS teksts_ln  
FROM
    table1 AS t1 
    LEFT JOIN table2 AS lv 
      ON t1.value = lv.keycode AND lv.lang_id = 5        
    LEFT JOIN table2 AS ln
      ON t1.value = ln.keycode AND ln.lang_id = 10
WHERE 
    t1.stat = '1' 
ORDER BY 
    t1.weight ASC, lv.teksts ASC ;

After we have it working correctly (producing the same results), you can improve efficiency by adding indexes:

  • on table2 (lang_id, keycode, teksts)

  • on table1 (stat)

Related Question