I have written this query to extract information from a database to produce a foreach statement for rows in a table. It is however very slow, and it takes between 6-15 seconds to load the page.
$depquery = "SELECT * FROM phpvms_schedules
WHERE code = 'FE'
AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0
AND phpvms_schedules.enabled = '1'
ORDER BY deptime ASC";
$deplist = DB::get_results($depquery);
Then I echo the table and elements inside it, and I use a foreach statement like this…
Run an explain in PHPmyAdmin to get an idea of how the query is being put together and how long each step is taking.
EXPLAIN (
SELECT * FROM phpvms_schedules
WHERE code = 'FE'
AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0
AND phpvms_schedules.enabled = '1'
ORDER BY deptime ASC
)
Pay attention to what indexes it’s choosing. If you have no indexes, that’s likely a large source of the problem.
Either I didn’t index it properly (although it gave me a message it did) or it didn’t sadly make any improvement to the speed of the query I did it using the above code, and also using the checkboxes you’ve mentioned in the table structure.
That’s because when you run the query in PHPMyAdmin you have to manually substitute all PHP variables and constants in the query. In this case TABLE_PREFIX.
If for example the value of TABLE_PREFIX is phpvms_, then that line would become:
AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,phpvms_schedules.daysofweek)>0
So please run the EXPLAIN again on the entire query, let’s see if it uses the index you have created.
I have added an index to the code column, however it is still slow, any ideas? It is slow even if I take out the ‘AND LOCATE (dayofweek…)’ line or 'AND phpvms_schedules.enabled = ‘1’
I’ve removed it and the query was slow without it. Basically, what it does is it gets only the schedules from a table in which ‘daysofweek’ is current day. i.e today’s a Monday. Monday is represented by 1 in ‘daysofweek’ column, hence it will only show schedules with 1 inside that column.
The output I intend the query to generate, is a list of upcoming flight departures for a virtual airline. The query is then ran by a foreach($deplist as $flight) statement which produces table rows, each being a single departure with all the information required.