How to do a fast simple query on a large table of postcodes 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?
Adding a postcode, population combined index might help with the ORDER BY part of the SQL.

ALTER TABLE postcode ADD KEY postcode_population (postcode, population)


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.

SELECT * FROM `postcodes` WHERE Postcode LIKE 'TERM%' order by population desc limit 0,10


You can see which index is used with EXPLAIN {query}. MySQL only used one index per table in the expression.