Wednesday, June 11, 2008

MySQL Index varchar fields partially

Once upon a time :) I had a simple table.
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| pnrkey | bigint(20) unsigned | NO | MUL | NULL | |
| pr_aac | char(2) | NO | MUL | NULL | |
| pr_pn_fn | varchar(99) | NO | MUL | NULL | |
| pr_pn_ln | varchar(99) | NO | MUL | NULL | |
+----------+---------------------+------+-----+---------+-------+
I created it using the following create statement..
DROP TABLE IF EXISTS ePDI_passengername;
CREATE TABLE ePDI_passengername (

pnrkey BIGINT UNSIGNED NOT NULL,
pr_aac CHAR(2) NOT NULL,
pr_pn_fn VARCHAR(99) NOT NULL,
pr_pn_ln VARCHAR(99) NOT NULL,

INDEX ePDI_passangername_index_pr_fn(pr_pn_fn),
INDEX ePDI_passangername_index_pr_aac(pr_aac),
INDEX ePDI_segmentData_index_pnrkey(pnrkey),
INDEX ePDI_passengername_index_pn_ln(pr_pn_ln)

)PARTITION BY RANGE(pnrkey)(
PARTITION p0802 VALUES LESS THAN (2008030000001),
PARTITION p0803 VALUES LESS THAN (2008040000001),
PARTITION p0804 VALUES LESS THAN (2008050000001)
);

I added 500,000 records to my table, and saw that the index table MYI was bigger in size than the data one MYD. also I was not happy with the performance of indexes on pr_pn_fn & pr_pn_ln as mysql ignored indexes in like '%string%' kind of searches and the performance was very poor in like 'string%' kind of searches

I shortened the indexes and found improvement in performance

Altering table indexes
alter table ePDI_passengername drop Index ePDI_passangername_index_pr_fn;
alter table ePDI_passengername add Index ePDI_passangername_index_pr_fn(pr_pn_fn(10));

No comments: