CODEWITHSUNDEEP
mysql
user1925041
user1925041

Reputation: 21

Mysql query took long time to execute

The following is my query to get the information from various tables.

I am using mysql database.

 $sql="select c.cid,c.id,c.compid, c.firstname,c.lastname,c.phone,c.home_phone,c.payment,c.status,c.pstatus, c.pstatus2, c.reservation, u.triptype,u.flight_type_arr, u.flight_type_dep, c.email,c.sugg_pickup_date_time,c.sugg_airport_pickup_time,u.triptype,u.traveltype,            
            dr_arr.driver_name as driver_name_arr, dr_arr.driver_email as driver_email_arr, 
            dr_dep.driver_name as driver_name_dep, dr_dep.driver_email as driver_email_dep,
            ar.rid,ar.finalprice1, ar.finalprice2, ap.p1, ap.p2, ap.p3, ap.transaction_id
            from customer c
            left join vehicle_fees vf1 on (vf1.vehicle_id=c.vehicle_id1)
            left join vehicle_fees vf2 on (vf2.vehicle_id=c.vehicle_id2)
            left join userinfo u on (u.id=c.id)
            left join holiday_fees hf1 on (hf1.holidayfee_Date=u.date)
            left join holiday_fees hf2 on (hf2.holidayfee_Date=u.date1)
            left join airport_reservation ar on (ar.cid = c.cid)
            left join driver dr_arr on (dr_arr.did = ar.did_arr)
            left join driver dr_dep on (dr_dep.did = ar.did_dep)
            left join airport_payment ap on (ap.cid = c.cid)
            where (c.reservation = 'booked' or c.reservation = 'cancelled') ORDER BY c.cid DESC" .$var_limit;

But It is taking more time to execute.

Can anybody suggest whether my query is wrong or correct?

Upvotes: 2

Views: 157

Answers (2)

DRapp
DRapp

Reputation: 48139

When doing like you have -- a primary table (Customers) is being joined to all the other "optional" tables, sometimes I've found that MySQL tries to think for you and internally restructure what IT considers is the primary table. By using a known special keyword MIGHT help..

from: 
    SELECT (rest of query)
to:
    SELECT STRAIGHT_JOIN (rest of query)

Now, for your where clause and order by. Ensure you have an index on (reservation, cid)

Upvotes: 1

Oleg Bondarenko
Oleg Bondarenko

Reputation: 111

The query looks legit, the problem that I see is that it has no limits. Usually you should put a LIMIT statement in the end of the query script to limit the amount of rows you want to get. I wonder how many records is there.

Upvotes: 0

Related Questions