Slow query

[HighFlyerPL185]

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?

[Michael Morris]

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.

[HighFlyerPL185]

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

[Michael Morris]

Add an index on code, see if that helps

[HighFlyerPL185]

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

[Michael Morris]

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.

[HighFlyerPL185]

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.

[guido2004]

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

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.

[HighFlyerPL185]

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.

[guido2004]

ALTER TABLE phpvms_schedules ADD INDEX (code);

I think.

You think correctly

[HighFlyerPL185]

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'

[Michael Morris]

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?

[HighFlyerPL185]

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.

[SpacePhoenix]

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

What is the intended output of the query?

[HighFlyerPL185]

S8D0Mv0.jpg

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.