Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use MySQL 5.6, and I have two tables each with 16M rows:

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

I have one query with order by order by newsstudios.id ASC :

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
  select newsstudio_id from newsstudio_categories 
  where newsstudios.id=newsstudio_categories.newsstudio_id 
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;

the result of this query is:

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

but when I change the direction of order by to DESC the query execution time decreases 100 times:

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

First: why does this change in the direction of order by cause this huge difference in performance?

Second: before this query I have tried LEFT JOIN and WHERE IN queries instead of WHERE EXISTS but they have duplicate result that I should use GROUP BY that it cause using filesort and using temporary which decreases performance a lot. Do you have any suggestions for the query to have better performance?

share|improve this question
    
Have you tested if adding an index with reverse order (category_id,newsstudio_id) is used (and what the efficiency is in that case)? –  ypercube Jun 28 '13 at 10:21
    
I will try (category_id,newsstudio_id) this index, but it's weird for me that why changing the order direction causes decreasing performance. –  Arash Mousavi Jun 28 '13 at 12:52
    
The decreased efficiency is probably because of coincidence. More rows with small ids happen to have one category_id in (1303,1313,1323,1333,1343,632) while rows with high ids do not (happen). So larger parts of the newsstudio_categories (newsstudio_id, category_id) have to be searched until 5 matching rows are found. –  ypercube Jun 28 '13 at 12:56
    
I tried (category_id,newsstudio_id), it don't improve performance but also decrease it. Do you have any suggestion(another query or schema) to fetch this result more efficient? –  Arash Mousavi Jun 28 '13 at 13:14
add comment

2 Answers 2

up vote 4 down vote accepted

If the different categories you are searching on are not to many and you can build the more complex query, this will use an index on (category_id, newsstudio_id) and I'd expect it to be more efficient than your previous query, with either ASC or DESC choice.

It's a bit more complex to construct and I'd expect efficiency to degrade if the number of categories are tens or hundreds:

SELECT newsstudio_id
FROM 
  ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
      ORDER BY newsstudio_id DESC  LIMIT 5 
    ) 
    UNION 
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
      ...
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
  ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;
share|improve this answer
    
thanks, it has great performance :) –  Arash Mousavi Jun 28 '13 at 14:34
    
Faster than the 0.19 seconds of your first query? –  ypercube Jun 29 '13 at 16:10
    
yeah, about 0.008 –  Arash Mousavi Jun 29 '13 at 20:09
add comment

As ypercube said in his comment, your index should perform better in this case if the fields are reversed.

Also, as long as every newsstudio_categories.newsstudio_id has at least one matching row in newsstudios.id (not guaranteed by your schema, but it seems likely), then you could get by with a query that only looks at newsstudio_categories

SELECT SQL_NO_CACHE DISTINCT `newsstudio_id` AS id FROM
newsstudio_categories
WHERE newsstudio_categories.category_id in (1303 , 1313, 1323, 1333, 1343, 632)
ORDER BY `newsstudio_id` DESC
LIMIT 5

or

SELECT SQL_NO_CACHE `newsstudio_id` AS id FROM
newsstudio_categories
WHERE newsstudio_categories.category_id in (1303 , 1313, 1323, 1333, 1343, 632)
GROUP BY `newsstudio_id`
ORDER BY `newsstudio_id` DESC
LIMIT 5
share|improve this answer
    
As I said in question I try these queries before but this queries cause filesort and temporary table that decrease performance a lot. –  Arash Mousavi Jun 28 '13 at 12:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.