Multiple Join or subquery query optimization SOLVED

Question

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.

Answer 1

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