0

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.

  • Is this the exact query? The columns from table t2 (with t2.lang_id=5) seem to be not used anywhere. Oh, it's used only for the order by. – ypercubeᵀᴹ May 18 '18 at 12:19
  • Is there a UNIQUE constraint on table1 (value)? – ypercubeᵀᴹ May 18 '18 at 12:20
  • It is unique in table1, but when I look at the structure I see only id=primary key and no other constraints. I'm not allowed to change anything in the structure. – Andra May 18 '18 at 12:29
  • Is the t2 column named keycode or atslega? The sample and the code differ. – ypercubeᵀᴹ May 18 '18 at 12:47
  • sorry, it's keycode – Andra May 18 '18 at 12:51
1

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)

  • Thank you! It seemed very promising, however, I got "MySQL returned an empty result set". – Andra May 18 '18 at 13:04
  • What's the type of column stat? – ypercubeᵀᴹ May 18 '18 at 13:05
  • stat is enum('0', '1') – Andra May 18 '18 at 13:14
  • it works! - but it's better enclose these values 5, 10, and 1 in quotes – Andra May 18 '18 at 13:20
  • 1
    If you can't add indexes, don't expect good performance. The more the tables grow, the less efficient the various queries will be. – ypercubeᵀᴹ May 18 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.