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`