MySQL Query Optimization?

Question

So I have a DB with ~5 million rows and I am trying to optimize the load times. My DB has columns and indexes as optimized as I can think so I assume its the query. This query runs on a table with ~5 million rows and returns 40 of them, but it takes 101 seconds.

If I remove the timezone conversions the same query takes 0.0015 seconds but returns slightly different results because of the timezone difference. How could I optimize to get the correct results with more speed?

SELECT *, date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) as `timezoneDate`
FROM `transactions`
WHERE `isValid` = X
AND `storeID` = X
AND date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) >= '2014-11-19'
AND date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) <= '2014-11-25'
ORDER BY `eventDate`

Also if it was not clear before, the second timezone I am converting to changes depending on user, so right now its America/New_York which is the same as US/Eastern but it changes.

you can't. you're using derived values, and that means no indexes. e.g. where foo=1 is fine and could use any applicable indexes. where somefunc(foo)=1 isn't, because the result of somefunc is unindexed. — Marc B
– Marc B, Commented Oct 26, 2015 at 15:42
Would it be faster to for example, remove the timezone restrictions, grab the results with + and - one day, then run a PHP loop t o calculate and remove extra results? I figure running a timezone convert on 10-12 extra rows and removing some would be faster than 100 seconds. — CMOS
– CMOS, Commented Oct 26, 2015 at 15:44
If the number returned is small enough, perhaps run it without the calls to CONVERT_TZ and a slightly wider net (2014-11-18 to 2014-11-26 perhaps), then narrow it after. doh, that occured to you while I was typing, sorry. — TZHX
– TZHX, Commented Oct 26, 2015 at 15:44
Is the query slow with timezone conversion in only the select as well? How rows are returned in the 0.0015 seconds? — JRD
– JRD, Commented Oct 26, 2015 at 15:45
reverse the logic. do the tz/time conversion on your fixed values , e.g. eventdate = date(convert_tz('2014-11-19'))-type thing. then you CAN use indexes. because the indexed field is being used "raw". — Marc B
– Marc B, Commented Oct 26, 2015 at 15:49

CMOS · Accepted Answer · 2015-10-26 16:46:14Z

2

What I ended up doing was a mix of JRD, TZHX, and MarcB's comments. I ended up doing a select without the time zone but increasing my selection radius by plus and minus one day. This got my ~5 million rows down to about 50, however since it was slightly more than I wanted I then ran the original query but only on the 50 results returned by the first select. This resulted in all of the exact same data being returned but in only 0.11 seconds. Thanks so much guys!

answered Oct 26, 2015 at 16:46

CMOS

2,9178 gold badges50 silver badges87 bronze badges

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

Comments

JRD · Accepted Answer · 2015-10-26 15:51:33Z

1

What about postponing the date filtering:

select * from (     
SELECT *, date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) as `timezoneDate`
    FROM `transactions`
    WHERE `isValid` = X
    AND `storeID` = X
)
where timezoneDate between '2014-11-19' and '2014-11-25'
ORDER BY timezoneDate

answered Oct 26, 2015 at 15:51

JRD

1,9871 gold badge13 silver badges17 bronze badges

2 Comments

Rick James Over a year ago

Won't work as spelled out because WHERE requires expressions, not aliases.

JRD Over a year ago

Say what? I don't follow. select alias from (select 'text' as alias) q where alias = 'text'; is perfectly valid.

Gordon Linoff · Accepted Answer · 2015-10-26 16:19:16Z

0

Not knowing your data, you should be sure that you have an index on transactions(storeId, isvalid, eventdate).

If this doesn't help performance, then your options are more limited. One is to define a "canonical" event time for New York. This requires a trigger to do the conversion.

A second option is similar, but it involves keeping the time difference as a separate column.

I would suggest time zone conversions on the constants, but the two time zones should be the same. I don't know why they would be returning different results.

edited Oct 26, 2015 at 16:19

answered Oct 26, 2015 at 16:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Query Optimization?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related