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`
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