优化传递参数查看
我在 mysql 中有相当复杂的视图，比如
select filter.id as filter_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
查询过滤器对应于域特定条件的人。
视图的典型用途是：
select * from where filter_id = some_value
问题是mysql无法优化查询。它在获取所有过滤器的数据后通过 filter_id 应用配置 - 非常低效。 从其他表获取 filter_id 的想法不适合我的情况。
如何转换查询以使其更有效？
I have quite complicated view in mysql, like
select filter.id as filter_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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
将长查询包装在过程中，并将过滤器作为参数传递给过程调用。然后，您不使用视图来调用该过程，该过程将为您构建整个查询并运行优化的查询。
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.
更好的是，您可以通过创建一个函数从会话变量中获取值，以简单的方式将参数传递到视图。有关技术，请参阅 https://www.stackoverflow.com/questions/14511760。这是我的创建函数的副本，您可能希望模仿它。
分隔符 //
创建函数 fn_getcase_id()
返回中度(11)
确定性无 SQL
开始
查看 stackoverflow.com/questions/14511760 并阅读所有信息两次或更多次。 wh 04/13/2017
结束//
分隔符;
您将需要创建一个类似的 FN（每个变量一个）。
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
END//
DELIMITER ;
You will need to create a similar FN (one for each variable).