I am experiencing weird behaviour from my MySQL/MariaDB InnoDB/XtraDB. Recently switched to MariaDB 5.5. The switch made the server overall more performent, but I still have this problem.
One particular tables index seems to break every now and then. And after a while, it fixes it self.
SHOW CREATE TABLE article_inventory; gives
CREATE TABLE `article_inventory` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`article_variant_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
`season_id` BIGINT(20) UNSIGNED NOT NULL,
`warehouse_id` BIGINT(20) UNSIGNED NOT NULL,
`quantity` BIGINT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `unique_inventory_idx` (`article_variant_id`, `season_id`, `warehouse_id`),
INDEX `article_variant_id_idx` (`article_variant_id`),
INDEX `article_inventory_season_id_idx` (`season_id`),
INDEX `article_inventory_warehouse_id_idx` (`warehouse_id`),
CONSTRAINT `article_inventory_article_variant_id_article_variant_id` FOREIGN KEY (`article_variant_id`) REFERENCES `article_variant` (`id`),
CONSTRAINT `article_inventory_season_id_season_id` FOREIGN KEY (`season_id`) REFERENCES `season` (`id`),
CONSTRAINT `article_inventory_warehouse_id_warehouse_id` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouse` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3827622858;
EDIT: Majority of SELECT queries is made against this table. Big updates are made every hour. Some times VERY large updates.
Running this query:
SELECT a.id
FROM article a
INNER JOIN article_variant a2
ON a.style_id = a2.style_id
INNER JOIN article_block a3
ON a2.po = a3.po
INNER JOIN color c
ON a2.color_id = c.id
INNER JOIN size s
ON a2.size_id = s.id
INNER JOIN article_group a4
ON a2.id = a4.article_variant_id AND (a4.season_id = 6)
INNER JOIN article_inventory a5
ON a2.id = a5.article_variant_id AND (((a5.warehouse_id = 5 OR a5.warehouse_id = 1) AND a5.season_id = 6))
INNER JOIN article_date a6
ON a.style_id = a6.style_id AND ((a6.pricelist_id = 5 AND a6.season_id = 6))
INNER JOIN article_price a7
ON a.style_id = a7.style_id AND ((a7.pricelist_id = 5 AND a7.season_id = 6))
INNER JOIN pricelist p
ON a7.pricelist_id = p.id
INNER JOIN concept c2
ON a4.concept_id = c2.id
INNER JOIN category c3
ON a4.category_id = c3.id
LEFT JOIN order_cart_row o
ON a2.id = o.article_variant_id AND (o.order_id = 17035)
LEFT JOIN shortlist s2
ON a.id = s2.article_id AND (s2.order_id = 17035)
WHERE ((a2.is_canceled <> 1 AND a4.is_canceled <> 1) OR o.quantity IS NOT NULL) AND c2.id = 2
GROUP BY a.id
... should execute in about 0.5-1.0sec, and give me an explain similar to this:
id select_type table type possible_keys key key_len ref rowsExtra
1 SIMPLE p const PRIMARY PRIMARY 8 const 1 Using index; Using temporary; Using filesort
1 SIMPLE c2 const PRIMARY PRIMARY 8 const 1 Using index
1 SIMPLE a3 index PRIMARY PRIMARY 98 NULL 1031Using where
1 SIMPLE a2 ref PRIMARY,unique_variant_idx,color_id_idx,style_id_idx,size_id_idx,article_variant_po_idx article_variant_po_idx 98 wsp_stage.a3.po 14 Using where
1 SIMPLE s eq_ref PRIMARY PRIMARY 11 wsp_stage.a2.size_id 1 Using index
1 SIMPLE c eq_ref PRIMARY PRIMARY 11 wsp_stage.a2.color_id 1
1 SIMPLE o eq_ref unique_rows_idx,article_variant_id_idx,order_id_idx unique_rows_idx 16 const,wsp_stage.a2.id 1 Using index
1 SIMPLE a eq_ref unique_style_idx unique_style_idx 767 wsp_stage.a2.style_id 1 Using index
1 SIMPLE a6 ref article_season_pricelist_unique_idx,season_id_idx,pricelist_id_idx,style_id_idx article_season_pricelist_unique_idx 784 wsp_stage.a2.style_id,const,const 1 Using index
1 SIMPLE a7 ref article_season_pricelist_unique_idx,season_id_idx,pricelist_id_idx,style_id_idx article_season_pricelist_unique_idx 784 wsp_stage.a2.style_id,const,const 1 Using index
1 SIMPLE a4 eq_ref unique_group_idx,one_per_season_idx,category_id_idx,concept_id_idx,season_id_idx,article_variant_id_idx one_per_season_idx 16 wsp_stage.a2.id,const 1 Using index
1 SIMPLE c3 eq_ref PRIMARY PRIMARY 8 wsp_stage.a4.category_id 1 Using index
1 SIMPLE s2 ref shortlist_article_id_idx shortlist_article_id_idx 8 wsp_stage.a.id 10 Using where
1 SIMPLE a5 ref unique_inventory_idx,article_variant_id_idx,article_inventory_season_id_idx,article_inventory_warehouse_id_iunique_inventory_idx 17 wsp_stage.a2.id,const 8 Using where
When everything is working as it should, article_inventory (alias a5) uses unique_inventory_idx, or article_variant_id_idx. Both should give me about 5-100 examined rows.
But every now and then, something happens, and the same query takes up to about 30sec, and gives me this explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p const PRIMARY PRIMARY 8 const 1 Using index; Using temporary; Using filesort
1 SIMPLE c2 const PRIMARY PRIMARY 8 const 1 Using index
1 SIMPLE a5 ref unique_inventory_idx,article_variant_id_idx,article_inventory_season_id_idx,article_inventory_warehouse_id_iarticle_inventory_season_id_idx 8 const 6718732 Using where
1 SIMPLE a4 eq_ref unique_group_idx,one_per_season_idx,category_id_idx,concept_id_idx,season_id_idx,article_variant_id_idx one_per_season_idx 16 wsp_stage.a5.article_variant_id,const1 Using where
1 SIMPLE c3 eq_ref PRIMARY PRIMARY 8 wsp_stage.a4.category_id 1 Using index
1 SIMPLE a2 eq_ref PRIMARY,unique_variant_idx,color_id_idx,style_id_idx,size_id_idx,article_variant_po_idx PRIMARY 8 wsp_stage.a5.article_variant_id 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 11 wsp_stage.a2.color_id 1 Using index
1 SIMPLE a eq_ref unique_style_idx unique_style_idx 767 wsp_stage.a2.style_id 1 Using index
1 SIMPLE a6 ref article_season_pricelist_unique_idx,season_id_idx,pricelist_id_idx,style_id_idx article_season_pricelist_unique_idx 784 wsp_stage.a2.style_id,const,const 1 Using index
1 SIMPLE a7 ref article_season_pricelist_unique_idx,season_id_idx,pricelist_id_idx,style_id_idx article_season_pricelist_unique_idx 784 wsp_stage.a2.style_id,const,const 1 Using index
1 SIMPLE s eq_ref PRIMARY PRIMARY 11 wsp_stage.a2.size_id 1 Using index
1 SIMPLE a3 eq_ref PRIMARY PRIMARY 98 wsp_stage.a2.po 1 Using index
1 SIMPLE o eq_ref unique_rows_idx,article_variant_id_idx,order_id_idx unique_rows_idx 16 const,wsp_stage.a5.article_variant_id1 Using where
1 SIMPLE s2 ref shortlist_article_id_idx shortlist_article_id_idx 8 wsp_stage.a.id 7 Using where
article_inventory (a5) is now using article_inventory_season_id_idx. A very very bad index, as it is the second least specific of them all. Gives me 6718732 examined rows.
my.ini:
[mysqld]
datadir="W:/mariadb/data/"
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=5000M
innodb_log_file_size=52428800
innodb_file_per_table
innodb_file_format=Barracuda
[client]
port=3307
