Q:

Why this query is to slow in mysql

I try to improve this query but I do not made successefully. I use a some left join and subquerys (I don't know another form) We try to get all bookings from users with certain status and the number of bookings multiples related with this booking and get from the log user the origin. The query is slow even if use a limit clausure. I Appreciate all the help can you give me.
This is the query:
SELECT DISTINCT b.uneaque_id, b.id, b.status, b.route_status, b.username, b.purpose, b.transfer, b.pickup_date, b.pickup_time, b.return_time, b.amount, b.default_location, b.start_address_route_comments, b.start_address_route, b.end_address_route_comments, b.end_address_route, u1.first_name, u1.last_name, b.transaction_Id, b.manual_payment, mr.AddressCount, lu.origin 
        FROM bookingdetails b 
        LEFT JOIN log_users lu ON lu.uneaque_id = b.uneaque_id AND lu.command_type = 'NEW BOOKING' 
        LEFT JOIN ( 
            SELECT booking_id, COUNT(*) AS AddressCount FROM booking_multiple_rides GROUP BY booking_id
        ) mr ON b.id = mr.booking_id,
        userdetails u1 WHERE b.username = u1.email 
        AND u1.user_status IN ('Active', 'Blocked', 'Not_Active')
        AND b.default_location = 1

PD: Sorry for my english.

A:

You have a ON b.id = mr.booking_id, userdetails u1  WHERE
you should change with a proper inner join  
SELECT DISTINCT b.uneaque_id
    , b.id, b.status
    , b.route_status
    , b.username
    , b.purpose
    , b.transfer
    , b.pickup_date
    , b.pickup_time
    , b.return_time
    , b.amount
    , b.default_location
    , b.start_address_route_comments
    , b.start_address_route
    , b.end_address_route_comments
    , b.end_address_route
    , u1.first_name
    , u1.last_name
    , b.transaction_Id
    , b.manual_payment
    , mr.AddressCount
    , lu.origin 
FROM bookingdetails b 
LEFT JOIN log_users lu ON lu.uneaque_id = b.uneaque_id AND lu.command_type = 'NEW BOOKING' 
LEFT JOIN ( 
        SELECT booking_id
          , COUNT(*) AS AddressCount 
          FROM booking_multiple_rides GROUP BY booking_id
) mr ON b.id = mr.booking_id
INNER JOIN userdetails u1 ON  b.username = u1.email 
AND u1.user_status IN ('Active', 'Blocked', 'Not_Active')
AND b.default_location = 1

and be sure you have proper index  on
table bookingdetails a composite index on   columns ( uneaque_id , id, default_location) 
table  log_users a composite index  on  columns  (uneaque_id, command_type)
table  userdetails a cmposite index on  columns (email,user_status )

