Slow query

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…

foreach($deplist as $flight) 

How can I speed this query up?

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.

Requesting thread move to the database forum.

I had to remove the following line because of an SQL syntax error:

AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0

Add an index on code, see if that helps

Apologies for a stupid question, but how do you add an index? Never did it before.

ALTER TABLE phpvms_schedules ADD INDEX (code);

I think. There’s a checkbox toggle for it on the table structure tab of the PHPmyAdmin interface.

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 :frowning: 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.

Right. I understand now. I’ve ran EXPLAIN again, and it shows the exact same result as in the thumbnail in my post above.

You think correctly :slight_smile:

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’

hmm…


AND locate(
  dayofweek(
    convert_tz( now(), '+1:00', '+0:00')
  )-1, phpvms_schedules.daysofweek) > 0

It’s likely this. What is the datatype of that field, and what are you attempting to accomplish with this section of the query?

Apologies for a late reply.

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.

Can you please post the output of a SHOW CREATE TABLE for the table?

What is the intended output of the query?

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.