Subquery in select statement times out even if with indexes already

Question

I have this sql query:

SELECT t1.company_id, 
t1.count,
(
    SELECT referrer 
    FROM 
      table2 USE INDEX (CompanyId_CreatedAt_Domain)
    WHERE 
      table2.company_id = t1.company_id 
      AND 
      (table2.created_at BETWEEN '2022-03-08 00:00:00' AND '2022-03-22 23:59:59')
    LIMIT 1
) as referrer
FROM 
   table1 t1 USE INDEX(table1_ta_cid_fid_a_index)
WHERE 
   t1.tracked_at BETWEEN '2022-03-08 00:00:00' AND '2022-03-22 23:59:59'
GROUP BY 
   t1.company_id ORDER BY t1.count DESC
LIMIT 10

I noticed that this query times out when I started to insert the subquery in the select statement. I also tried putting a LIMIT 10 and check if it won't time out, but it still timed out, I even tried adding LIMIT 1, but still, the result is the same.

Is there a way to refactor this query so that it will not time out? I added indexes already in both my table1 and table2 tables.

These are the columns that i've indexed:

• company_id
• created_at
• tracked_at

Another note is that both table1 and table2 has 2.5M number of rows.

Some of you might suggest that I need to add an index in the referrer column, but tbh that is impossible right now as indexing that column might take days to alter the table and would increase the size and slow down other queries.

Any tips on this? Your help is greatly appreciated! Thanks!

Answer 1

Make sure set the indexes no the table1 and table2.

You may try LEFT JOIN

SELECT t1.company_id,
    t1.count,
    t2.referrer
FROM 
(
    SELECT company_id, 
        count,
    FROM 
        table1 USE INDEX(table1_ta_cid_fid_a_index)
    WHERE 
        tea.tracked_at BETWEEN '2022-03-08 00:00:00' AND '2022-03-22 23:59:59'
    GROUP BY 
        tea.company_id 
    ORDER BY tea.count DESC
    LIMIT 10
) AS t1
LEFT JOIN
(
    SELECT referrer,
        company_id
    FROM 
      table2 USE INDEX (CompanyId_CreatedAt_Domain)
    WHERE 
      table2.created_at BETWEEN '2022-03-08 00:00:00' AND '2022-03-22 23:59:59'
    LIMIT 1
) as t2
ON t2.company_id = t1.company_id