Question 399452 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Inner join with where clause works fast with one condition, but not when activeFlag is added

I have a query like

select C.customerId, C.firstName, C.LastName, R.IsActive from
Customer as C
Inner join Rider as R ON R.customerId = C.customerid
where R.rideid = 'xyz' and
R.rideareacode = 'abc' and 
R.isActive = 1

This is taking too much like 90 seconds or more. But if I remove the R.IsActive = 1 condition query is taking only a second.
IsActive has only two values 1 or 0, also the Customer and Rider table are big (80k rows)

I tried indexing the IsActive column of my Rider table but still the query is taking too much, I also tried indexing (rideid, rideareacode, isActive) still it is taking too long.

Index_keys for Rider table :

IsActive (nonclustered)
rideid, rideareacode, isActive, userid (nonclustered, unique)
riderid (nonclustered, unique, primary key)

There is no indexing for the customers table.

Tags:

2

Answers


  1. 0

    R needs INDEX(rideid, rideareacode, isActive), with the columns in any order.

    Even better is this "covering index":

    INDEX(rideid, rideareacode, isActive,  -- in any order
      customerId)                      -- after the orhers
    Login or Signup to reply.
  2. 0

    Besides modifying the indexes, you can also add the conditions to the ON clause:

    select C.customerId, C.firstName, C.LastName, R.IsActive from
Customer as C
Inner join Rider as R ON R.customerId = C.customerid and
           R.rideid = 'xyz' and
           R.rideareacode = 'abc' and 
           R.isActive = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search