多重Join或子查询查询优化

发布于 2024-08-07 01:14:46 字数 696 浏览 3 评论 0原文

我有这个查询，对于给定的情况，它会在 1 或 2 秒内执行：

    Select Count(*) as qtty  
    From event e  
    Join org o On o.orgID = e.orgID  
    Join venue v On v.venueID = e.venueID  
    Where Match( e.name, e.description ) Against ( $keywords )  
        And e.site_id = $site_id  
        And e.display <> 0</code>

它计算行数以构建分页。当我引入按事件类型过滤（类型与事件多对多相关）时，查询开始花费不少于 45 秒的时间：

    And Exists (   
      Select ete.id  
      From event_type_to_event ete   
      Where ete.event_id = e.eventID  
      And ete.event_type_id = $category )</code>

我还尝试了使用 event_type_to_event 进行连接，但速度更慢。
有什么建议吗？

注：已解决。使用索引，查询执行时间降至不到一秒。

原文

I have this query, which executes in 1 or 2 seconds for a given case:

    Select Count(*) as qtty  
    From event e  
    Join org o On o.orgID = e.orgID  
    Join venue v On v.venueID = e.venueID  
    Where Match( e.name, e.description ) Against ( $keywords )  
        And e.site_id = $site_id  
        And e.display <> 0</code>

It counts the rows to build the pagination. When I introduced filtering by event type (types are related many to many to events) the query started taking no less than 45 seconds:

    And Exists (   
      Select ete.id  
      From event_type_to_event ete   
      Where ete.event_id = e.eventID  
      And ete.event_type_id = $category )</code>

I also tried a Join with event_type_to_event but it was even slower.
Any suggestions?

NOTE: Solved. Using indices, the query execution time went down to less than a second.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不爱素颜 2024-08-14 01:14:46

我怀疑您需要在表 event_type_to_event 中的 event_type_id 列上添加索引，但如果那里已经有索引，请尝试以下操作：

Select Count(*) as qtty
From event e
   Join org o On o.orgID = e.orgID
   Join venue v On v.venueID = e.venueID
Where Match( e.name, e.description ) Against ( $keywords )
   And e.site_id = $site_id
   And e.display <> 0
   And Exists 
      (Select * From event_type_to_event 
       Where event_id = e.eventID
          And event_type_id = $category)

如果 Event_Id 是表 event_type_to_event 的 PK，您也可以尝试 连接而不是使用 Exists，

Select Count(*) as qtty
From event e
   Join org o On o.orgID = e.orgID
   Join venue v On v.venueID = e.venueID
   Join event_type_to_event t
       On t.event_id = = e.eventID
Where Match( e.name, e.description ) Against ( $keywords )
   And e.site_id = $site_id
   And e.display <> 0
   And t.event_type_id = $category

I suspect you need to add an index on the column event_type_id in table event_type_to_event, but if there is already an index there, then try the following:

Select Count(*) as qtty
From event e
   Join org o On o.orgID = e.orgID
   Join venue v On v.venueID = e.venueID
Where Match( e.name, e.description ) Against ( $keywords )
   And e.site_id = $site_id
   And e.display <> 0
   And Exists 
      (Select * From event_type_to_event 
       Where event_id = e.eventID
          And event_type_id = $category)

If Event_Id is the PK of table event_type_to_event you can also try a join instead of using Exists,

Select Count(*) as qtty
From event e
   Join org o On o.orgID = e.orgID
   Join venue v On v.venueID = e.venueID
   Join event_type_to_event t
       On t.event_id = = e.eventID
Where Match( e.name, e.description ) Against ( $keywords )
   And e.site_id = $site_id
   And e.display <> 0
   And t.event_type_id = $category

回复收藏 0 原文

~没有更多了~