Filesort while using primary key for orderby

I have been breaking my head for the past two days now for this.

``
CREATE TABLE clients (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
guarantor_name varchar(50) NOT NULL,
guarantor_dob date DEFAULT NULL,
guarantor_relationship int(11) DEFAULT '14',
telephone_number varchar(50) DEFAULT NULL,
telephone_type int(11) DEFAULT '7',
state varchar(50) DEFAULT '',
pincode mediumint(6) unsigned NOT NULL,
income int(11) DEFAULT NULL,
family_income int(11) DEFAULT NULL,
name varchar(100) NOT NULL,
gender int(11) DEFAULT '2',
marital_status int(11) DEFAULT '1',
reference varchar(100) DEFAULT NULL,
reference_type int(11) DEFAULT '4',
reference2 varchar(50) DEFAULT NULL,
reference2_type int(11) DEFAULT '2',
spouse_name varchar(100) DEFAULT NULL,
date_of_birth date DEFAULT NULL,
spouse_date_of_birth date DEFAULT NULL,
address text NOT NULL,
active tinyint(1) NOT NULL DEFAULT '1',
inactive_reason int(11) DEFAULT '1',
date_joined date DEFAULT NULL,
grt_pass_date date DEFAULT NULL,
center_id int(11) DEFAULT NULL,
created_at datetime DEFAULT '2013-01-06 14:29:16',
deleted_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
deceased_on date DEFAULT NULL,
created_by_user_id int(11) NOT NULL,
other_income int(11) DEFAULT NULL,
total_income int(11) DEFAULT NULL,
poverty_status varchar(10) DEFAULT NULL,
caste int(11) DEFAULT '1',
religion int(11) DEFAULT '1',
created_by_staff_member_id int(11) NOT NULL,
claim_document_status int(11) DEFAULT '1',
claim_document_recieved_by int(11) DEFAULT NULL,
cliam_document_recieved_on date DEFAULT NULL,
town_classification int(11) DEFAULT '1',
upload_reference int(11) DEFAULT NULL,
picture_file_name varchar(50) DEFAULT NULL,
picture_content_type varchar(50) DEFAULT NULL,
pictu

KEY `index_dedupe_variables`
(`deleted_at`,`state`,`reference_type`,`reference`,`id`,`client_identifier`)

Try removing that index and replacing it with this one:

KEY `new_index`
(`deleted_at`,`state`,`reference_type`,`id`)

Even when an index is being used, only the useful portion of the index is used; columns to the right of the first instance where the index cannot be used are not considered in that index.

In your query, 'reference' uses a wildcard on the left side, making that column of the index not useful, and eliminating the use of id in the index.

Your explain plan tells you this is happening:

ref: const,const,const

From this, you can tell that only the first three columns of the index are actually being used by the query.

By redefining the index to eliminate the unusable "reference" column, we can use all 4 of the columns of the index, and since the index is already sorted in the order you want, we can then do this:

WHERE `deleted_at` IS NULL
  AND state = 'Maharashtra'
  AND reference_type = 4 
  AND reference LIKE '%8858' 
ORDER BY deleted_at, state, reference_type, id

We don't really want them ordered by those first three columns, but since all they are all equality comparisons, ordering by them won't change the results and it should be "obvious" to the optimizer that it can simply return the results in index order... no filesort needed.

Your EXPLAIN should change to "Using Where" and possibly "Using index" (since this can mean "using index for lookups" even when it's not a covering index.)

It's possible that the optimizer might catch on and not use a filesort even if your ORDER BY were only id\ so you might try it both ways after you change the index.

Also, remove the use index from your query. It shouldn't be needed.

StackExchange Database Administrators Q#51938, answer score: 4