Edit Question - KnowledgeHub

Title Body

This is a weird one. I am trying to use Views in MySQL (I'm reasonably new to MySQL with more experience with Sybase and SQL Server). Any way this new project we are using MySQL as it seems to have good performance. However to make querying for a web front end simpler we decided to create a few views, all work well, but they take forever to run. The views are very simple, just select statements (these tables do have a few million rows in them) . Say for example this query: SELECT CAST(classifier_results.msgDate as DATE) AS mdate ,classifier_results.objClass AS objClass ,COUNT(classifier_results.objClass) AS obj ,classifier_results.subjClass AS subjClass ,COUNT(classifier_results.subjClass) AS subj FROM classifier_results WHERE (classifier_results.msgDate >= (curdate() - 20)) GROUP BY CAST(classifier_results.msgDate as DATE) ,classifier_results.objClass ,classifier_results.subjClass ORDER BY classifier_results.msgDate DESC When run as a normal select takes around 1.5 seconds to return a result. However when this query is put into a view (as is) - i.e. CREATE VIEW V1a_sentiment_AI_current AS SELECT CAST(classifier_results.msgDate as DATE) AS mdate ,classifier_results.objClass AS objClass ,COUNT(classifier_results.objClass) AS obj ,classifier_results.subjClass AS subjClass ,COUNT(classifier_results.subjClass) AS subj FROM classifier_results WHERE (classifier_results.msgDate >= (curdate() - 20)) GROUP BY CAST(classifier_results.msgDate as DATE) ,classifier_results.objClass ,classifier_results.subjClass ORDER BY classifier_results.msgDate DESC The query takes about 10 times longer (22-30 seconds). So I'm thinking maybe there is some optimization or query caching that doesnt work with Views or maybe there is some setting we've missed in the MySQL config. But is there any way to speed up th

Tags (comma-separated) Cancel