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:
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 (
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,