Long query times, How can i get this down :(

[graham smart]

Hi Guys,

I have a problem with one of my queries.

It takes far to long to execute and uses too much CPU.

Does anyone know how to make it cleaner?

I have a table of IP addresses with some details in them like country, lat long etc.

My query will simply get the last 30 hits based on IP address and Time for Xsiteid( last 30 IP's )

heres the DB structure..
+-----------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+----------------+
| id | int(15) | NO | PRI | NULL | auto_increment |
| ip | varchar(26) | NO | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | |
| siteid | varchar(30) | NO | | NULL | |
| country | varchar(2) | NO | | NULL | |
| town | varchar(40) | NO | | Unknown | |
| lat | varchar(8) | NO | | Unknown | |
| lon | varchar(8) | NO | | Unknown | |
| extra | varchar(100) | NO | | NULL | |
| requested | int(1) | NO | | 0 | |
| ref | tinytext | NO | | NULL | |
| browser | varchar(100) | NO | | Unknown | |
+-----------+--------------+------+-----+-------------------+----------------+


heres the query:
SELECT DISTINCT * from visitorips where siteid LIKE '372010' order by id desc limit 30;
Ive tried:
SELECT DISTINCT * from visitorips where siteid = '372010' order by id desc limit 30;
SELECT DISTINCT id,ip,ref,etc,etc,etc from visitorips where siteid LIKE '372010' order by id desc limit 30;

But these take just as long ( if longer )

5 rows in set (0.69 sec)

Uses like 150% cpu.

How can i get this down?
Im sure theres a better way to query the table?

It currently has 200k rows. ( last 62 days is all thats kept. )

I have a primary key on ID and nothing else.
However i did try putting an index on other columns and didnt gain any performance increase.

Thanks
G



Edited 1 time(s). Last edit at 02/09/2011 06:56AM by graham smart.