I have quite complicated view in mysql, like

select filter.id as filt开发者_Python百科er_id, person.id, person.name
from person, filter
inner  join 
...
left join
...
where person_match_filter_condition ...
group by filter.filter_id, person.id, person.name

Query filters person which corresponds domain specific conditions.

Typical use of view is:

select * from where filter_id = some_value

Problem is that mysql cannot optimize query. It applies confition by filter_id AFTER get data for all filters - very ineffective. Idea to get filter_id from other tables is not good for my case.

How can I transform my query to make it more effective?

Wrap the long query in a procedure, and pass the filters to the procedure call as parameters. Then instead of using views you call the procedure, the procedure will build you the entire query and will run optimized query.

Better yet, you can pass parameters to your views in a simple manner by creating a Function to GET your values from Session Variables. See https://www.stackoverflow.com/questions/14511760 for the technique. This is a copy of my create function you may wish to pattern after. DELIMITER //

CREATE FUNCTION fn_getcase_id()
RETURNS MEDIUMINT(11) DETERMINISTIC NO SQL BEGIN

see stackoverflow.com/questions/14511760 and read ALL the info TWICE or MORE. wh 04/13/2017

RETURN @sv_case_id;

END//

DELIMITER ; You will need to create a similar FN (one for each variable).