Mysql – Optimize complicated sql query by either changing query or indexing

MySQLperformancequery-performance

I'm not great with SQL and I started working at a place and they have a query that is running slow. This SQL query is taking about 4 seconds to load and is coming from a table with about 160,000 rows. It's a MyISAM table and we're using MySQL. Here is the query:

$driver = 28;          //for some reason they're using a magic number here
$date = $theDate;      //the date is current date in datetime format
SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver' 
AND truck_id in 
(Select distinct truck_id from jobs where driver_id ='$driver' and time like '$date%' 
AND customer IN
(Select distinct customer from jobs where driver_id ='$driver' and time like '$date%' )) 
and time like '$date%' ORDER BY customer, truck_id ASC;

Here is the jobs table structure:

id              int(11)
driver_id       int(11)
truck_id        int(11)
job_id          int(11)
mileage_start   int(11)
mileage_end     int(11)
mileage_total   int(11)
time            datetime
customer        int(11)
address         text
city            text
state           varchar(255)
zip             varchar(255)
phone           varchar(255)
...

I'm trying to get the query time to be much less than 4 seconds. I just started at this place and changing the whole table structure might break a bunch of other stuff I'm not aware of yet. Every column is indexed as BTREE (which I guess is the default?) so I'm wondering if what I need to do is either redo the query or index the used columns differently? I'm not experienced enough with SQL to really know what to do. Thank you for the input.

Best Answer

I dont have the reputation to add comments so I will take a stab at an answer. My experience is with MSSQL but I think it is transferable here.

It seems like this query is doing a bunch of redundant checks.

Do you get different results from this:

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver' 
AND truck_id in 
(Select distinct truck_id from jobs where driver_id ='$driver' and time like '$date%' 
AND customer IN
(Select distinct customer from jobs where driver_id ='$driver' and time like '$date%' )) 
and time like '$date%' ORDER BY customer, truck_id ASC;

than you do from this?:

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
and time like '$date%' ORDER BY customer, truck_id ASC;

The check for customer and truck in the table with the same conditions as the main query should not be necessary.