Hi,
we upgraded MariaDB 5.5 to 10.0 on one server last night and are now
dealing with severe performance issues. Here is one case where it's
notable:
Query - SELECT b.*, p.category_id FROM brands b JOIN products p
USING(brand_id) GROUP BY category_id, brand_id ORDER BY title ASC;
Table structures:
CREATE TABLE `brands` (
`brand_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`slug` varchar(50) NOT NULL,
`description` text NOT NULL,
`discount_suv_1` float NOT NULL,
`discount_suv_2` float NOT NULL,
`discount_van_1` float NOT NULL,
`discount_van_2` float NOT NULL,
`discount_orp_1` float NOT NULL,
`discount_orp_2` float NOT NULL,
PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8
CREATE TABLE `products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) DEFAULT NULL,
`featured_image_id` int(11) DEFAULT NULL,
`brand_id` int(11) DEFAULT NULL,
`title` text NOT NULL,
`slug` text NOT NULL,
`price` float NOT NULL,
`custom_price` float NOT NULL,
`description` text NOT NULL,
`hidden` tinyint(1) NOT NULL,
`homepage` tinyint(1) NOT NULL,
`sale` tinyint(1) NOT NULL,
`width` float NOT NULL,
`height` int(11) NOT NULL,
`diameter` int(11) NOT NULL,
`resistance` varchar(50) NOT NULL,
`breaking_distance` varchar(50) NOT NULL,
`noise` varchar(50) NOT NULL,
`ean` varchar(50) NOT NULL,
`availability` char(1) NOT NULL,
`li` varchar(15) NOT NULL,
`si` varchar(15) NOT NULL,
`classes` varchar(5) NOT NULL,
`all_year` tinyint(1) NOT NULL,
`type` char(3) NOT NULL,
`holes_count` float NOT NULL,
`pitch` float NOT NULL,
`et` float NOT NULL,
`code` varchar(50) NOT NULL,
`custom` tinyint(1) NOT NULL,
`car_type` varchar(100) NOT NULL,
`models` text NOT NULL,
`imported` tinyint(1) NOT NULL,
`is_runflat` tinyint(1) NOT NULL,
`stock_count` int(11) NOT NULL,
`parser` char(1) NOT NULL,
PRIMARY KEY (`product_id`),
KEY `category_id` (`category_id`),
KEY `featured_image_id` (`featured_image_id`),
KEY `brand_id` (`brand_id`),
KEY `slug` (`slug`(100)),
KEY `hidden` (`hidden`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES
`categories` (`category_id`) ON DELETE SET NULL,
CONSTRAINT `products_ibfk_4` FOREIGN KEY (`featured_image_id`)
REFERENCES `product_images` (`product_image_id`) ON DELETE SET NULL,
CONSTRAINT `products_ibfk_5` FOREIGN KEY (`brand_id`) REFERENCES
`brands` (`brand_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=63638 DEFAULT CHARSET=utf8
Table brands: 149 rows
Table products: 62074 rows
Query time on 5.5: about 0.2s
Query time on 10.0: between 2 and 9s (if not cached)
Explain on 5.5: http://watchdog.sk/5.5.png
Explain on 10.0: http://watchdog.sk/10.png
Any hints what's going on? What about the difference in the explain command?
Thank you.
azur
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
