MySQL - Best Index and Most Efficient Select Statement

Question

I have a MySQL table. It has two columns. First one is a unique identifier (INT) and the second is a string (VARCHAR). There's around 1.7m records. At the moment, the only search I am doing is:

select name from artist_name where name like '$q%'"

My question is what would be the best index to put on this table and how could I improve the efficiency, if at all, of the SQL statement? At some point I will be introducing a 2nd table that will join on the first column but I'll tackle that at a later point.

Answer 1

Use B-Tree indexes

Check this.

Answer 2

It looks like putting an index on your second column (artist_name) so that your search queries with like clause could work faster would be the best practice. Undoubtedly, you can add an unique index on your unique identifier, if it does not exist already.

Furthermore, you may consider putting your index as partial specifying the length of the name of the artist to be searched.

Check this article for detailed information about the index types in MySql.

Answer 3

SphinxSearch - Full text search

Do this,

1. execute this query in mysql prompt and record the execution time,

   SELECT name FROM artist_name WHERE name LIKE '$q%'";

2. Install sphinxsearch.

3. Now execute the same query like below,

   SELECT name FROM artist_name WHERE MATCH('$q');

You will see the execution time to be reduced astronomically.

I have given a high level knowledge above.

1. To install sphinx search and index your current table column is very easy and yes there are a series of steps to follow but once done you will be lucky.
2. I use sphinxsearch in my app, and the features like "stopwords","wordforms","wordstemming" makes you feel so good.

Other options available:

1. Mysql full text search : Its pretty decent too, if you are not looking for customizing anything you can use mysql full text by just doing a,

ALTER TABLE artist_name ADD FULLTEXT(name);

SELECT * 
FROM artist_name
WHERE MATCH(name) 
AGAINST ($q);

If you want any help on how to install sphinx search I will be there.

Next time follow the right convention in your sql queries, this is STACKOVERFLOW, not FACEBOOK.