SQL Query Joins Multiple Tables - Too Slow (8 Tables)

SQL query multiple joins too slow

I was able to cut down the time by 1/2 using subquery instead of join. It takes 1-2 seconds to run the query. Not ideal, but definitely progress. I would still need to join the hotel in subquery for some filtering, but it's still faster.

Not an expert, but I think it helps that I don't join every hotel to every term, but first filter terms and then just select appropriate hotels instead.

SELECT `hotel`.* FROM `hotel` 
INNER JOIN (
    SELECT `term`.`hotel_ID` FROM `term` 
    INNER JOIN `airport_term` ON airport_term.term_ID=term.ID 
    INNER JOIN `airport` ON airport.ID=airport_term.airport_ID WHERE `airport`.`name` IN ('Vienna', 'Berlin', 'Prague') 
    GROUP BY `term`.`hotel_ID`
) `subquery` ON subquery.hotel_ID=hotel.ID ORDER BY `hotel`.`master_rating` DESC

Slow SQL query when join two tables, any way to improve the query speed?

Without seeing the actual data or being able to add indexes, etc it is hard to provide advice, but there a couple of approaches you can try:

a) Use an Exists instead of the JOIN

SELECT B.HOUR, B.LOCATION, B.PRICE, B.TYPE, B.DATE
FROM B
WHERE EXISTS ( SELECT 1 FROM A 
               WHERE A.HOUR=B.HOUR AND A.LOCATION=B.LOCATION AND A.PRICE=B.PRICE AND A.DATE=B.DATE)

b) Group the larger 'B' table in a CTE or into a temp table

;WITH data as (
     SELECT B.HOUR, B.LOCATION, B.PRICE, B.TYPE, B.DATE
     FROM B
     GROUP BY B.HOUR, B.LOCATION, B.PRICE, B.TYPE, B.DATE
)
SELECT Data.HOUR, Data.LOCATION, Data.PRICE, Data.TYPE, Data.DATE
FROM   Data
          INNER JOIN A
            ON A.HOUR=Data.HOUR AND A.LOCATION=Data.LOCATION AND A.PRICE=Data.PRICE AND A.DATE=Data.DATE

It is possible that neither solution will work, but they may be worth a try

Very slow mysql query: left join multiple table and where clause for each table

Try this: Convert your left joins to a bunch of WHERE EXISTS sub-queries and drop the GROUP BY, because (that's my suspicion here) that's what you really wanted to express.

select 
    jj.id, jj.imgtitle, jj.alias
from 
    jjtable jj
    -- you could drop this join, you don't do anything with jjtable_catg
    inner join jjtable_catg jjc on jjc.cid = jj.catid
where
    jj.published = 1
    and jj.approved  = 1 
    and jjc.cid in(4, 10)
    and exists (
      select 1 
      from jjtable_map m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (77) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_per m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (28,36) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_fea m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (87,90) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_ag m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (98,99) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_fa m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (104,107) and t.delete_flag = 0
    )
order by 
    case when date(jj.date) > date_add(date(now()), interval -14 day) then jj.view end DESC, 
    jj.id DESC

Also create these composite indexes (if they're missing):

• jjtable_catg: (cid)
• jjtable_tags: (tid, delete_flag)
• jjtable_map and all jjtable_map_*: (picid, delete_flag, tid)

---

Related Topics