The cardinality for the brand_id index is reported as hugely different. 188 V 48555. Do these tables definitely contain the same data?
-----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom....@lists.launchpad.net] On Behalf Of azu...@pobox.sk Sent: Thursday, February 11, 2016 4:13 PM Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Performance issues after upgrading to 10.0 From 5.5: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment brands 0 PRIMARY 1 brand_id A 149 NULL NULL BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment products 0 PRIMARY 1 product_id A 48555 NULL NULL BTREE products 1 category_id 1 category_id A 48555 NULL NULL YES BTREE products 1 featured_image_id 1 featured_image_id A 48555 NULL NULL YES BTREE products 1 brand_id 1 brand_id A 48555 NULL NULL YES BTREE products 1 slug 1 slug A 48555 100 NULL BTREE products 1 hidden 1 hidden A 48555 NULL NULL BTREE From 10.0: +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | brands | 0 | PRIMARY | 1 | brand_id | A | 149 | NULL | NULL | | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | products | 0 | PRIMARY | 1 | product_id | A | 61882 | NULL | NULL | | BTREE | | | | products | 1 | category_id | 1 | category_id | A | 10 | NULL | NULL | YES | BTREE | | | | products | 1 | featured_image_id | 1 | featured_image_id | A | 61882 | NULL | NULL | YES | BTREE | | | | products | 1 | brand_id | 1 | brand_id | A | 188 | NULL | NULL | YES | BTREE | | | | products | 1 | slug | 1 | slug | A | 61882 | 100 | NULL | | BTREE | | | | products | 1 | hidden | 1 | hidden | A | 2 | NULL | NULL | | BTREE | | | +----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ I'm running MariaDB 10.0.23 from Debian Jessie. Upgrade was done correctly with mysql_upgrade (this was done by Debian itself, i just upgraded the OS by standard way but, as i said, Debian packages are doing correct upgrade and then 'check table for upgrade' for every table). But i also done OPTIMIZE TABLE for every table - no luck. Citát Guillaume Lefranc <guillaume.lefr...@mariadb.com>: > 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 _______________________________________________ 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