Mysql – 60M+ InnoDB table simple query performance HELP

innodbMySQLoptimizationquery-performance

I know it is a repeated question but really need help here.
What can I do to improve the performance of this query?
Considering that I have no freedom to change system variables/configs 🙁
Thanks in advance!!!

CREATE TABLE `t1` (
  `pinc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pinc_codigo` char(10) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`pinc_id`,`pinc_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

mysql> EXPLAIN SELECT pinc_codigo FROM t1 WHERE pinc_codigo='somechar10';
+----+-------------+---------------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |
+----+-------------+---------------+-------+---------------+---------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | t1 | index | NULL          | PRIMARY | 14      | NULL | 69000333 | Using where; Using index |
+----+-------------+---------------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT pinc_codigo FROM t1 WHERE pinc_codigo='somechar10';
+-------------+
| pinc_codigo |
+-------------+
| somechar10  |
+-------------+
1 row in set (30.41 sec)

Best Answer

Refer to Johan's answer here, particularly the bit about covering indexes in InnoDB.

My understanding of what he has written is that you should change your table definition to the following

CREATE TABLE `t1` (
  `pinc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pinc_codigo` char(10) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`pinc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

ALTER TABLE `t1` ADD INDEX `t1_idx_pinc_cogido` (`pinc_cogido`);

Could you do this on test and report back?

[EDIT]

It's not very clear from your post, but is pinc_cogido unique? If so, you should use

ALTER TABLE `t1` ADD UNIQUE INDEX `t1_uidx_pinc_cogido` (`pinc_cogido`);

(also, slight change - it's good practice to name all your indexes)

PRIMARY KEY `t1_pk_pinc_id' (`pinc_id`);

[EDIT2]

In answer to this

Thankx @Veráce ! No, no other fields in the table just this two

Is there any point in having a table with 1 field? Could you explain the thinking here? Is your pinc_cogido field a lookup?

The table structure should not be IMHO, since pinc_cogido is both UNIQUE and NOT NULL.

CREATE TABLE `t1` (
  `pinc_codigo` char(10) COLLATE latin1_general_ci NOT NULL,
  `other_field1` ....,
  `other_field2` ....,
  `...`,
  PRIMARY KEY `t1_pk_pinc_cogido` (`pinc_cogido`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;