文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >SQL -查询运行非常慢。

问SQL -查询运行非常慢。
EN

Stack Overflow用户
提问于 2013-06-13 15:09:32
回答 3查看 187关注 0票数 1

我的桌子关系如下：

​

​

我从php中运行了下面的查询，并运行了两个大问题。

1)超过了最大内存限制

2)超过最大执行时间

代码语言：javascript
复制
SELECT DISTINCT venues.name, locations.location, events.event, types.type, foods.food, beverages.beverage, event_options.event_option, styles.style, space_requirements.space_requirement, features.feature

FROM (SELECT * FROM venues v LIMIT $offset,$limit) venues

INNER JOIN locations ON venues.location_id = locations.location_id

LEFT JOIN venue_events ON venues.venue_id = venue_events.venue_id
LEFT JOIN events ON events.event_id = venue_events.event_id

LEFT JOIN venue_types ON venues.venue_id = venue_types.venue_id
LEFT JOIN types ON types.type_id = venue_types.type_id

LEFT JOIN venue_foods ON venues.venue_id = venue_foods.venue_id
LEFT JOIN foods ON foods.food_id = venue_foods.food_id

LEFT JOIN venue_beverages ON venues.venue_id = venue_beverages.venue_id
LEFT JOIN beverages ON beverages.beverage_id = venue_beverages.beverage_id

LEFT JOIN venue_event_options ON venues.venue_id = venue_event_options.venue_id
LEFT JOIN event_options ON event_options.event_option_id = venue_event_options.event_option_id

LEFT JOIN venue_styles ON venues.venue_id = venue_styles.venue_id
LEFT JOIN styles ON styles.style_id = venue_styles.style_id

LEFT JOIN venue_space_requirements ON venues.venue_id = venue_space_requirements.venue_id
LEFT JOIN space_requirements ON space_requirements.space_requirement_id = venue_space_requirements.space_requirement_id

LEFT JOIN venue_features ON venues.venue_id = venue_features.venue_id
LEFT JOIN features ON features.feature_id = venue_features.feature_id

我正在使用这个查询来检索与每个场馆相关的所有附件。我需要在后端实现分页，所以我在Sub查询中使用极限$offset、$limit。我找不到任何其他的想法(查询)，所以我可以获得与我现在检索的相同的结果。目前我正在使用

ini_set('memory_limit', '-1');

ini_set('max_execution_time', 60);

php函数可以忽略这些问题，但我认为这不是最佳实践。同时，我也在考虑这样的情况:我可能需要取回50+场馆的所有配件，而max_execution_time = 60可能是不够的。

我怎样才能解决这个问题？请帮帮我。

更新的

获取的行数是(事件*类型*食物*饮料* event_options *样式* space_requirements *功能)。但对我有用的行数不是乘法，而是和。

下面是使用EXPLAIN关键字运行查询后的结果

代码语言：javascript
复制
Array
(
    [0] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => 
            [type] => ALL
            [possible_keys] => 
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 11
            [Extra] => Using temporary
        )

    [1] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_events
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 3
            [Extra] => Using index
        )

    [2] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => events
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_events.event_id
            [rows] => 1
            [Extra] => 
        )

    [3] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_types
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 7
            [Extra] => Using index
        )

    [4] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => types
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_types.type_id
            [rows] => 1
            [Extra] => 
        )

    [5] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_foods
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 3
            [Extra] => Using index
        )

    [6] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => foods
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_foods.food_id
            [rows] => 1
            [Extra] => Using index
        )

    [7] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_beverages
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 3
            [Extra] => Using index
        )

    [8] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => beverages
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_beverages.beverage_id
            [rows] => 1
            [Extra] => 
        )

    [9] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_event_options
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 2
            [Extra] => Using index
        )

    [10] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => event_options
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_event_options.event_option_id
            [rows] => 1
            [Extra] => 
        )

    [11] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_styles
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 1
            [Extra] => Using index
        )

    [12] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => styles
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_styles.style_id
            [rows] => 1
            [Extra] => 
        )

    [13] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_space_requirements
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 3
            [Extra] => Using index
        )

    [14] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => space_requirements
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_space_requirements.space_requirement_id
            [rows] => 1
            [Extra] => 
        )

    [15] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => locations
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => venues.location_id
            [rows] => 1
            [Extra] => 
        )

    [16] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => venue_features
            [type] => ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 4
            [ref] => venues.venue_id
            [rows] => 7
            [Extra] => Using index
        )

    [17] => Array
        (
            [id] => 1
            [select_type] => PRIMARY
            [table] => features
            [type] => eq_ref
            [possible_keys] => PRIMARY
            [key] => PRIMARY
            [key_len] => 1
            [ref] => serofero_mvb.venue_features.feature_id
            [rows] => 1
            [Extra] => 
        )

    [18] => Array
        (
            [id] => 2
            [select_type] => DERIVED
            [table] => venues
            [type] => ALL
            [possible_keys] => 
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 11
            [Extra] => 
        )

)

