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