Internal union with union All

Question

Which version of the request is faster / better? (Curiosity.)

More importantly, are they equivalent?

Do these queries do the same in this example?

1) INNER JOIN with two OR conditions:

SELECT DISTINCT (cat.id) as 'Cat ID:' FROM cat INNER JOIN cuteness_showdown ON (cat.id = cuteness_showdown.cat_1 OR cat.id = cuteness_showdown.cat_2); 

2) Request each column separately and UNION ALL:

 SELECT DISTINCT (table_1.id) as 'Cat ID:' FROM (SELECT cuteness_showdown.cat_1 AS id FROM cuteness_showdown UNION ALL SELECT cuteness_showdown.cat_2 AS id FROM cuteness_showdown) AS table_1; 

Now, which version is faster / better if I need a column from another table?

1) INNER JOIN with two OR conditions (no change):

 SELECT DISTINCT (cat.id) as 'Cat ID:', cat.name as 'Cat Name:' FROM cat INNER JOIN cuteness_showdown ON (cat.id = cuteness_showdown.cat_1 OR cat.id = cuteness_showdown.cat_2); 

2) Request each column separately and UNION ALL (necessary for the INNER JOIN cat table):

 SELECT DISTINCT (table_1.id) as 'Cat ID:' cat.name as 'Cat Name:' FROM (SELECT cuteness_showdown.cat_1 AS id FROM cuteness_showdown UNION ALL SELECT cuteness_showdown.cat_2 AS id FROM cuteness_showdown) AS table_1 INNER JOIN cat on (table_1.id = cat.id); 

Answer 1

To find out which is faster, exit the terminal, write a script that runs every 1000 times and compares the results :)

As for whether they are equivalent, the query optimizer very often produces an accurate execution plan for several SQL queries that do the same, so they may well be. I can’t tell you if these procedures will receive this treatment, but you can use EXPLAIN to view the execution plans for yourself and compare them, assuming you have data.

If the execution plans are actually the same, it’s best to choose a more readable statement so that anyone who comes to support the code can do this easily. Alternatively, if they do not match, then you need to decide whether to talk about more complex reading in order to increase productivity, which depends on how high the efficiency of the transaction in your project is. I would say that if you have a relatively small database that is unlikely to scale and the response time is less than 10 ms, then performance is not a problem, so just simplify it.

Answer 2

There is another option

 SELECT DISTINCT (cat.id) as 'Cat ID:' FROM cat INNER JOIN cuteness_showdown ON cat.id IN (cuteness_showdown.cat_1,cuteness_showdown.cat_2) 

There is a very slight difference, but, strangely enough, IN () always seemed to do more efficient work with keys than OR, see if I could somehow generate some tables for some solid data.