CODEWITHSUNDEEP
phpmysql
Rocco The Taco
Rocco The Taco

Reputation: 3797

MySQL using PHP session values slow execution with CURDATE

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'

Upvotes: 0

Views: 56

Answers (1)

Sammitch
Sammitch

Reputation: 32272

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.

Upvotes: 1

Related Questions