Simple query slow on large table

Hi Everyone,
=20
I'm having a very simple query often take several seconds to run and
would be hugely grateful for any advice on how i might spped this up.
=20
The table contains around 500k rows and the structure is as follows:
=20
+-----------+--------------+------+-----+------------------- +-----------
-----+
| Field | Type | Null | Key | Default | Extra
|
+-----------+--------------+------+-----+------------------- +-----------
-----+
| ID | int(11) | | PRI | NULL |
auto_increment |
| siteid | int(11) | | MUL | 0 |
|
| sender | varchar(255) | | | |
|
| subject | varchar(255) | | MUL | |
|
| message | text | | | |
|
| datestamp | timestamp | YES | MUL | CURRENT_TIMESTAMP |
|
| msgtype | int(1) | | MUL | 0 |
|
| isread | int(1) | | | 0 |
|
+-----------+--------------+------+-----+------------------- +-----------
-----+

I have indexes on siteid, datestamp and msgtype.

Queries such as the following are constantly appearing in the slow
queries log:

SELECT * FROM enquiries WHERE siteid =3D 59255 AND msgtype =3D 0 ORDER =
BY
datestamp DESC LIMIT 5;

An EXPLAIN on the above query returns:

+----+-------------+-----------+------+----------------+---- ----+-------
--+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------+------+----------------+---- ----+-------
--+-------+------+-----------------------------+
| 1 | SIMPLE | enquiries | ref | siteid,msgtype | siteid |
4 | const | 1940 | Using where; Using filesort |
+----+-------------+-----------+------+----------------+---- ----+-------
--+-------+------+-----------------------------+

Shouldn't MySQL be using the datestamp index for sorting the records?
When I remove the ORDER BY clause the query is considerably faster. Do
I need to do something to make sure it using the index when sorting?

Any help will be greatly appreciated!

Regards

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Simple query slow on large table

Simon Kimber schrieb:
> Hi Everyone,
>
> I'm having a very simple query often take several seconds to run and
> would be hugely grateful for any advice on how i might spped this up.
>
> The table contains around 500k rows and the structure is as follows:
>
> +-----------+--------------+------+-----+------------------- +-----------
> -----+
> | Field | Type | Null | Key | Default | Extra
> |
> +-----------+--------------+------+-----+------------------- +-----------
> -----+
> | ID | int(11) | | PRI | NULL |
> auto_increment |
> | siteid | int(11) | | MUL | 0 |
> |
> | sender | varchar(255) | | | |
> |
> | subject | varchar(255) | | MUL | |
> |
> | message | text | | | |
> |
> | datestamp | timestamp | YES | MUL | CURRENT_TIMESTAMP |
> |
> | msgtype | int(1) | | MUL | 0 |
> |
> | isread | int(1) | | | 0 |
> |
> +-----------+--------------+------+-----+------------------- +-----------
> -----+
>
> I have indexes on siteid, datestamp and msgtype.
>
> Queries such as the following are constantly appearing in the slow
> queries log:
>
> SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY
> datestamp DESC LIMIT 5;
>
> An EXPLAIN on the above query returns:
>
> +----+-------------+-----------+------+----------------+---- ----+-------
> --+-------+------+-----------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+-----------+------+----------------+---- ----+-------
> --+-------+------+-----------------------------+
> | 1 | SIMPLE | enquiries | ref | siteid,msgtype | siteid |
> 4 | const | 1940 | Using where; Using filesort |
> +----+-------------+-----------+------+----------------+---- ----+-------
> --+-------+------+-----------------------------+
>
> Shouldn't MySQL be using the datestamp index for sorting the records?
> When I remove the ORDER BY clause the query is considerably faster. Do
> I need to do something to make sure it using the index when sorting?
>
> Any help will be greatly appreciated!
>
> Regards
>

hi Simon,
you can try a "join" see http://www.artfulsoftware.com/infotree/queries.php for hints.

sql is pretty bad for time series data.
IMHO is the most obvious thing to reduce the number entries in your table.
(do you realy need ID when you have a timestamp ?, etc)

Otherwise the other stuff like: myisam instead of immodb but this depends on
your requirements.


re,
wh

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Simple query slow on large table

On Tue, Aug 18, 2009 at 5:08 AM, Simon Kimber wrote:
> I have indexes on siteid, datestamp and msgtype.
>
> Queries such as the following are constantly appearing in the slow
> queries log:
>
> SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY
> datestamp DESC LIMIT 5;

Read the explanation of ORDER BY optimization:
http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization .html

As it explains, you aren't providing a key it can use. If you create
a multi-column index on siteid, msgtype, and datestamp, that will
probably fix it.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org