MySQL ODBC Connector - Can this SELECT go any faster?

Author

Can this SELECT go any faster?

René Fournier

2006-01-04, 8:24 pm

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_UNI
XTIME(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? unsub...sie.nctu.edu.tw

Gordon Bruce

2006-01-04, 8:24 pm

Try this=20

SELECT replace(left(history
.time_sec,7),'-','') AS month=20
FROM history=20
WHERE history.account_id =3D 216=20
GROUP BY month=20
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 =3D 'CSRB' on =
version 5.0.17.

mysql> SELECT replace(left(pord_Ti
mestamp,7),'-','') AS month
-> FROM product_order_main
-> WHERE perm_user_ID =3D '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 =3D =
'CSRB';
+----------+
| count(*) |
+----------+
| 7095 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from product_order_main WHERE perm_user_ID =3D =
'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=E9 Fournier & #91;mailto:m5@renefo
urnier.com]=20
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 =20
row is UNIX time-stamped, and belong to a particular account.
I would like to know which months a particular account has been =20
active. (For example, maybe one account has been active since June =20
2004, so the SELECT should return every month since then.) Here's =20
what I'm using:

SELECT date_format(FROM_UNI
XTIME(history.time_sec), '%Y%m') AS month
FROM history
WHERE history.account_id =3D 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 =20
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=09
rows: 6556
Extra: Using where; Using temporary; Using filesort

Any ideas how I can speed this up more? (I am just starting to learn =20
how to improve MySQL performance but clearly have a ways to go.) Thanks.

....Rene

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql? unsub...
rint.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

René Fournier

2006-01-04, 8:24 pm

Thanks, but I don't think replace will help me, since my time_sec =20
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=3DMyISAM DEFAULT CHARSET=3Dlatin1 COLLATE=3Dlatin1_gen
eral_ci =20=

AUTO_INCREMENT=3D1 ;

This is why I am formatting time_sec... so I can refer to them as =20
months, e.g.:

SELECT date_format(FROM_UNI
XTIME(history.time_sec), '%Y%m') AS month
FROM history
WHERE history.account_id =3D 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 =3D 216
> GROUP BY month
> ORDER BY history.time_sec DESC;
>
> This is what I get on 1 of my tables with no index on =20
> perm_user_ID , 80,000 rows in the table and 7,000 rows where =20
> perm_user_ID =3D 'CSRB' on version 5.0.17.
>
> mysql> SELECT replace(left(pord_Ti
mestamp,7),'-','') AS month
> -> FROM product_order_main
> -> WHERE perm_user_ID =3D '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 =20
> =3D 'CSRB';
> +----------+
> | count(*) |
> +----------+
> | 7095 |
> +----------+
> 1 row in set (0.05 sec)
>
> mysql> select count(*) from product_order_main WHERE perm_user_ID =20
> =3D '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=E9 Fournier & #91;mailto:m5@renefo
urnier.com]
> 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_UNI
XTIME(history.time_sec), '%Y%m') AS month
> FROM history
> WHERE history.account_id =3D 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=09
> 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.) =20
> Thanks.
>
> ...Rene
>
>
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?=20
> unsub=3Dgordon@mailp
rint.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?=20
> unsub=3Dm5@renefourn
ier.com
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links