slow query - using where (463 289 rows)

radekk · December 30, 2020, 4:19pm

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:

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.

debug · December 30, 2020, 4:28pm

Radek,

It’s because columns which you are using in “WHERE” statement, do not have indexes, that’s why MySQL does full table scan. So workaround is to create indexes…

radekk · December 30, 2020, 4:28pm

So index should be definied for v_uagent, v_ipaddr, v_scres and d_id as well - for that columns which are used in WHERE?

Why you said that it’s a workaround? It’s not a standard method in increasing queries speed? But it’s also connected with disk space which you must reserve for indexes, am I right about this?

sterin · December 30, 2020, 4:28pm

Mikhail just phrased it badly.
It is correct that it is not a workaround it is standard procedure to use indexes to speed up queries. That is their purpose.

And yes they will also require storage space equivalent to the size of the columns part of the index times the nr of rows in the table.

And in your case since you have AND’s between all conditions in your WHERE you can use a combined index to speed it up as much as possible. Which means that one index contains more than one column.

In your case you could create:

ALTER TABLE visits ADD INDEX visits_ix_vi_vu_vs_did_idv ( v_ipaddr, v_uagent, v_scres, d_id, id_visitor);

In this index I’m assuming that IP-address is more unique than your other fields and I also added the column that you are retrieving since then MySQL can avoid reading from the table all together since all data needed for the query is available in the index.