Re: Query Takes Longer Each Time Its Run

[anthony]

I posted the sample database in the previous post. I've stripped this down and narrowed it to the function call in the where clause. There are four udf functions which calculate the dates of daylight saving start and end, the date of the nth occurrence of a day in the month (ie 3rd sunday of 1-1-2011), and the function which gets called in the query that calculates the local timezone date/time from a gmt date time given the timezone offset values.

I installed this database on 5.1.34 and 5.5.8. Both servers are running similar hardware as described earlier.

Here are three runs of the query on the database on each version. The complete test database dump with 1 table, 626 rows, and 4 udfs is below. Can anyone else confirm these results?

NOTE: In previous posts I was reporting query times of 2.24s and ~+5s each run. That was with ~1600 rows, I trimmed the test case database down to 626 rows which is small enough to still see an appreciable increase in the query time (now ~1 second per query).

In Version 5.1.34:

mysql> show variables like 'version';
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| version | 5.1.34-community-log |
+---------------+----------------------+
1 row in set (0.02 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM records WHERE ConvertGMTDateToLocalDate(ClockIn,-8,1,-7) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59';
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.16 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM records WHERE ConvertGMTDateToLocalDate(ClockIn,-8,1,-7) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59';
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.17 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM records WHERE ConvertGMTDateToLocalDate(ClockIn,-8,1,-7) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59';
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.16 sec)

######################

mysql> show variables like 'version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| version | 5.5.8-log |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM records WHERE ConvertGMTDateToLocalDate(ClockIn,-8,1,-7) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59';
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (.40 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM records WHERE ConvertGMTDateToLocalDate(ClockIn,-8,1,-7) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59';
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (.90 sec)

mysql> SELECT SQL_NO_CACHE count(*) FROM records WHERE ConvertGMTDateToLocalDate(ClockIn,-8,1,-7) BETWEEN '2011-01-01 00:00:00' AND '2011-01-25 23:59:59';
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (1.85 sec)