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.