innodb DEPENDENT SUBQUERY very slow

Question

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 (date);

query 1

SELECT A.* FROM 
(SELECT * FROM mytable WHERE tindex='words1') A
INNER JOIN
(SELECT tn,col FROM mytable WHERE tindex='words2') B
USING (tn,col) order by date limit 10

using 29.6278s

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    15674   Using temporary; Using filesort
1   PRIMARY <derived3>  ALL NULL    NULL    NULL    NULL    1716    Using where; Using join buffer
3   DERIVED mytable index   NULL    date    5   NULL    13918039    Using where; Using index
2   DERIVED mytable index   NULL    date    5   NULL    13918039    Using where; Using index

query 2

SELECT * FROM mytable
WHERE tindex IN ('words1','words2')
GROUP BY tn,col HAVING COUNT(1)=2 order by date limit 10;

using 23.8711s

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  mytable index   NULL    date    5   NULL    19359399    Using where; Using index; Using temporary

Answer 1

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.

Answer 2

Use JOIN

SELECT * 
FROM mytable a
JOIN
mytable b
ON b.tn = a.tn
AND b.col = a.col
WHERE b.tindex =  'waterproof'
AND a.tindex =  'anticorrosive'
ORDER BY a.DATE
LIMIT 10

Also a composite index(tn,col,tindex) will help.

Answer 3

There are two approaches

Approach #1 : DITCH CORRELATED SUBQUERY

You should ditch the correlated subquery and replace it with the JOIN of two subqueies

SELECT A.* FROM 
(SELECT * FROM mytable  WHERE tindex='anticorrosive') A
INNER JOIN
(SELECT tn,col FROM mytable  WHERE tindex='waterproof') B
USING (tn,col);

Depending on the number of products, this will be a long query anyway.

Approach #2 : DITCH JOINS ALTOGETHER

SELECT tn,col FROM mytable
WHERE tindex IN ('anticorrosive','waterproof')
GROUP BY tn,col HAVING COUNT(1)=2;

This should be more like it (Less Overhead)

CAVEAT

Please add this index

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

This will make sure every (tn,col) only has unique tindex values.

Give it a Try !!!

Answer 4

I think your query is already well performed by mysql, because the rows column means mysql get exactly what rows(1173 & 47) it has to work with.

Your problem is ORDER BY:

To fix that try to up sort_buffer_size to 1024M

ORDER BY Optimization

Also try another indexes, unique: (tindex, date) or (date, tindex)