MySQL using PHP session values slow execution with CURDATE

Question

I've got a series of MySQL recordsets and queries on a PHP control panel/admin screen I inherited. I've found two queries that seem to be especially slow and both use CURDATE so I think that may be the issue or at least that seems to be the shared element that makes them different from the other queries outside of being a bit more complex.

I condensed the statements to use the IN statement where I could optimize it but am at a loss as to what else I could do to speed either of these queries up or substitute out the CURDATE for something better?

When I remove everything after the PHP session value including the CURDATE and other AND/OR statements it obviously speeds up the query.

Query 1:

SELECT COUNT(ID) AS total_count
FROM HTG_ScheduleRequest
WHERE AssignedTech = '".$emp."'
    AND (ScheduleDateExact > CURDATE())
    AND JobStatus IN (2,5,8)
    AND RecordType = '1'
    OR SSR = '".$emp."'
    AND (ScheduleDateCurrent > CURDATE())
    AND JobStatus IN (3,4)
    AND RecordType = '1'

Query 2:

SELECT COUNT(ID) AS total_count
FROM HTG_ScheduleRequest
WHERE AssignedTech = '".$emp."'
    AND JobStatus IN (2,5,8)
    AND ScheduleDateExact >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)
    AND ScheduleDateExact < DATE_ADD(CURDATE(), INTERVAL 2 DAY)
    AND RecordType = '1'
    OR SSR = '".$emp."'
    AND JobStatus IN (3,4)
    AND ScheduleDateCurrent >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)
    AND ScheduleDateCurrent < DATE_ADD(CURDATE(), INTERVAL 2 DAY)
    AND RecordType = '1'

Sammitch · Accepted Answer · 2015-11-10 22:12:55Z

1

After reformatting your query it looks like your logic is broken. I believe you want:

SELECT COUNT(ID) AS total_count
FROM HTG_ScheduleRequest
WHERE (
        AssignedTech = '".$emp."'
        AND (ScheduleDateExact > CURDATE())
        AND JobStatus IN (2,5,8)
        AND RecordType = '1'
    ) OR (
        SSR = '".$emp."'
        AND (ScheduleDateCurrent > CURDATE())
        AND JobStatus IN (3,4)
        AND RecordType = '1'
    )

That aside, you query looks fine and the particulars of the date function should not be affecting anything. I would suggest looking at the EXPLAIN output for these queries and see if you need to define or adjust the indexes on the table.

answered Nov 10, 2015 at 22:12

Sammitch

32.5k7 gold badges58 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rocco The Taco Over a year ago

That helped, I'll check out the EXPLAIN to see about any missing indexes too, thanks again!

Collectives™ on Stack Overflow

MySQL using PHP session values slow execution with CURDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related