Try this SELECT replace(left(history.time_sec,7),'-','') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC;
This is what I get on 1 of my tables with no index on perm_user_ID , 80,000 rows in the table and 7,000 rows where perm_user_ID = 'CSRB' on version 5.0.17. mysql> SELECT replace(left(pord_Timestamp,7),'-','') AS month -> FROM product_order_main -> WHERE perm_user_ID = 'CSRB' -> GROUP BY month -> ORDER BY pord_Timestamp DESC; +--------+ | month | +--------+ | 200511 | | 200510 | | 200509 | | 200508 | | 200507 | | 200506 | | 200505 | | 200504 | | 200503 | | 200502 | | 200501 | | 200412 | | 200411 | | 200410 | | 200409 | | 200408 | | 200407 | | 200406 | | 200405 | | 200404 | | 200403 | | 200402 | | 200401 | | 200312 | | 200311 | +--------+ 25 rows in set (0.08 sec) mysql> select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +----------+ | count(*) | +----------+ | 7095 | +----------+ 1 row in set (0.05 sec) mysql> select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +----------+ | count(*) | +----------+ | 7095 | +----------+ 1 row in set (0.05 sec) mysql> select count(*) from product_order_main; +----------+ | count(*) | +----------+ | 80774 | +----------+ 1 row in set (0.05 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.17-nt | +-----------+ 1 row in set (0.00 sec) -----Original Message----- From: René Fournier [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 2:23 PM To: mysql@lists.mysql.com Subject: Can this SELECT go any faster? Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: 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 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]