Mysql – Subquery ORDER BY doesn’t work on MySQL 5.6, but works on 5.5

MySQLmysql-5.6order-byperformancequery-performancesubquery

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables.

I was able to get the expected result on MySQL 5.5, but after upgrading to 5.6, ORDER BY on the subquery seems be ignored. How to change the query to make the ORDER BY work?

The query is supposed to:

find products which is under specified category id (eg: category id 488).
find the latest price of each product.
group products with same 'matchkey' (matchkey generated by removing stopwords from the product name. generated by other programming)
find the lowest price of each grouped products
show each grouped products info with lowest price

Incorrect result:

The 3 tables relationship are as below:

products <-> many-to-many <-> categories
products -> one-to-many -> prices

table information:

mysql> show columns from products;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field          | Type             | Null | Key | Default             | Extra                       |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| name           | varchar(300)     | NO   | MUL | NULL                |                             |
| active         | tinyint(1)       | YES  |     | 1                   |                             |
| created_at     | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
| matchkey       | varchar(300)     | NO   | MUL | NULL                |                             |
| sku            | varchar(50)      | YES  | MUL | NULL                |                             |
+----------------+------------------+------+-----+---------------------+-----------------------------+

mysql> show columns from prices;
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                | Null | Key | Default             | Extra                       |
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| price_selling | decimal(8,2)        | NO   | MUL | NULL                |                             |
| price_before  | decimal(8,2)        | NO   |     | 0.00                |                             |
| product_id    | int(10) unsigned    | NO   | MUL | NULL                |                             |
| created_at    | timestamp           | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at    | timestamp           | NO   |     | 0000-00-00 00:00:00 |                             |
+---------------+---------------------+------+-----+---------------------+-----------------------------+

mysql> show columns from categories;
+------------------+------------------+------+-----+---------------------+-----------------------------+
| Field            | Type             | Null | Key | Default             | Extra                       |
+------------------+------------------+------+-----+---------------------+-----------------------------+
| id               | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| parent_id        | int(11)          | YES  | MUL | NULL                |                             |
| lft              | int(11)          | YES  | MUL | NULL                |                             |
| rgt              | int(11)          | YES  | MUL | NULL                |                             |
| depth            | int(11)          | YES  |     | NULL                |                             |
| name             | varchar(255)     | NO   | MUL | NULL                |                             |
| active           | tinyint(1)       | YES  |     | 1                   |                             |
| created_at       | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at       | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+------------------+------------------+------+-----+---------------------+-----------------------------+

mysql> show columns from category_product;
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| product_id         | int(10) unsigned | NO   | PRI | NULL    |       |
| category_id        | int(10) unsigned | NO   | PRI | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+

The query that works on MySQL 5.5 is:

SELECT 
    p3.pid AS id, category_product.product_id, p3.name, p3.priceId, 
    p3.product_id, p3.price_selling, p3.price_before, MIN(p3.price_selling) AS minprice,
    p3.matchkey, categories.url_key AS category_urlkey
FROM category_product
RIGHT JOIN (
            SELECT
                pp.priceId, pp.product_id, pp.price_selling, pp.price_before,
                pp.pid, pp.name, pp.matchkey
            FROM (
                    SELECT 
                        prices.id AS priceId, prices.product_id, prices.price_selling, prices.price_before,
                        products.id AS pid, products.name, products.matchkey
                    FROM prices
                    RIGHT JOIN products ON prices.product_id = products.id
                    WHERE prices.id = (SELECT MAX(p.id) FROM prices p WHERE p.product_id = prices.product_id)
            ) AS pp
            GROUP BY pp.pid
            ORDER BY pp.price_selling ASC
            ) AS p3 ON category_product.product_id = p3.pid
RIGHT JOIN categories ON categories.id = category_product.category_id AND categories.id = 488 
GROUP BY p3.matchkey 
ORDER BY p3.pid DESC;

The table structures & sample data can be used for test:

