innodb DEPENDENT SUBQUERY very slow

Here is some innodb table. It insert,update,query very frequently.

tn is product id

col is product sub id

tindex is product description.(store by each word)

date is product add date stored by strototime('now')

CREATE TABLE IF NOT EXISTS `mytable` (
  `tn` varchar(15) NOT NULL,
  `col` smallint(2) NOT NULL,
  `tindex` varchar(30) NOT NULL,
  `date` int(10) NOT NULL,
  KEY `date` (`date`),
  KEY `tn` (`tn`),
  KEY `tindex` (`tindex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How I want to query some products which sub id contains words 'anticorrosive' and 'waterproof'

SELECT * 
FROM mytable a
WHERE tindex =  'anticorrosive'
AND EXISTS (
SELECT 1 
FROM mytable
WHERE tn = a.tn
AND col = a.col
AND tindex =  'waterproof'
)
ORDER BY DATE
LIMIT 10

full table is neally 12,700,000 lines, 1.1GB ,it cost 4.8356s, explian the query get

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY a   ref tn,tindex   tindex  92  const   1173    Using where; Using filesort
2   DEPENDENT SUBQUERY  mytable ref tn,tindex   tn  47  production.a.tn 161 Using where

And here is my.ini,

[mysqld]
character_set_server=utf8
skip-external-locking
skip-networking
key_buffer = 256M
tmp_table_size = 128M
max_connections = 1024
wait_timeout=10
back_log = 2048
key_buffer_size = 256M
max_allowed_packet = 2M
table_cache = 2048
table_open_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 4M
net_buffer_length = 92K
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache = 512
query_cache_size= 256M
bulk_insert_buffer_size = 192M
ft_min_word_len=2
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 2

How to speed up the query? re-do the index? query code? or change some value in my.ini

UPDATE 1
I have tried change index kye, and my table still grow up, now it has neally 20mln lines 2.2GB

```
ALTER TABLE mytable ADD UNIQUE INDEX (tn,col,tindex);
ALTER TABLE mytable ADD INDEX (

Some notes:

• Why is there no PRIMARY KEY in the table?

• Why date is of type int and not date or datetime or timestamp?

• Why is it called date when it stores date and time?

Regarding the actual question, the efficiency of the query, an index on (tindex, tn, col, date) would help in my opinion much more than the other suggestions. And since you have no primary key and the (tindex, tn, col) is unique (you have added another unique index), my suggestion is:

• (optionally) drop that unique index)

-
define the primary key as (tindex, tn, col):

ALTER TABLE mytable
  DROP INDEX __the_name_of_the_unique_index,  -- this is optional
  ADD PRIMARY KEY (tindex, tn, col) ;

This will take some time (that's why I suggest you do both operations in one pass.)

Then you can measure your query and all the suggested rewritings of the query (and check if the primary index is used).

• Why is this a better index?

Because the tindex column is the first column in the index, all the rows with tindex='anticorrosive' will be in consecutive pages in the index (and all the rows with tindex='corrosive' will be in another part of index but still in consecutive pages.) So, reading (scanning) these 2 parts of the index will be much faster than scanning the whole table or scanning the whole index twice (which is what mysql does essentially with the (tn, col, tindex) index.)

Another benefit of making this index the primary key is that you get rid of the (hidden) 6-byte column that InnoDB added and has been using as the clustered index of the table (since you did not provide any primary or unique constraint/index) so the table is now less wide. The (tindex, tn, col) will be the clustered index of the table from now on. That also means that the date values will be available for the query - after the 2 parts of the index have been scanned.

StackExchange Database Administrators Q#58060, answer score: 4