I have a query that I want to give someone permissions to view. The query is pretty basic but along the lines of:
-- In actuality, this is a 60 line SQL query joining 5 tables
SELECT * FROM
<a bunch of joined tables>
GROUP by a.id
Normally this view is queried on an id (which is indexed on the initial table), something like this:
SELECT * FROM ...
WHERE id < 10 AND row_last_modified > '2021-05-14 04:42:47'
This normally executes in about 10ms when running the normal query (against the tables, not the view). However, as soon as I create this view, it seems to do the GROUP BY and aggregate on every single row before doing anything else, so even if I grab one ID, the query still takes about 5 minutes to run, making the view entirely unusable. Is there a way around this in mysql, or do I just have to give the end user access to all those tables to do the query instead of the view?
(I suppose another option would be to create the view without the GROUP BY and just tell the user "You must tack on this GROUP BY a.id for the query to work or else you will get garbage results.")
The only thing I've read to deal with this is the pushdown offered in mysql8: https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html, but maybe I'm missing something here (fingers crossed) and there's an easy way to do this!
Here is the explain plan:
explain SELECT * from global_view where info_id=1;
The 765375 rows scanned on the derived table seems to be the killer.