0

I have the following query that is used to define a view in MySQL. But the query takes forever to complete even if I perform the query outside the view. I have a feeling that the inner select is what's slowing things down, but i have no idea how to optimize it further. HELP PLEASE!

SELECT DISTINCT
    `table1`.`date` AS `date`,
    `table1`.`col1` AS `col1`,
    (SELECT 
            COUNT(`tbl1`.`col1`)
        FROM
            `tbl1`
        WHERE
            ((`tbl1`.`col1` = `table1`.`col1`)
                AND (`tbl1`.`date` = `table1`.`date`))) AS `usage_count`
FROM
    `table1` `tbl1`
  • fwiw, my original query was a little easier to read i think. This form was created by doing a select with a sub-select then clicking the 'broom' button in mysql workbench. Unfortunately i'm not sure i have a copy of the original query from before it was 'cleaned up' – smokes2345 Sep 13 '17 at 19:21
  • For best results, you might want to add the output from EXPLAIN PLAN to your question - that will tell people how the engine is processing the query. Noting the indexes and primary keys on each table might also help. – RDFozz Sep 13 '17 at 20:57
2

Yes, the subselect is slowing you down. This is because it has to execute that query for every single row of the outer query. The bigger the outer table, the more times you have to run that same query again.

By moving it out to a join you will only have to execute it once.

SELECT DISTINCT
    `table1`.`date` AS `date`,
    `table1`.`col1` AS `col1`,
    `usage_count`
FROM `table1` LEFT JOIN
(
    SELECT COUNT(`col2`) AS `usage_count`, `col1`, `date`
    FROM `tbl1`
    GROUP BY `col1`, `date`
) AS tbl1 ON `tbl1`.`col1` = `table1`.`col1`
    AND `tbl1`.`date` = `table1`.`date`
  • I often forget that you can treat the results of a select as a table itself. Thanks for the reminder ;) – smokes2345 Sep 14 '17 at 12:25
0

If you don't need to check the column for NULL before counting it, leave it out. That is: COUNT(col2) --> COUNT(*)

tbl1 needs either of these:

INDEX(col1, date)
INDEX(date, col1)

PLEASE do not have an alias (tbl1) the same as a different table! If it does not confuse MySQL, it is destined to confuse the reader!

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.