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]
