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.

Query:

   SELECT SQL_NO_CACHE pricelist_entries.peID AS id,
   pricelist_entries.peID AS stockID,
   pricelist_entries.peArtnr AS artnr,
   pricelist_entries.peName AS title,
   pricelist_entries.peGrosspriceVAT AS price,
   "brand1" AS TYPE,
   pricelist_entries.peBrand,
   "0" AS material_type,
   pricelist_entries.peNetprice AS purchase_price,
   25 AS vat,
   pricelist_entries.ean_code
   FROM pricelist_entries
   INNER JOIN pricelist ON pricelist.pID = pricelist_entries.peParentID
   LEFT OUTER JOIN materials ON (materials.supplier = pricelist_entries.peBrand 
   AND materials.article_nr_lev = pricelist_entries.peArtnr)
   AND materials.companyID=276
   AND materials.deleted=0
   WHERE pricelist.tagged_to IN (44,92,97,104,108,109,134,135,136)
   AND pricelist.pCurrent = 1
   AND pricelist_entries.peDeleted=0
   AND (materials.pricelist_entries_id IS NULL OR materials.material_type!=0) 
   AND (peName LIKE "%search%" || peArtnr LIKE "%search%" || pricelist_entries.ean_code LIKE "%search%")

Explain1: https://box.jakupovic.org/index.php/s/7durJAWRqKDFzIx

Explain2: https://box.jakupovic.org/index.php/s/rrmDyqaQIroOBbN

All of the tables in the database are innodb and the server has 32GB RAM, 16 cores and shared resources with apache2 and the rest of the OS. Innodb buffer pool size is currently set to 8GB. The full database is around 4Gb and is as far as I can see fully loaded into memory. Innotop tells me that the buffer pool is hit 100% of the time.

EDIT: Added SHOW CREATE TABLE for each table. I noticed that some of the tables seem to use utf-8 instead of latin1, which should be default across all of our database tables. Could such a thing impact performance?

Table   Create Table
pricelist   CREATE TABLE `pricelist` (
  `pID` int(11) NOT NULL AUTO_INCREMENT,
  `pCompanyid` int(11) NOT NULL,
  `pDeleted` int(11) NOT NULL,
  `tagged_to` int(11) NOT NULL,
  `country` int(11) NOT NULL COMMENT '0: sverige, 1: norge',
  `pType` varchar(11) NOT NULL,
  `pBrand` varchar(50) NOT NULL,
  `pType2` varchar(50) NOT NULL,
  `pLopnummer` int(11) NOT NULL,
  `pModelyear` year(4) NOT NULL,
  `pValidfrom` date NOT NULL,
  `pValidto` date NOT NULL,
  `pCurrent` int(11) NOT NULL,
  `imported_by` varchar(200) NOT NULL,
  `pDate` date NOT NULL,
  PRIMARY KEY (`pID`),
  KEY `tagged_to` (`tagged_to`),
  KEY `pType2` (`pType2`)
) ENGINE=InnoDB AUTO_INCREMENT=2170 DEFAULT CHARSET=dec8


