Search code examples
mysqlsqldatabasedatabase-performance

slow MySQL database query on several fields and 140k+ records

I have the below query:

 SELECT ID FROM 

`Article` this_ WHERE
(this_._Temporary_Flag = FALSE OR this_._Temporary_Flag = NULL) AND 
 this_.Published = TRUE AND
  (this_.PublishedOn IS NULL OR this_.PublishedOn <= '2012-10-29 08:54:36') AND 
  (this_.Deleted = FALSE OR this_.Deleted = NULL) AND 
  (this_._ComputedDeletedValue = FALSE OR this_._ComputedDeletedValue = NULL) AND 
  ((this_._TestItemSessionGuid IS NULL OR this_._TestItemSessionGuid = '')) AND 
  NOT (this_.CategoryId IS NULL) 
  AND (this_.PublishedOn < '2012-09-10 00:00:00' AND this_.CategoryId = 51118080)
   ORDER BY this_.PublishedOn DESC LIMIT 1

The table in question contains 141,505 records to be exact. I've checked the indexes and all of the fields mentioned in the WHERE clause and ORDER BY are indexed (Index Kind: INDEX, Index Type: BTREE).

This query takes 40seconds to run for the first time. Subsequent runs are down to 1 - 2sec, which I am assuming is due to caching. If I restart the MySQL Server, it again takes approximately 40 seconds again. Any ideas why it is performing so slow, and any optimisations that can be done?

Update 1

Database is MySQL, and table storage engine is InnoDb.

Update 2

As a side-note, the scope of this query is to get the 'previous article', related to the current one. I am doing this by getting all the articles which have their PublishedOn field less than the current ones, ordering by the PublishedOn descending and taking the first 1 (I updated the LIMIT as by mistake I originally entered it 50, when it should be 1).

The other conditions are to load only valid articles, and articles in the same category.

Update 3: EXPLAIN output

SelectType = Select
Type = index_merge
Possible_keys = CategoryId,PublishedOn,_TestItemSessionGuid,Deleted,_ComputedDeletedValue,_Tempo‌​rary_Flag,Published Key = Deleted,_ComputedDeletedValue,_Temporary_Flag,Published,CategoryId
Key_Len = 1,1,1,1,9
ref = (NULL)
Rows = 3383
Extra = Using intersect(Deleted,_ComputedDeletedValue,_Temporary_Flag,Published,CategoryId); Using where; Using filesort ______
Solution

  • Make sure you have (1) indexed your table fields and (2) Partition table e.g table field PublishedOn are best to partition by range You may refer to http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html for more details.