CODEWITHSUNDEEP
mysqlsqlinner-join
Mostav
Mostav

Reputation: 2612

SQL INNER join take too long to execute

I have below sql query that take too long to execute, please there is an alternative for inner joins to resolve my issue?, because when I remove 3 or 4 joins the query is executed faster.

SELECT  p.first_name, p.last_name, p.x, p.dob,'name', w.id_table1, w.type, w.stype, w.ploc, w.dat, w.created_at,  av.item as table1_edge , av1.item as sdf ,av2.item as sd, av3.item as odor, av4.item as xz, av5.value as xxx , av9.item as cc, av10.item as cxz                        
                    FROM table1 w 
                    INNER JOIN table2 a  ON w.id_table1 = a.table1_id_table1
                    INNER JOIN table3 av ON a.id_table2 = av.id_table3 and av.sub_group = 'x1'
                    INNER JOIN table3 av1 ON a.id_table2 = av1.id_table3 and av1.sub_group = 'x2'
                    INNER JOIN table3 av2 ON a.id_table2 = av2.id_table3 and av2.sub_group = 'x3'
                    INNER JOIN table3 av3 ON a.id_table2 = av3.id_table3 and av3.sub_group = 'x4'
                    INNER JOIN table3 av4 ON a.id_table2 = av4.id_table3 and av4.sub_group = 'x5'
                    INNER JOIN table3 av5 ON a.id_table2 = av5.id_table3 and av5.sub_group = 'x6' and av6.item like 's%'
                    INNER JOIN table3 av6 ON a.id_table2 = av6.id_table3 and av6.sub_group = 'x7' and av6.item like 'x%'
                    INNER JOIN table3 av7 ON a.id_table2 = av7.id_table3 and av7.sub_group = 'x8' and av6.item like 'z%'
                    INNER JOIN table3 av8 ON a.id_table2 = av8.id_table3 and av8.sub_group = 'x9' and av6.item like 'y%' 
                    INNER JOIN table3 av9 ON a.id_table2 = av9.id_table3 and av9.sub_group = 'x10'
                    INNER JOIN table3 av10 ON a.id_table2 = av10.id_table3 and av10.sub_group = 'x11'
                    INNER JOIN table0 p ON w.table0_id_table0 = p.id_table0
                    where w.created_at between '1991-12-09 00:00:00' and now() and user_id_user = 4

data:
table0
 first_name
last_name
dob
...
table1
id_table1
type
stype
...

Upvotes: 1

Views: 2030

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to be storing things in an EAV format (entity-attribute-value). If you don't understand what this means, you can review the definitions on Wikipedia.

One solution is to use aggregation:

SELECT p.first_name, p.last_name, p.x, p.dob, 'name',
       w.id_table1, w.type, w.secondary_type, w.primary_location, 
       w.onset, w.created_at, 
       MAX(CASE WHEN av.sub_group = 'x1' THEN av.item END) as table1_edge,
       MAX(CASE WHEN av.sub_group = 'x1' THEN av.item END) as sdf,
       . . .                      
FROM table1 w INNER JOIN
     table2 a 
     ON w.id_table1 = a.table1_id_table1 INNER JOIN
     table3 av
     ON a.id_table2 = av.id_table3 
WHERE av.sub_group IN ('x1', 'x2', . . . ) AND
      w.created_at between '1991-12-09' and now() AND
      user_id_user = 4
GROUP BY p.first_name, p.last_name, p.x, p.dob, 
         w.id_table1, w.type, w.secondary_type, w.primary_location, w.onset, w.created_at;

You also want appropriate indexes. I am guessing these are table1(user_id_user, created_at, id_table1) and table3(id_table3, sub_group, item).

Upvotes: 0

Evgeny
Evgeny

Reputation: 4010

Maybe you can make you query simpler like this:

SELECT  p.first_name, p.last_name, p.x, p.dob,'name', 
    w.id_table1, w.type, w.secondary_type, w.primary_location,
    w.onset, w.created_at, av.item,
    IF(av.sub_group='x1', 1, 0) as val_x1, 
    IF(av.sub_group='x2', 1, 0) as val_x2, 
    IF(av.sub_group='x3', 1, 0) as val_x3,
               ..........
    IF(av.sub_group='x11', 1, 0) as val_x11                         
FROM table1 w 
INNER JOIN table2 a ON w.id_table1 = a.table1_id_table1
left join table3 av on a.id_table2 = av.id_table3
INNER JOIN table0 p ON w.table0_id_table0 = p.id_table0
where w.created_at between '1991-12-09 00:00:00' 
    and now() and user_id_user = 4
    and  ( (av.sub_group in ('x1', 'x2', 'x3', 'x4', 'x5', 'x10', 'x11'))
       or (av.sub_group='x6' and av.item like 's%') 
       or (av.sub_group='x7' and av.item like 'x%') 
       or (av.sub_group='x8' and av.item like 'z%') 
       or (av.sub_group='x9' and av.item like 'y%')
      )

We make just one JOIN with table table3 and move all conditions into where clause. With val_x1 .... val_x11 we can know which one value we have at av.item.

Upvotes: 1

user2789880
user2789880

Reputation: 107

Have you tried to use subqueries instead for the inner joins that take longer to execute?

Example:

select a.id, b.id, c.id
from table a
inner join (
   select id
   from tableb
   where b = 'x1'
) as b on b.id = a.id
inner join (
   select id
   from tablec
   where c = 'x2'
) as c on c.id = a.id

Upvotes: 1

Related Questions