MySQL Explain gives different result for same query, query performance changes

Question

I've been having some performance issues on my production server. The main issue, I think is the table pricelist_entries, which has approximately 6 million rows. Basically its holds all of the products available. The query below usually takes a second to execute but ever since the table pricelist_entries grew past 3 million rows the execution time has started to increase. The weird thing is that sometimes the same query will take 10 seconds, other times 1 second. First I thought that maybe its the query cache but I disabled that and I still get the same variating result. What I can see from the two explains is that when its fast the query only accesses about 5000 rows while when its slow it seem to do an almost full table scan. I can't figure out why this happens. Any help is appreciated.

Answer 1

Thanks for confirming that innodb_buffer_pool_size is OK.

Here are some ideas; I don't know which will be best:

Plan A

pricelist:  INDEX(pCurrent, tagged_to)
materials:  INDEX(companyID, article_nr_lev)

Plan B

SELECT STRAIGHT_JOIN ...
    FROM pricelist ...    -- first
    JOIN pricelist_entries ...
    LEFT JOIN materials

Please provide SHOW CREATE TABLE for each table; there could be some more clues there.

More

materials.article_nr_lev = pricelist_entries.peArtnr -- Whenever JOINing, the datatypes must be the same (or 'close'). Those two value are probably close enough -- same collation (mandatory) and both VARCHAR. But they are of different lengths; I think that is 'close enough'. However, do you sometimes need 255 characters, and sometimes need only 50? For various subtle reasons (having to do with the execution of queries), it is better to keep VARCHAR sizes smaller (yet large enough to be safe). I suspect the 255 could be changed to 50 without harm.

Also you have a lot of 4-byte INT fields that could probably be turned into smaller fields (eg, 2-byte SMALLINT UNSIGNED). Shrinking table sizes often leads to improved performance, albeit minor.

category int(11) NOT NULL COMMENT '0 - Inget val 1 - Alkov 2 - ... -- Consider using ENUM. That would avoid remembering the mapping. (And take 1 byte instead of 4.)

Be careful about CHARCTER SET -- some columns are dec8, some are utf8. The may be issues with comparisons, conversions, etc. (I haven't noticed any problems with the query in question.)