Optimize complicated sql query by either changing query or indexing

Question

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.

Answer 1

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.

• motel one sun: Thank you so much! This worked great and now the query is taking hardly any time at all.

Answer 2

Date test

time like '$date%'

-->

`time` >= CURDATE()

(This simplification is based on what you said about the origin if $date.)

Then you can use

INDEX(driver_id, `time`)

Don't use IN this way

IN ( SELECT DISTINCT ... )

is almost always better formulated via a JOIN.

Why dup subquery?

You reference 2 columns from the table with the same WHERE clause. You are checking to see if truck_id is in one of them and customer is in the other. Don't you want to check whether both truck_id and customer show up together on the same date? If so, you really need a JOIN.

Switch from MyISAM to InnoDB