Performance problem with InnoDB/XTraDB; query not using index

[pop]

We are using Percona server 5.5.34-32.0 for Linux x86_64 (rel123.0, Revision 591) and have a table of about 400K records (of XTraDB type). One of the fields is a varchar(512) and has an index built on it. Our query is very simple, like:

select * from config_nodes where path like ‘sites.companyabc_www.merch.models.xxxxxxx.%’;

The problem is that the query optimizer doesn’t use the index for this query (as can be seen by using “explain” command). If we try to do:

select path from config_nodes where path like ‘sites.companyabc_www.merch.models.xxxxxxx.%’;

then the query optimizer is used and the results are retrieved much quicker (220 vs 370 msecs).
I also noticed that when the LIKE operand is short it sometimes allows the optimizer to kick in (depending on the “expected” number of records that XTraDB somehow calculates).

Is there any way to optimize (force using index) the query so that the returned result are obtained by using the index. I played with MariaDB 10.0.10 and the results are much better. Does switching to new Percona 5.6.x should improve the situation?

The schema is pretty simple:

| id | bigint(20) | NO | PRI | NULL
| path | varchar(250) | NO | UNI | NULL
| node_type | char(1) | NO | | NULL
| string_value | mediumtext | YES | | NULL

[niljoshi]

Hi,

Can you provide explain plan of both the queries (select * and select path) and “SHOW CREATE TABLE config_nodes \G” ?
Show create table will show us the indexes are available on that table and index length.
As you are comparing very few characters you can also create “Prefix Index” on that field and check.

[pop]

Hi niljoshi,

Here is the “SHOW CREATE” output (I might changed path definition to be key but it’s the same even if I don’t have it):

mysql> show create table config_nodes \G
*************************** 1. row ***************************
Table: config_nodes
Create Table: CREATE TABLE config_nodes (
id bigint(20) NOT NULL,
path varchar(250) NOT NULL,
node_type char(1) NOT NULL,
string_value mediumtext,
bool_value bit(1) DEFAULT NULL,
long_value bigint(20) DEFAULT NULL,
double_value double DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY path (path),
UNIQUE KEY config_nodes_unique_path (path),
KEY config_nodes_path (path)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The “explain” gives the following:

mysql> explain select path from config_nodes where path like ‘sites.madewell_www.urlNormalization.%’;
±—±------------±-------------±------±------------------------------------------------±-----±--------±-----±-------±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------------±------±------------------------------------------------±-----±--------±-----±-------±-------------------------+
| 1 | SIMPLE | config_nodes | range | path,config_nodes_unique_path,config_nodes_path | path | 252 | NULL | 239746 | Using where; Using index |
±—±------------±-------------±------±------------------------------------------------±-----±--------±-----±-------±-------------------------+
1 row in set (0.00 sec)

mysql> explain select * from config_nodes where path like ‘sites.madewell_www.urlNormalization.%’;
±—±------------±-------------±-----±------------------------------------------------±-----±--------±-----±-------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------------±-----±------------------------------------------------±-----±--------±-----±-------±------------+
| 1 | SIMPLE | config_nodes | ALL | path,config_nodes_unique_path,config_nodes_path | NULL | NULL | NULL | 479493 | Using where |
±—±------------±-------------±-----±------------------------------------------------±-----±--------±-----±-------±------------+

If you take a look at this output you’ll notice “type” of ALL for when the query asks for “*” (all the fields). The number of rows is wrong in this case too.
I have 403132 in this table and in the “explain” command output it gives 479493 which is above the real number.

For your last remark about using a very few characters, I am not. The other query is using around 100 characters as a prefix (or a bit more).
It seams to me that even if I use “FORCE INDEX” which will force the optimizer to use this index doesn’t solve the problem. Here it is:

select * from config_nodes force index (config_nodes_unique_path) where path like ‘sites.madewell_www.urlNormalization.%’;
…results displayed…
21 rows in set (0.79 sec)

And this is slower than when I am not using index at all (result comes out in 0.35 second; compared to the previous 0.79 seconds is twice slower).

And here is its “explain” output:

mysql> explain select * from config_nodes force index (config_nodes_path) where path like ‘sites.madewell_www.urlNormalization.%’;
±—±------------±-------------±------±------------------±------------------±--------±-----±-------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------------±------±------------------±------------------±--------±-----±-------±------------+
| 1 | SIMPLE | config_nodes | range | config_nodes_path | config_nodes_path | 252 | NULL | 239062 | Using where |
±—±------------±-------------±------±------------------±------------------±--------±-----±-------±------------+
1 row in set (0.00 sec)

Quite strange. I don’t know if that’s a problem in query optimizer or something else. And again, I am using the older Percona server. I am talking to our
system admin guys to install the newer Percona on a virtual machine so I can test with this newer server (I presume it’s 5.6.x).

TIA,

Pop

[niljoshi]

Hi,

First, I want to know that why there are three indexes with the same field/column path?

UNIQUE KEY path (path),
UNIQUE KEY config_nodes_unique_path (path),
KEY config_nodes_path (path)

it seems, due to this, optimizer definitely will be confused that which to use with SELECT *. If there are unique values for that column “path” then why not try to remove remaining two indexes which consumes more space and completely unnecessary and then check?