1

I have a table defined as follows:

| book | CREATE TABLE `book` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `provider_id` int(10) unsigned DEFAULT '0',
  `source_id` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `provider` (`provider_id`,`source_id`),
  KEY `idx_source_id` (`source_id`),
) ENGINE=InnoDB AUTO_INCREMENT=1605425 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

when there are about 10 concurrent read with following sql:

SELECT * FROM `book`  WHERE (provider_id = '1' AND source_id = '1037122800') ORDER BY `book`.`id` ASC LIMIT 1

it becomes slow, it takes about 100 ms.

however if I changed it to 

SELECT * FROM `book`  WHERE (provider_id = '1' AND source_id = '221630001') LIMIT 1

then it is normal, it takes several ms.

I don't understand why adding order by id makes query much slower? could anyone expain?

seaguest
  • 2,510
  • 5
  • 27
  • 45
  • 2
    First of all, your query conditions are different. Don’t change them if you’re trying to compare one thing to another. Now it might have nothing to do with ordering and all to do with the conditions. Second, if you want any single result, the database can go in any order and stop after finding one. If you sort it has to find the specific one and may have to go through all data. Also 100ms is slow? Depends on the amount of data. Check the query plan. – Sami Kuhmonen May 07 '18 at 04:42
  • @SamiKuhmonen，in massive query condition, the source_id is doesn't make difference. in a single query in both cases (with order by id or not), it takes only several ms. – seaguest May 07 '18 at 06:06
  • 1
    Tip 1: don't compare a string to an integer – Strawberry May 07 '18 at 06:16
  • @Strawberry，I changed "provider_id = '1' " to "provider_id = 1 ", the result are same.probably mysql optimized that. – seaguest May 07 '18 at 06:25
  • What happens if you reverse the provider_id,source_id index? Also, given that this index is unique, the order by (and limit) clause appears to serve no purpose – Strawberry May 07 '18 at 06:29
  • @Strawberry, I changed the query to "SELECT * FROM `book` WHERE (source_id = '1036997500' AND provider_id = '1') ORDER BY `book`.`id` ASC LIMIT 1 ", same performance as provider_id in front, source_id is indexed as well. – seaguest May 07 '18 at 06:49
  • No, not the query . The index. – Strawberry May 07 '18 at 07:11
  • @Strawberry， the result is still the same. – seaguest May 07 '18 at 07:45
  • So, just leave off the order by /limit. After all, we can guarantee that there is only one result – Strawberry May 07 '18 at 07:56

2 Answers2

0

Try to add desired columns (Select Column Name,.. ) instead of * or Refer this.

Why is my SQL Server ORDER BY slow despite the ordered column being indexed?

Sam
  • 83
  • 7
0

I'm not a mysql expert, and not able to perform a detailed analysis, but my guess would be that because you are providing values for the UNIQUE KEY in the WHERE clause, the engine can go and fetch that row directly using an index.

However, when you ask it to ORDER BY the id column, which is a PRIMARY KEY, that changes the access path. The engine now guesses that since it has an index on id, and you want to order by id, it is better to fetch that data in PK order, which will avoid a sort. In this case though, it leads to a slower result, as it has to compare every row to the criteria (a table scan).

Note that this is just conjecture. You would need to EXPLAIN both statements to see what is going on.

Turophile
  • 3,367
  • 1
  • 13
  • 21
  • the explain result are exactly the same, only one row scanned. I guess the problem might be related with the table lock. – seaguest May 07 '18 at 06:08