Strange index behaviour

MySQL & MariaDB Other MySQL® Questions
1

Hello,
My first post here, I hope somebody can help me shed some light on the following behaviour of Percona Server with regard to selects on a table with an index on only part of a char field.

We are using version 5.5.19-55-log Percona Server (GPL), Release 24.0 on Ubuntu 10.04 (LTS) 64-bit. intel Xeon processor.

We have a concordance table objects which looks like this:

CREATE TABLE objects ( object_id int(11) NOT NULL AUTO_INCREMENT, table_id int(11) NOT NULL, UUID char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (object_id), KEY uuid (UUID(13))) ENGINE=InnoDB AUTO_INCREMENT=425289906 DEFAULT CHARSET=utf8

This table holds the mapping between external UUIDs and the internal syntactic keys in our database. It is quite a large table, and we want to have a index that has a good cardinality, but is not larger than necessary.

Since the UUIDs vary much at the start, but at the end just have the mac address of the system I thought I could create an index that just covers the first part of the table as shown above. However, this seems to have very strange effects.

First of all, the index is way too small as seen in this output of show table status:

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment || objects | InnoDB | 10 | Compact | 425289993 | 68 | 29180821504 | 0 | 1605632 | 7340032 | 425289906 | 2012-01-30 12:54:08 | NULL | NULL | utf8_general_ci | NULL | | |

But most importantly, it causes in incorrect results when I do a select:

mysql> select * from objects where object_id=978642; object_id | table_id | UUID || 978642 | 4 | c92c7850-f701-4c12-8272-f9406a30a0a7 |1 row in set (0.13 sec)

But when selecting based on the UUID I get:

mysql> select * from objects where UUID=‘c92c7850-f701-4c12-8272-f9406a30a0a7’;Empty set (0.00 sec)

If I try to get some information on the index I see the following:

mysql> explain select * from wikidata_objects where uuid >= ‘0’ and uuid <= ‘f’;| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra || 1 | SIMPLE | objects | range | uuid | uuid | 39 | NULL | 82324 | Using where |1 row in set (0.00 sec)

So it seems to only have 82k rows in the index out of the 425 million.

I’m puzzled by this behaviour, I would like to get your input on why this is happening and how we could fix it.

With kind regard,

David

2

Early versions had issues with fast index creation. Have you updated from an early version keeping the data files? If this is the case, dropping and recreating the index will solve your issues.

Also note that using UTF-8 causes your char-field and index to be three times larger than necessary.

3

Thank you for your quick reply.

I’m aware that using utf8 on the uuid record makes it larger than necessary. That is a mistake we should fix as well.

The index was created today on this server, so issues with the fast index creation in older versions should play a role.

For the moment we solved it by creating the index on the full width of the uuid field, which took about 4 hours.