Hello! There is a request:

select count(*) as ctr from T1 where my_id in (select my_id from T2 where phrase = 'some_phrase');

in works very slowly. In SQL queries, I am not yet special, but it is clear that the same operations can be performed using join. Explain how to crank?

Update Sorry folks, I didn’t specify - in the second table my_id is not the primary key. in the first table my_id is primary, and in the second it can occur as many times as desired.

  • I have a feeling that the absence of an index on a phrase will work slowly in this query. I guessed right? Index is not there? - BOPOH
  • yes, the phrase is just a box. The index is my_id. Tasks to make everything fly - no, you just need to do so that it is not very slow. The data is not sent to the user, but to the admin panel for tests. - AntoNi0
  • 2
    Read this And there already look what is slowing down. I repeat - it seems to me that the absence of an index on the text field on which you build the query is slowing down. JOIN is also not an easy operation, so changing IN to JOIN or not is not a simple question for me personally. We must look at the profiler. - BOPOH
  • The phrase can also occur several times. Such, like, you do not index? - AntoNi0
  • @ AntoNiO why? It is fully indexed. - alexlz

5 answers 5

 SELECT COUNT(*) AS `cnt` FROM `T1` INNER JOIN `T2` ON `T2`.`my_id` = `T1`.`my_id` WHERE `T2`.`phrase` = 'some_phrase'

Such a query will return the result you want. I assume that the correspondences of the records in tables 1 to 1, the fields that participate in the ON condition must be indexed. And subqueries in the IN clause of the WHERE part in MySQL are generally better not to use, as the MySQL query interpreter does not know how to correctly process them.

  • in T2 my_id can occur several times. - AntoNi0
  • Now tell me, what is your answer different from the answer @Gorets? Alignment and cnt field name? And, damn, he did not write where . This is it for nothing. - alexlz
  • one
    @alexlz, well, MySQL will ultimately seem to lead the request of Comrade Gorets and Comrade ice178 to the expression: SELECT COUNT (*) AS cnt FROM T1 INNER JOIN T2 WHERE T2. my_id` = t1. my_id AND T2 . phrase = 'some_phrase'; See for yourself: explain extended SQL ; show warnings; Although yes, it’s not good to repeat the response of another person ... - BOPOH

In this particular case, table T1 is not needed at all. You need to calculate how many different my_id codes are found in table T2. If there is an explicit connection between T1 and T2 (i.e. there are no dangling references), then the SELECT COUNT query (DISTINCT my_id) is enough. FROM T2 WHERE phrase = 'some_phrase'

  • If the vehicle is not "simplified" the task facing him to COUNT (*). If he did that, then your advice is past the cash register. If he really needs a quantity - then you are right (the crap type my_id is present in T2, but not in T1 - we don’t consider it. It can be a lot of crap ) - alexlz
 SELECT COUNT(*) FROM T1 INNER JOIN T2 ON T1.MY_ID = T2.MY_ID AND T2.PHARE = 'some_phrase';

not sure but ..

  • May be. What happens if T2.MY_ID is not unique? And one T1.MY_ID correspond to two (or more) T2.MY_ID? - alexlz
  • I do not know, but I think that the ID - already implies uniqueness, even if it is not the primary key - Gorets
  • Only the crematorium gives a full guarantee, so nothing prevents you from adding SELECT COUNT (DISTINCT T1.my_id) FROM T1 INNER JOIN T2 ON T1.MY_ID = T2.MY_ID AND T2.PHARE = ​​'some_phrase'; - renegator
  • Forgive the people, I did not specify - in the second table mu_id is not the primary key. in the first table mu_id is primary, and in the second it can occur as many times as desired. - AntoNi0
  • I see no problems, it suits the conditions, DISTINCT is not necessary - Gorets
 SELECT t1.count(*) AS ctr FROM t1 LEFT JOIN t2 ON t1.my_id=t2.my_id WHERE t2.phrase='some_phrase'

If I did not mess up, then so.

  • Confused. Lines with MY_ID that are missing in the selection from T2 will be additionally counted. - alexlz
  • @alexlz where the condition on t2 is involved, then left join turns into inner, null t2.phrase values ​​will be filtered out. but in fact, yes, the left join is not needed here a priori. - Yura Ivanov
  • @Yura Ivanov Yes. I confused a few. The only mistake, as in the case of INNER JOIN, is the non-uniqueness of the pairs my_id / phrase in T2 - alexlz

here is a similar question

here only with join it will not be faster

  • Practice shows that faster. Plus joins he caches the campaign itself. - AntoNi0