5

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,
    `picture_file_size` int(11) DEFAULT NULL,
    `picture_updated_at` datetime DEFAULT NULL,
    `application_form_file_name` varchar(50) DEFAULT NULL,
    `application_form_content_type` varchar(50) DEFAULT NULL,
    `application_form_file_size` int(11) DEFAULT NULL,
    `application_form_updated_at` datetime DEFAULT NULL,
    `fingerprint_file_name` varchar(50) DEFAULT NULL,
    `fingerprint_content_type` varchar(50) DEFAULT NULL,
    `fingerprint_file_size` int(11) DEFAULT NULL,
    `fingerprint_updated_at` datetime DEFAULT NULL,
    `client_group_id` int(10) unsigned DEFAULT NULL,
    `priority_sector_list_id` int(10) unsigned DEFAULT NULL,
    `upload_id` int(10) unsigned DEFAULT NULL,
    `psl_sub_category_id` int(10) unsigned DEFAULT NULL,
    `occupation_id` int(10) unsigned DEFAULT NULL,
    `client_identifier` varchar(50) NOT NULL,
    `age` int(11) DEFAULT NULL,
    `age_as_on_date` date DEFAULT NULL,
    `is_loan_applicant` tinyint(1) DEFAULT '0',
    `occupation_field` varchar(50) DEFAULT NULL,
    `father_name` varchar(100) DEFAULT NULL,
    `father_dob` date DEFAULT NULL,
    `ews` int(11) DEFAULT '1',
    `residence_stability` int(11) DEFAULT '1',
    `owner_status` int(11) DEFAULT '1',
    `no_of_children` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_clients_client_group` (`client_group_id`),
    KEY `index_clients_psl_sub_category` (`psl_sub_category_id`),
    KEY `index_dedupe_variables`
    (`deleted_at`,`state`,`reference_type`,`reference`,`id`,`client_identifier`)
) ENGINE=InnoDB AUTO_INCREMENT=234631 DEFAULT CHARSET=utf8;

This is the table. Well its a lengthy table. Now i have the query 

SELECT SQL_NO_CACHE `id`, `client_identifier`, `guarantor_name`, `guarantor_dob`,
`guarantor_relationship`, `telephone_number`, `telephone_type`, `state`, `pincode`,
`income`, `family_income`, `name`, `gender`, `marital_status`, `reference`,
`reference_type`, `reference2`, `reference2_type`, `father_name`, `father_dob`,
`ews`, `residence_stability`, `owner_status`, `no_of_children`, `spouse_name`, `age`,
`age_as_on_date`, `date_of_birth`, `spouse_date_of_birth`, `active`, `inactive_reason`,
`date_joined`, `grt_pass_date`, `center_id`, `created_at`, `updated_at`, `deceased_on`,
`created_by_user_id`, `other_income`, `total_income`, `poverty_status`, `caste`,
`religion`, `created_by_staff_member_id`, `claim_document_status`,
`claim_document_recieved_by`, `cliam_document_recieved_on`, `town_classification`,
`is_loan_applicant`, `occupation_field`, `upload_reference`, `picture_file_name`,
`picture_content_type`, `picture_file_size`, `picture_updated_at`,
`application_form_file_name`, `application_form_content_type`,
`application_form_file_size`, `application_form_updated_at`,
`fingerprint_file_name`, `fingerprint_content_type`, `fingerprint_file_size`,
`fingerprint_updated_at`, `priority_sector_list_id`, `occupation_id`,
`client_group_id`, `upload_id`, `psl_sub_category_id`
FROM `clients` use index(primary,index_dedupe_variables)
WHERE `deleted_at` IS NULL AND state = 'Maharashtra'
and reference_type = 4 and reference LIKE '%8858' ORDER BY id;

I have created index on

(deleted_at,state,reference_type,reference,id)

When I explain this query I get

************* 1. row *************
d id: 1
select_type: SIMPLE
table: clients
type: ref
possible_keys: index_dedupe_variables
key: index_dedupe_variables
key_len: 167
ref: const,const,const
rows: 119122
Extra: Using where; Using filesort
1 row in set (0.00 sec)

I tried increasing sort buffer size as well as max_heap_table_size but I am not able to get rid of filesort but of no use. I even tried to force index which too didn't work. I wanted to use covering index but given the number of columns it might sound awful .I am not sure where am i going wrong :( .

Improve this question
1
  • How many rows are returned by the query?
    – ypercubeᵀᴹ
    Oct 22, 2013 at 16:17

2 Answers 2

Reset to default
4 
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.

Improve this answer
0
2

I believe the primary issue with your query may not be due to the order by on the PRIMARY KEY. In your WHERE clause, you're using a prefix LIKE comparator — reference LIKE '%8858'. Usually in such a scenario, the query optimiser cannot effectively leverage any of the indexes available.

Can you remove that and see whether explain still results in a File Sort?

Improve this answer
6
  • Yeah I did try and filesort did vanish in the explain. But I did not get Using index in the explain column. I replaced reference LIKE with reference ="Some constant value ". But it is a compulsion that I use LIKE in this query. My problem is this query takes 0.3 seconds to run and it has to run several times in given functionality. Out of 0.3 seconds 0.25 seconds are eaten up by filesort. It would be great if I can remove this
    – user2294477
    Oct 22, 2013 at 6:20
  • Using Index will only show up when you create a Covering Index over every single column involved in your query. That includes the columns you've mentioned in the SELECT clause as well. In your case, that isn't a feasible option. Also, if the key column of your EXPLAIN shows the index, then it is using it. To handle the LIKE, can't you instead use a UNION over all possible prefixes?
    – anirvan
    Oct 22, 2013 at 6:47
  • Can you explain me with an example ,
    – user2294477
    Oct 22, 2013 at 7:10
  • Here's a contrived trivial example — suppose you had to query to find animals which end with at, you'd write — SELECT type, country FROM animals WHERE type LIKE '%at'; But that gives you terrible performance. So you look into all animals and see that there's only cat, rat and say bat in the DB. You can make your query perform much better by rewriting as — SELECT animal, country FROM animals WHERE type = 'cat' UNION SELECT animal, country FROM animals WHERE type = 'rat' UNION SELECT animal, country FROM animals WHERE type = 'bat'
    – anirvan
    Oct 22, 2013 at 8:12
  • Aah !! that might not be possible. The query I am using is to check "like" duplicates of an identification such as driving license or say any unique identifier. Any more ideas ??
    – user2294477
    Oct 22, 2013 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.