Thanks, but I don't think replace will help me, since my time_sec
column is not DATE. Here's the table def (well, the part that matters):
CREATE TABLE history (
id int(10) unsigned NOT NULL auto_increment,
time_sec int(10) unsigned NOT NULL default '0',
time_msec smallint(5) unsigned NOT NULL default '0',
amount int(11) NOT NULL default '0',
account_id int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY account_id (account_id),
KEY time_sec (time_sec),
KEY time_msec (time_msec),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=1 ;
This is why I am formatting time_sec... so I can refer to them as
months, e.g.:
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
...Rene
On 4-Jan-06, at 2:05 PM, Gordon Bruce wrote:
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/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
