CODEWITHSUNDEEP
mysqlperformanceinnodbmyisam
Werzi2001
Werzi2001

Reputation: 2135

MySQL InnoDB much slower than MyISAM

I have two tables (reduced to important fields):

syskeywordobjects:
pksyskeywordobjects BIGINT
fksyskeywords BIGINT
fkcontents BIGINT

fkcontents INDEX (fkcontents)
fksyskeywords INDEX (fksyskeywords)
fkcontents_fksyskeywords INDEX (fkcontents, fksyskeywords)


syskeywords:
pksyskeywords BIGINT
keyword VARCHAR

keyword INDEX (keyword)

On this two tables i run the following query:

SELECT k.pksyskeywords, k.keyword, COUNT( k.pksyskeywords ) AS counter
FROM syskeywordobjects ko INNER JOIN syskeywords k ON ko.fksyskeywords = k.pksyskeywords
WHERE (
    k.pksyskeywords <> 1218713201167374664
    AND EXISTS (
        SELECT innerko.pksyskeywordobjects
        FROM syskeywordobjects innerko
        WHERE ko.fkcontents = innerko.fkcontents
        AND innerko.fksyskeywords = 1218713201167374664
    )
)
GROUP BY k.pksyskeywords, k.keyword
ORDER BY counter DESC 
LIMIT 20

If the tables use MyISAM the query takes about 1-2s but if i use InnoDB (which i have to) it takes 25-30s. Why is InnoDB about 20 times slower?

For further help here the explain results.

InnoDB:
id    select_type         table    type    possible_keys                                        key                        key_len   ref                   rows    Extra
1     PRIMARY             k        range   PRIMARY                                              PRIMARY                    8         NULL                  52051   Using where; Using temporary; Using filesort
1     PRIMARY             ko       ref     fksyskeywords                                        fksyskeywords              8         k.pksyskeywords       1       Using where
2     DEPENDENT SUBQUERY  innerko  ref     fkcontents,fksyskeywords,fkcontents_fksyskeywords    fkcontents_fksyskeywords   16        ko.fkcontents,const   1       Using index

MyISAM:
id    select_type           table    type    possible_keys                                       key                         key_len    ref                   rows      Extra
1     PRIMARY               ko       index   fksyskeywords                                       fkcontents_fksyskeywords    16         NULL                  277823    Using where; Using index; Using temporary; Using f...
1     PRIMARY               k        eq_ref  PRIMARY                                             PRIMARY                     8          ko.fksyskeywords      1    
2     DEPENDENT SUBQUERY    innerko  ref     fkcontents,fksyskeywords,fkcontents_fksyskeywords   fkcontents_fksyskeywords    16         ko.fkcontents,const   1

Upvotes: 3

Views: 1546

Answers (1)

N.B.
N.B.

Reputation: 14071

In order to have InnoDB working faster, you should always set the innodb_buffer_pool to a large value. It allows InnoDB to store working dataset in the memory (table data, index data structures) and thus works faster (IO of RAM > IO of HDD). Default value is 8MB if I'm not mistaken, I personally have it at 80% of available RAM.

Upvotes: 5

Related Questions