Mysql – Optimizing the table to hold a million records

index-tuningMySQL

I want to use a table with a column that should have a million records and it has to retrieve the results very fast.

Im going to use it in a query like below

Database : MySQL

select tags from tags_master where tags like 'A%';

Im trying to use it in typeahead. i tried with sample data, but for a million entries it is taking so much time.

I already tried appying indexes on that field. is there any other trick to optimize it further for typeahead purposes ?

Thanks in advance.

Best Answer

The possible solution is to limit the resulting set to 10-20 rows as well as sorting not only alphabetical but also by frequency. There are vocabularies exists that contains words along with the probability to be met in the text. May be you have to build your own based on the query statistics. That is called ranking and is used by the search engines. In the simpliest case you have the table like that:

+------+------+
! word ! prob !
+------+------+

and the query is :

SELECT word
  FROM table
 WHERE word LIKE a%
 ORDER BY prob DESC
        , word ASC
 LIMIT 10
;
Related Question