Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
I would try an index on (tindex, tn, col, date) –  ypercube Feb 5 at 20:41

4 Answers 4

up vote 2 down vote accepted
+50

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.

share|improve this answer
    
great! I droped unique index. ALTER TABLE mytable ADD PRIMARY KEY (tindex, tn, col) ; and ALTER TABLE mytable ADD INDEX (date);. Now i only have 2 indexes. then use the same query, only 0.0052 s. I try to change any other search words, all of them below 0.1s. Make a right index is such important that cause different effection. I will Sstill test for a long time to see if it will cause another problem then mark a right anwser. Thanks. –  cj333 Feb 7 at 14:39

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.

share|improve this answer
    
a bitter faster, 4.0612s, hope it would cost under 1s. thanks. –  cj333 Feb 1 at 16:32
    
Did you add the index? –  Mihai Feb 1 at 17:01
    
i added alter table mytable add index myindex (tn,col,tindex), but still slow.. sometimes some query words would cost 87.6206s. –  cj333 Feb 4 at 10:14
1  
The JOIN is ok - but only if the (tn,col,tindex) combination is unique. Otherwise it may return duplicate results (while the original will not.) –  ypercube Feb 5 at 20:44

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 !!!

share|improve this answer
    
thanks for your anwser. I have added ALTER TABLE mytable ADD UNIQUE INDEX (tn,col,tindex);, ALTER TABLE mytable ADD INDEX (date); the query still slow. see the explain in my question. –  cj333 Feb 7 at 11:24

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.