I have a table with 2.5 Millions of Postcodes.
I have to populate a textbox while the user is searching for the Postcode so the query has to be very fast.
The table is composed by:
ID (Primary key), Postcode (Unique), Population INT(11) NOT NULL
The query is:
SELECT * FROM `postcodes` WHERE substr(Postcode,1,LENGTH_OF_TERM) ='TERM' order by population desc limit 0,10
TERM
is what the user has typed and LENGTH_OF_TERM
is the lenght of the TERM
.
Despite i have created also an index on Postcode
the query is very slow (3-4 seconds).
How i can improve the speed of the query?
Best Answer
Adding a postcode, population combined index might help with the
ORDER BY
part of the SQL.The main problem is that running a function over a column means its unable to use and index. So here should be an equivalent query that has no function over
postcode
.You can see which index is used with
EXPLAIN {query}
. MySQL only used one index per table in the expression.