MySQL Optimising Join

Question

I have inherited the following query:

    select [...]
    from
        t_bike_use
            join t_bike
                 on t_bike_use.bid = t_bike.id
            join t_user
                 on t_bike_use.uid = t_user.id
            left join t_violation
                      on t_violation.useid = t_bike_use.id
            left join t_trade
                      on t_bike_use.id = t_trade.record_id
            left join t_admin
                      on t_bike_use.admin_id = t_admin.id
            join t_user_detail
                 on t_user.id = t_user_detail.uid
    group by t_bike_use.id
    order by t_violation.id desc

I'm having trouble getting this join to execute quickly. Currently it takes 1.6s, and since it is executed frequently it tends bring the database to its knees.

The t_bike_use, t_bike, t_user and t_trade tables all have between 2k and 8k rows. However, there are foreign keys between t_bike_use and t_bike and t_user but not one for t_trade.record_id.

If I remove the join with t_trade execution time falls to 400ms.

    left join t_trade
              on t_bike_use.id = t_trade.record_id

I have an index on t_trade.record_id but MySQL 5.6 does not want to use it.

How can this query be optimised?

Here's the output of EXPLAIN.

1  SIMPLE  t_bike_use     ALL     PRIMARY,uuid,fk_uid,fk_bid  <>       <>  <>                   1771  Using temporary; Using filesort
1  SIMPLE  t_violation    ALL     <>                          <>       <>  <>                   1     Using where; Using join buffer (Block Nested Loop)
1  SIMPLE  t_user         eq_ref  PRIMARY                     PRIMARY  4   t_bike_use.uid       1   
1  SIMPLE  t_bike         eq_ref  PRIMARY                     PRIMARY  4   t_bike_use.bid       1   
1  SIMPLE  t_user_detail  ref     fk_uid                      fk_uid   4   t_bike_use.uid       1   
1  SIMPLE  t_trade        ALL     record_id                   <>       <>  <>                   2085  Range checked for each record (index map: 0x2)
1  SIMPLE  t_admin        eq_ref  PRIMARY                     PRIMARY  4   t_bike_use.admin_id  1

I would first check if this query is fully correct. I suspect that not every column is functionally dependent on t_bike_use.id: https://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql — Lukasz Szozda, Apr 11 '19 at 15:05
@LukaszSzozda if I understand correctly, if it was fully correct then I wouldn't need the `group by`? — Tim, Apr 11 '19 at 15:31
@Tim When I see a query with single column in group by(id) then I assume that group by was used to "remove duplicates". The point is if you have columns that are not wrapped by aggregate function MySQL is free to choose any row without specific logic which may lead to serious problems. — Lukasz Szozda, Apr 11 '19 at 15:37
@LukaszSzozda You are quite correct, without the `group by` there are "duplicates". Unfortunately given I've inherited this, I'm not even entirely sure what the correct behaviour is. — Tim, Apr 11 '19 at 15:46

score -1 · Answer 1 · answered Apr 11 '19 at 15:50

-1

You can use sub-query instead Join that makes query execution faster.

answered Apr 11 '19 at 15:50

Tushar

1
1

Can you elaborate? – Tim Apr 11 '19 at 15:55

MySQL Optimising Join

1 Answers1