mysql query not using the index i want

Question

I have the following query left joining 2 tables :

explain 

    select 
        n.* from npi n left join npi_taxonomy nt on n.NPI=nt.NPI_CODE 

    where 
        n.Provider_First_Name like '%s%' and 
        n.Provider_Last_Name like '%b%' and 
        n.Provider_Business_Practice_Location_Address_State_Name = 'SC' and 
        n.Provider_Business_Practice_Location_Address_City_Name = 'charleston' and 
        n.Provider_Business_Practice_Location_Address_Postal_Code in (29001,29003,29010,29016,29018,29020,29030,29032,29033,29038,29039,29040,29041,29042,29044,29045,29046,29047,29048,29051,29052,29053,29056,29059,29061,29062,29069,29071,29072,29073,29078,29079,29080,29081,29082,29102,29104,29107,29111,29112,29113,29114,29115,29116,29117,29118,29123,29125,29128,29133,29135,29137,29142,29143,29146,29147,29148,29150,29151,29152,29153,29154,29160,29161,29162,29163,29164,29168,29169,29170,29171,29172,29201,29202,29203,29204,29205,29206,29207,29208,29209,29210,29212,29214,29215,29216,29217,29218,29219,29220,29221,29222,29223,29224,29225,29226,29227,29228,29229,29230,29240,29250,29260,29290,29292,29401,29402,29403,29404,29405,29406,29407,29409) and 
        n.Entity_Type_Code = 1 and 
        nt.Healthcare_Provider_Taxonomy_Code in ('101Y00000X') 

    limit 0,10;

I have added a multi-column index :

npi_fname_lname_state_city_zip_entity on the table npi which indexes the columns in the following order :

NPI,  
Provider_First_Name, 
Provider_First_Name, 
Provider_Business_Practice_Location_Address_State_Name, Provider_Business_Practice_Location_Address_City_Name, Provider_Business_Practice_Location_Address_Postal_Code, 
Entity_Type_Code

However, when i do an explain on the query, it shows me that it uses the primary index (NPI). Also, it says rows examined = 1

What's worse is : the query takes roughly 120 seconds to execute. How do i optimize this ?

I would really appreciate some help regarding this.

More on constructing indexes and pagination.

Rick James
– Rick James

2017-01-11 19:41:26 +00:00
Commented Jan 11, 2017 at 19:41 — Rick James
– Rick James, Commented Jan 11, 2017 at 19:41

The Scrum Meister · Accepted Answer · 2011-03-08 03:56:32Z

2

The reason why your multi column index doesn't help, is because you are filtering with a wild card like '%s%'.

Indexes can only be used when filtering using the left most prefix of the index, which means that 1) cannot do a contains search, and 2) if the left most column of the multi column index cannot be used, the other columns in the index cannot be used aswell.

You should switch the order of the columns in the index to

Provider_Business_Practice_Location_Address_State_Name, 
Provider_Business_Practice_Location_Address_City_Name, 
Provider_Business_Practice_Location_Address_Postal_Code, 
Entity_Type_Code

That way MySql will only scan the rows that match those the criteria for those columns (SC, charleston etc).

Alternatively, look into full text indexes.

edited Mar 8, 2011 at 3:56

answered Mar 8, 2011 at 0:59

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query not using the index i want

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related