0

My query example is : 

select * from table1 
 where table1_seq = (select table2_seq from table2 where ~)

then does sub-query operate every row of table1's Data ??

or

just operates once??

IF once, then which is better performance between upper query or use join query??

Mittal Patel
  • 2,732
  • 14
  • 23
amatuer
  • 3
  • 4

1 Answers1

0

With your query it won't return an error only if the subquery return 1 and only 1 result.

You should do an explain on the query :

Explain select * from table1 
where table1_seq = (select table2_seq from table2 where id2=X)

Will return something like that :

id  |   select_type |   table   |   partitions  |   type    |   possible_keys   |   key     |   key_len |   ref     |   rows    |   filtered    |   Extra
==========================================================================================================================================================
1   |   PRIMARY     |   Table1  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   NULL
2   |   SUBQUERY    |   Table2  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   Using index

Inner Join in other hand will also do 2 query :

EXPLAIN select table1.*
from table1 
INNER JOIN table2 ON table1.table1_seq = table2.table2_seq
WHERE  table2.id2=X

Result :

id  |   select_type |   table   |   partitions  |   type    |   possible_keys   |   key     |   key_len |   ref     |   rows    |   filtered    |   Extra
==========================================================================================================================================================
1   |   SIMPLE      |   Table1  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   NULL
2   |   SIMPLE      |   Table2  |   NULL        |   const   |   PRIMARY         |   PRIMARY |   4       |   const   |   1       |   100.00      |   NULL

So it's less a problem of benchmark and more about of what you want to do. If you want to select only 1 value from another table you can really do the query you wrote.

Daniel E.
  • 2,440
  • 1
  • 14
  • 24
  • Thank you for your comment .. and good information but my question is "Does sub-query execute whenever row changed" For example, table1 has 10 rows Data then the sub query execute 10 times or once ?? – amatuer Mar 05 '18 at 13:13
  • https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order , I know only the different clause order execution. Not sure but in my mind the first query will execute on the whole table 1 then narrow with the where statement, in the second query it will restrain on the Join of the 2 tables then narrow the were statement. – Daniel E. Mar 05 '18 at 13:41
  • select all data of table1 and where cluase execute like 'inner join'. then, I think sub-query execute once. i will find more information to be sure. but, your comment gives very helpful concept. Thank you. – amatuer Mar 05 '18 at 15:39