CODEWITHSUNDEEP
phpmysqlsqlperformance
Bijan
Bijan

Reputation: 8602

MySQL cannot run query for time restriction

I have a MySQL query I am running on my website that is like:

SELECT
    [random columns]
FROM
    DB_Name
WHERE
    EndDate > unix_timestamp(now())
    AND StartDate <= UTC_DATE()

In my Database EndDate is the epoch date and StartDate is the date in YYYY-MM-DD format.

Running this query is totally fine and it is able to run in like 0.1seconds. The WHERE clauses shows rows that have already started but are ending in the future.

I have a section of my website that specifically shows rows that are ending in the next 24hours. So I change the EndDate filter to be EndDate <= unix_timestamp(now() + interval 1 day) AND StartDate <= UTC_DATE() but this makes the query take several minutes.

Running SELECT COUNT(*) FROM DB_Name WHERE EndDate filter to be EndDate <= unix_timestamp(now() + interval 1 day) returns ~400K rows. By comparison, there are only 3K rows for WHERE EndDate > unix_timestamp(now() + interval 1 day)

Is there a faster way to make this comparison without it taking minutes to complete?

Upvotes: 0

Views: 53

Answers (1)

PlayerKillerYKT
PlayerKillerYKT

Reputation: 294

I think u need this SQL, if u need filter further end strict in 1 day:

SELECT
    [random columns]
FROM
    DB_Name
WHERE
    EndDate > unix_timestamp(now())
    AND EndDate <= unix_timestamp(now() + interval 1 day)
    AND StartDate <= UTC_DATE()

Upvotes: 4

Related Questions