CODEWITHSUNDEEP
mysqlindexingquery-optimization
YD8877
YD8877

Reputation: 10800

mysql query not using the index i want

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.

Upvotes: 1

Views: 271

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30141

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.

Upvotes: 2

Related Questions