Can you post the output of: SHOW INDEX IN brands; SHOW INDEX IN products;
for both databases? And IMHO, if this is the case this would warrant a bug report. Are you running the latest 10.0 release? On Thu, Feb 11, 2016 at 3:44 PM <azu...@pobox.sk> wrote: > 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 > -- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
_______________________________________________ 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
