CODEWITHSUNDEEP
mysqlperformanceindexing
javacoder
javacoder

Reputation: 752

MySQL Sorting bad performance

I see the following (simplified) query appearing in my slow-log mysql file, it's by a search engine crawler that crawls all pages from a paginated website. 

select * from listing 
where 
   active=1 
   and approved=1 
   and deleted=0 
   and enabled=1 
order by 
   full_listing desc,  
   picture_count*rating/rating_count desc, 
   rating desc 
limit 21230, 10;

I'm surprised it takes over 8 seconds to process on a table of only 60,000 records.

The explain plan looks like this

1   SIMPLE  listing0_   index_merge listing_active,listing_approved,listing_enabled,listing_deleted,sort_index  listing_active,listing_approved,listing_enabled,listing_deleted 1,1,1,1     3102    Using intersect(listing_active,listing_approved,listing_enabled,listing_deleted); Using where; Using filesort

What index can I create in order to improve its performance?

table structure:

'CREATE TABLE `listing` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account_id` bigint(20) DEFAULT NULL,
  `domain_id` bigint(20) DEFAULT NULL,
  `active` bit(1) NOT NULL,
  `approved` bit(1) NOT NULL,
  `denied` bit(1) NOT NULL,
  `deleted` bit(1) NOT NULL,
  `enabled` bit(1) NOT NULL,
  `full_listing` bit(1) DEFAULT b''0'',
  `public_id` varchar(255) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `rating` int(11) NOT NULL,
  `rating_count` int(11) NOT NULL,
  `rating_enabled` bit(1) NOT NULL,
  `picture_count` int(11) DEFAULT ''0'',
  `createdAt` datetime NOT NULL,
  `createdBy` varchar(15) NOT NULL,
  `updatedAt` datetime NOT NULL,
  `updatedBy` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `listing_public_id` (`public_id`),
  KEY `FKB4DC521D9306A80C` (`account_id`),
  KEY `FKB4DC522D7A66E1A8` (`domain_id`),
  KEY `listing_active` (`active`),
  KEY `listing_approved` (`approved`),
  KEY `listing_enabled` (`enabled`),
  KEY `listing_deleted` (`deleted`),
  KEY `listing_picture_count` (`picture_count`),
  KEY `listing_rating` (`rating`),
  KEY `listing_rating_count` (`rating_count`),
  KEY `listing_full_listing` (`full_listing`),
  CONSTRAINT `listing_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`),
  CONSTRAINT `listing_ibfk_2` FOREIGN KEY (`domain_id`) REFERENCES `domain` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59512 DEFAULT CHARSET=utf8'

Upvotes: 1

Views: 232

Answers (1)

exussum
exussum

Reputation: 18550

Using filesort could be a cause. What are the index's ? Personally i would use bitshifts for what you are doing for for example column status contains an int

with the right hand bits as this

0000 Active | Approved | Deleted | enabled

so in your example I would have where status = 1011 or status = 11 (when the bits are converted back in to ints)

Then you only have 1 index with all combinations (using 0 and 1 can sometimes make mysql ignore the index)

Upvotes: 1

Related Questions