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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
