在当前时间之前查询和返回时间较慢

Question

My site is loading very slowly, it's only a single page that is loading slow, and I suspect it is because of a MySQL query being sent to the database. How can I speed it up?

$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";

The query is basically working to extract information into a 'Departure Board' table. The table is sorted by deptime, and it displays results from current time onwards. However, I also want it to go back at least 4 records, to 4 flights before current time.

To clarify, let me give you an example:

It's now 15:25. Because of if(($flight->deptime) >= $time) and if($count < 15) and inside a foreach it will display 15 records on and after that time. However, I want to also travel back 4 records, so it finds last 4 records before 15:25. Is this possible?

Basically, I want to search up and down around the current time.

Answer 1

You want a UNION, something like:

SELECT * 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
LIMIT 15
UNION ALL
SELECT * 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 DESC
LIMIT 4

Note I didn't guess on your date function because I don't know what it is doing. I just changed the > to a < and the ordering on deptime to DESC. (One of those < > might need to also have an = to not miss a departure right now?)

Using the LIMIT will do the filtering in the DB as opposed to in code which it sounds like you are doing. This will reduce the overhead of processing this information in code and should make it much quicker. I would also sort the UNION of these results in the DB and avoid doing it in code.

EDIT

Just to be completely clear this should handle the sorting in the DB:

SELECT tbl* FROM (
SELECT * 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
LIMIT 15
UNION ALL
SELECT * 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 DESC
LIMIT 4
) as tbl ORDER BY tbl.deptime ASC