CREATE TABLE `pricelist_entries` (
  `peID` int(11) NOT NULL AUTO_INCREMENT,
  `peCompanyID` int(11) NOT NULL,
  `peDeleted` int(11) NOT NULL,
  `peParentID` int(11) NOT NULL,
  `peTillvalParent` int(11) NOT NULL,
  `peType` varchar(50) NOT NULL COMMENT '0:caravan, 1: mobilehome, 2:trailer, 99:private',
  `peType2` varchar(50) NOT NULL,
  `peArtnr` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `peBrand` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `peName` varchar(600) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `peName2` varchar(200) NOT NULL,
  `peModelyear` year(4) NOT NULL,
  `peNetprice` double NOT NULL,
  `peGrossprice` double NOT NULL,
  `peEngangsavgift` double NOT NULL,
  `peGrosspriceVat` double NOT NULL,
  `peRetailerMargin` double NOT NULL,
  `peValidFrom` date NOT NULL,
  `peValidTo` date NOT NULL,
  `peWeight` double NOT NULL,
  `ean_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `category` int(11) NOT NULL COMMENT '0 - Inget val 1 - Alkov 2 - Halvintegrerad 3 - Hlavintegrerad m. taksäng 4 - Integrerad 5 - Van 6 - Hardtop 7 - Daycruiser 8 - Walkaround 9 - Cabin 10 - Bowrider 11 - Mittcabin 12 - Styrpulpet 13 - Roddbåt 14 - RIB/Gummibåt 15 - Vattenskoter 16 - Segelbåt 17 - Övriga båtar',
  `layout` int(11) NOT NULL COMMENT '0 - Inget val 1 - Långbäddar 2 - Dubbelbädd 3 - Barnkammare 4 - Övrigt 5 - Queenbed',
  `passengers` int(11) NOT NULL COMMENT 'not including driver',
  `beds` int(11) NOT NULL,
  `fuel` int(11) NOT NULL COMMENT '0 - Inget val 1 - Diesel 2 - Bensin 3 - Etanol 4 - Biogas',
  `boogie` int(11) NOT NULL COMMENT '0 - Inget val, 1 - Nej, 2 - Ja',
  `motortype` varchar(200) NOT NULL,
  `horsepower` int(11) NOT NULL,
  `length` int(11) NOT NULL,
  `width` int(11) NOT NULL,
  `height` int(11) NOT NULL,
  `totalweight1` int(11) NOT NULL COMMENT 'Totalvikt 1 är hur mycket fordonet väger totalt inkl lastvikt',
  `totalweight2` int(11) NOT NULL COMMENT 'Tjänstevikt',
  `totalweight3` int(11) NOT NULL COMMENT 'Totalvikt 2 är den vikt man kan klassa upp till för att få mer lastvikt. ',
  `equipment_category` varchar(60) NOT NULL,
  PRIMARY KEY (`peID`),
  KEY `peParentID` (`peParentID`),
  KEY `peArtnr` (`peArtnr`),
  KEY `peBrand` (`peBrand`),
  KEY `ean_code` (`ean_code`),
  KEY `peCompanyID` (`peCompanyID`)
) ENGINE=InnoDB AUTO_INCREMENT=23777770 DEFAULT CHARSET=dec8


Table   Create Table
materials   CREATE TABLE `materials` (
  `added_now` int(11) NOT NULL,
  `materialID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `categoryID` int(10) unsigned NOT NULL,
  `companyID` int(11) NOT NULL,
  `pricelist_entries_id` int(11) NOT NULL DEFAULT '0',
  `supplier` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `title_two` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `label_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `label_title_two` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `price` float DEFAULT NULL,
  `article_nr_lev` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `article_nr_int` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `temp_amount` int(11) NOT NULL DEFAULT '0',
  `temp_ordering_min_amount` int(11) NOT NULL DEFAULT '0',
  `temp_stock_item` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0: NEJ 1: JA',
  `temp_storage_place1` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `temp_storage_place2` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `temp_ordered_amount` int(11) NOT NULL,
  `temp_backordered` int(11) NOT NULL,
  `temp_order_date` date NOT NULL,
  `temp_backordered_date` date NOT NULL,
  `purchase_price` float NOT NULL,
  `temp_booked_amount` int(11) NOT NULL DEFAULT '0',
  `lowest_price` int(11) NOT NULL DEFAULT '0',
  `notes` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `material_type` int(11) NOT NULL DEFAULT '0',
  `temp_reorder_point` int(11) NOT NULL DEFAULT '0',
  `ean_code` varchar(255) NOT NULL,
  `vat` float NOT NULL DEFAULT '25',
  `supplier_id_jeeves` int(11) NOT NULL DEFAULT '0',
  `lastEdited` datetime NOT NULL,
  `lastEditedBy` int(11) NOT NULL,
  `copied_from` int(11) NOT NULL,
  PRIMARY KEY (`materialID`),
  KEY `article_nr_lev` (`article_nr_lev`),
  KEY `pricelist_entries_id` (`pricelist_entries_id`),
  KEY `companyID` (`companyID`),
  KEY `supplier` (`supplier`)
) ENGINE=InnoDB AUTO_INCREMENT=500151 DEFAULT CHARSET=utf8

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.)