Query takes forever to run

Question

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`

Answer 1

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`

Answer 2

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!