maria-discuss team mailing list archive

[azurit@xxxxxxxx]

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