谢谢你

php
mysql
EN

回答 3

Stack Overflow用户

发布于 2013-06-13 15:10:35

为所选列创建索引。

票数 2
EN

Stack Overflow用户

发布于 2013-06-13 16:16:54

您需要将查询更改为以下内容；

代码语言：javascript
复制
SELECT DISTINCT venues.`name`, locations.`location`, events.`event`, types.`type`, foods.`food`, beverages.`beverage`, event_options.`event_option`, styles.`style`, space_requirements.`space_requirement`, features.`feature`

请记住，这只是代码的第一行。您需要在查询中的任何地方更改这一点。

之所以会出现此错误，是因为缺少索引，因此索引将以正确的方式定义，并且使php很容易区分。

票数 0
EN

Stack Overflow用户

发布于 2013-06-13 19:00:26

基于EXPLAIN输出，似乎查询已经使用主键作为索引。你为什么不试着分割查询。

首先，获取要显示的场所的ids (分页)。

代码语言：javascript
复制
SELECT id FROM venues v LIMIT $offset,$limit

$venue_ids = implode(',', $results);

接下来，运行此查询。

代码语言：javascript
复制
SELECT DISTINCT venues.name, locations.location, events.event, types.type, foods.food, beverages.beverage, event_options.event_option, styles.style, space_requirements.space_requirement, features.feature

FROM venues

INNER JOIN locations ON venues.location_id = locations.location_id

LEFT JOIN venue_events ON venues.venue_id = venue_events.venue_id
LEFT JOIN events ON events.event_id = venue_events.event_id

LEFT JOIN venue_types ON venues.venue_id = venue_types.venue_id
LEFT JOIN types ON types.type_id = venue_types.type_id

LEFT JOIN venue_foods ON venues.venue_id = venue_foods.venue_id
LEFT JOIN foods ON foods.food_id = venue_foods.food_id

LEFT JOIN venue_beverages ON venues.venue_id = venue_beverages.venue_id
LEFT JOIN beverages ON beverages.beverage_id = venue_beverages.beverage_id

LEFT JOIN venue_event_options ON venues.venue_id = venue_event_options.venue_id
LEFT JOIN event_options ON event_options.event_option_id = venue_event_options.event_option_id

LEFT JOIN venue_styles ON venues.venue_id = venue_styles.venue_id
LEFT JOIN styles ON styles.style_id = venue_styles.style_id

LEFT JOIN venue_space_requirements ON venues.venue_id = venue_space_requirements.venue_id
LEFT JOIN space_requirements ON space_requirements.space_requirement_id = venue_space_requirements.space_requirement_id

LEFT JOIN venue_features ON venues.venue_id = venue_features.venue_id
LEFT JOIN features ON features.feature_id = venue_features.feature_id

WHERE venues.id IN ( $venue_ids )

这至少应该消除派生表。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/17090631

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档