On 17-Nov-05, at 2:41 PM, [EMAIL PROTECTED] wrote:
René Fournier <[EMAIL PROTECTED]> wrote on 11/17/2005 04:19:25 PM:
OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by-
optimization.html I have learned improved the speed somewhat by
creating a multi-column index on account_id and time_sec, such that:
---------------------------------------------------------------------
---
------
SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month
FROM history
WHERE history.account_id = 216
GROUP BY month
ORDER BY history.time_sec DESC
Showing rows 0 - 5 (6 total, Query took 0.0267 sec)
---------------------------------------------------------------------
---
------
So, about 15x faster. But if I drop "GROUP BY month", it goes really
fast:
---------------------------------------------------------------------
---
------
SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month
FROM history
WHERE history.account_id = 216
ORDER BY history.time_sec DESC
Showing rows 0 - 29 (6248 total, Query took 0.0009 sec)
---------------------------------------------------------------------
---
------
Is it possible to have the "GROUP BY month" as part of a multi-column
index? Or do something to get the speed closer to 0.0009 sec?
...Rene
You are already pushing the limits of total response time:
submit time
+ parse/validate time
+ optimization
+ execution (including: date conversion, grouping, and re-sorting)
+ formating and transmitting output
=====================================
= .0267 sec
If a network or disk access is involved in any part of that chain,
I think
you are optimal (it's hard to get some pings to return in sub .03
seconds). Have you thought about storing your data into a HEAP or
MEMORY
table? That might get more speed out of it. As would using a prepared
statement (reducing the parse/validate portion of the equation).
Why is this query so time-critical, if I may ask?
Well, I have a number of queries that are executed on every page, and
I'm just trying to optimize them. I don't presume to be a DBA, but I
would like to learn how to tune these queries as much as possible...
Thanks for your response. Maybe 0.0267 seconds is as good as it gets.
...Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
