Hi,
My problem is connected with slow query generated by this simple statement:
SELECT id_visitorFROM visitsWHERE v_uagent = 1 AND v_ipaddr = ‘127.0.0.1’ AND v_scres = 1 AND d_id = 1;
it took ~ 10s to complete query.
Table visits
:
CREATE TABLE visits
( id_visitor
int(10) unsigned NOT NULL auto_increment, d_id
int(10) unsigned default NULL, v_ipaddr
varchar(16) default NULL, v_uagent
int(10) unsigned default NULL, v_scres
int(10) unsigned default NULL, v_datetime
datetime default NULL, PRIMARY KEY USING BTREE (id_visitor
)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1 ;
EXPLAIN query:
±—±------------±------------±-----±--------------±-----±--------±-----±-------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------------±-----±--------------±-----±--------±-----±-------±------------+| 1 | SIMPLE | visits | ALL | NULL | NULL | NULL | NULL | 463289 | Using where | ±—±------------±------------±-----±--------------±-----±--------±-----±-------±------------+
It has 463 289 rows and its size is ~ 45,6MB.
MySQL server version: 5.0.45
OS: Linux
I don’t have any idea why this query is so slow. It’s executed ~10 times/sec so load of my mysql server is increased rapidly when this number is bigger than 10 or 20 queries/sec.
It would be great if I can boost my sql query.
/Thanks in advance, Radek.