CODEWITHSUNDEEP
mysqlindexing
Matthew Underwood
Matthew Underwood

Reputation: 1499

MYSQL - using where and filesort

Hello this query is producing this explain which is odd considering I have a index set up for both the columns

'1', 'SIMPLE', 'vtr_video_transactions', 'ALL', 'user_standard,user_date', NULL, NULL, NULL, '5', 'Using where; Using filesort'

CREATE TABLE `vtr_video_transactions` (
  `vtr_id` int(11) NOT NULL AUTO_INCREMENT,
  `vtr_transaction_id` int(11) unsigned DEFAULT NULL,
  `vtr_user_id` int(11) unsigned DEFAULT NULL,
  `vtr_standards_id` int(11) unsigned DEFAULT NULL,
  `vtr_video_date` datetime DEFAULT NULL,
  PRIMARY KEY (`vtr_id`),
  KEY `user_standard` (`vtr_user_id`,`vtr_standards_id`),
  KEY `user_date` (`vtr_user_id`,`vtr_video_date`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

See user_date for the index. I have it set to DESC on MYSQL Workbench. But I get filesort with the explain. Not sure why. Cheers

Data for table 

LOCK TABLES `vtr_video_transactions` WRITE;
/*!40000 ALTER TABLE `vtr_video_transactions` DISABLE KEYS */;
INSERT INTO `vtr_video_transactions` VALUES (1,1,1,2,'2015-09-05 17:18:59'),(2,2,1,3,'2015-08-27 19:04:12'),(3,2,1,4,'2015-08-27 18:55:53'),(4,10,1,119,'2015-08-27 19:04:12'),(5,11,1,10,'2015-08-27 19:04:12');

Upvotes: 0

Views: 114

Answers (1)

Drew
Drew

Reputation: 24959

See the manual page here

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

Upvotes: 3

Related Questions