--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `matchkey` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `sku` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_sku_index` (`sku`),
  KEY `id` (`id`),
  KEY `matchkey` (`matchkey`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=60057 ;

--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `name`, `active`, `created_at`, `updated_at`, `matchkey`, `sku`) VALUES
(26195, 'Samsung Galaxy S3 Mini GT-I8200 Unlocked Cellphone, White, 8GB', 1, '2016-01-23 14:38:25', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(26425, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:28', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(26632, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:29', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(28212, 'Samsung Galaxy S3 Mini GT-I8200 Unlocked Cellphone, White, 8GB', 1, '2016-01-23 14:38:51', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(35111, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:39:03', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(37611, '[From U.S.A]Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:39:29', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(60017, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:11', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL);

--
-- Table structure for table `prices`
--
CREATE TABLE IF NOT EXISTS `prices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `price_selling` decimal(8,2) NOT NULL,
  `price_before` decimal(8,2) NOT NULL DEFAULT '0.00',
  `product_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `price_selling` (`price_selling`),
  KEY `id` (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=114663 ;

--
-- Dumping data for table `prices`
--
INSERT INTO `prices` (`id`, `price_selling`, `price_before`, `product_id`, `created_at`, `updated_at`) VALUES
(33585, '1052.40', '0.00', 26195, '2016-01-22 09:53:02', '0000-00-00 00:00:00'),
(65594, '1052.40', '0.00', 26195, '2016-01-23 01:27:08', '0000-00-00 00:00:00'),
(110038, '1052.40', '0.00', 26195, '2016-01-23 14:38:25', '0000-00-00 00:00:00'),
(33815, '1052.40', '0.00', 26425, '2016-01-22 09:55:17', '0000-00-00 00:00:00'),
(65790, '1052.40', '0.00', 26425, '2016-01-23 01:27:11', '0000-00-00 00:00:00'),
(110209, '1016.40', '0.00', 26425, '2016-01-23 14:38:28', '0000-00-00 00:00:00'),
(34022, '1052.40', '0.00', 26632, '2016-01-22 09:57:43', '0000-00-00 00:00:00'),
(66020, '1052.40', '0.00', 26632, '2016-01-23 01:27:13', '0000-00-00 00:00:00'),
(110373, '1052.40', '0.00', 26632, '2016-01-23 14:38:29', '0000-00-00 00:00:00'),
(35602, '960.25', '0.00', 28212, '2016-01-22 10:12:50', '0000-00-00 00:00:00'),
(67396, '960.25', '0.00', 28212, '2016-01-23 01:27:45', '0000-00-00 00:00:00'),
(111555, '960.25', '0.00', 28212, '2016-01-23 14:38:51', '0000-00-00 00:00:00'),
(71346, '929.20', '0.00', 35111, '2016-01-23 01:37:05', '0000-00-00 00:00:00'),
(112318, '899.30', '0.00', 35111, '2016-01-23 14:39:03', '0000-00-00 00:00:00'),
(73852, '936.10', '0.00', 37611, '2016-01-23 01:54:47', '0000-00-00 00:00:00'),
(113711, '936.10', '0.00', 37611, '2016-01-23 14:39:29', '0000-00-00 00:00:00'),
(109601, '1108.99', '2369.91', 60017, '2016-01-23 14:38:08', '0000-00-00 00:00:00');


--
-- Table structure for table `categories`
--
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  `depth` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `url_key` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `categories_parent_id_index` (`parent_id`),
  KEY `categories_lft_index` (`lft`),
  KEY `categories_rgt_index` (`rgt`),
  KEY `url_key` (`url_key`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=825 ;

--
-- Dumping data for table `categories`
--
INSERT INTO `categories` (`id`, `parent_id`, `lft`, `rgt`, `depth`, `name`, `active`, `url_key`, `created_at`, `updated_at`) VALUES
(1, NULL, 1, 968, 0, 'Root', 1, NULL, '2016-01-23 13:19:51', '2016-01-23 13:19:51'),
(487, 1, 446, 453, 1, 'Mobile & Communication', 1, 'mobile-communication', '2016-01-23 13:19:51', '2016-01-23 13:19:51'),
(488, 487, 447, 448, 2, 'Mobile Phones', 1, 'mobile-phones', '2016-01-25 06:27:37', '2016-01-25 06:27:37');


--
-- Table structure for table `category_product`
--
CREATE TABLE IF NOT EXISTS `category_product` (
  `product_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `product_id` (`product_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `category_product`
--
INSERT INTO `category_product` (`product_id`, `category_id`) VALUES
(26195, 1),
(26195, 487),
(26195, 488),
(26425, 1),
(26425, 487),
(26425, 488),
(26632, 1),
(26632, 487),
(26632, 488),
(28212, 1),
(28212, 487),
(28212, 488),
(35111, 1),
(35111, 487),
(35111, 488),
(37611, 1),
(37611, 487),
(37611, 488),
(60017, 1),
(60017, 487),
(60017, 488);

Responses to comments

Downgrading to 5.5 is my last option if can't fix it. I would like to rewrite the query, I tried changing the query still no lucky to get it work. I think is something wrong in the query I write.

For the testing data provided above, the query should return products under (category id: 488). The query is used for a price comparison site. When a user clicks on the category ('Mobile Phones' – category id in database- 'categories' table is 488) link on website, the website will list all the products (grouped by 'matchkey' column), showing the lowest price of that product group.

Adding WHERE categories.id = 488 or categories.name = 'Mobile Phones', I still get the same result. The problem is the query result not showing the lowest price, even I write ORDER BY pp.price_selling ASC on the subquery.

Demo:

Both demos above are using the same query, but the results are different.

Best Answer

SELECT a, b ...
    GROUP BY a

is asking for trouble. You may have been 'lucky' in 5.5 and 'unlucky' in 5.6.

The problem is that any value of b can be shown for each 'group'. Sure, there are cases where a given a maps uniquely to a given b (eg, in a normalization table), but that does not feel like the case here. To see that there is a problem:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

before doing the SELECT.

You really should switch to InnoDB.

When you get to 5.7, that setting will be defaulted on.

Efficient techniques for groupwise max.

MAJOR CHANGE #1 : Refactor the Query

Here is your original query

Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)

You could try

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A;

or maybe the count per email

select email,count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A group by email;

MAJOR CHANGE #2 : Proper Indexing

I think you have this already since Query 1 and Query 2 run fast. Make sure you have a compound index on (account_id,email). Do SHOW CREATE TABLE mybigtable\G and make sure you have one. If you don't have it or if you are not sure, then create the index anyway:

ALTER TABLE mybigtable ADD INDEX account_id_email_ndx (account_id,email);

UPDATE 2012-03-07 13:26 EST

If you want to do a NOT IN(), change the INNER JOIN to a LEFT JOIN and check for the right side being NULL, like this:

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    LEFT JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
    WHERE tbl345.email IS NULL
) A;

UPDATE 2012-03-07 14:13 EST

Please read these two links on doing JOINs

Here is a great YouTube Video where I learned to refactor queries and the book it was based on

Mysql – Slow INSERT/UPDATE on InnoDB

As you can see, the process that takes most time is "query end". There is an interlocking problem when multiple threads want to write the file at the same time, this way the log will be flushed every second:

innodb_flush_log_at_trx_commit = 0

in the /etc/my.cnf file

Also, you should try Optimize InnoDB as explained above for future query improvement.