1

I have a large database containing more than five million records, this database has three fields (ID, name, text), the field ID has a primary key, the field name has a FULLTEXT index.

I want to create a search engine for my site that seeks in the field name, I use FULLTEXT index but has the disadvantage not to accept the keywords of less than four characters, so I decided to delete it and put a INDEX KEY on the field name and use the following request:

EXPLAIN SELECT * FROM table WHERE locate ('search', name) > 0;

the problem is that this application does not use the index KEY field name, but this request:

EXPLAIN SELECT name FROM table WHERE locate ('search', name) > 0;

uses the INDEX KEY, I do not know why when I select all fields MYSQL does not use index.

In your opinion how to solve this problem and if possible a better alternative.

Improve this question
1

1 Answer 1

Reset to default
1

You can set the minimum amount of characters for full text indexes in the mysql configuration. I am not at my computer at the moment to find a example however this page might help you: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

Update:

Back at my pc. So regarding why mysql would use a index on the SELECT name FROM table WHERE locate ('search', name) > 0; statement is very simple. When you create a index on the name field the index contains the actual name field i.e. the value of the name field, so when you select only the name field mysql can do a search and retrieve all the data required from the index. So in this scenario mysql has to do one operation to retrieve the data which match the searched values in the index and return them.

The SELECT name FROM table WHERE locate ('search', name) > 0; however needs the other data fields as well. Since only the name field's value is stored in the index mysql will have to read the index and then the table to retrieve the other fields. So in this scenario mysql has to match the values in the index then find the values on the table and then return them. This means mysql has to do 2 operations which is double the amount of work compared to the previous scenario.

Since 5 million rows is still very small it is probably faster for mysql to just loop through the table and retrieve the rows. As you add more rows mysql will probably start using the index once the cost of looping through the table is higher than the cost of reading the index and then looking up the values on the table.

Hope that makes sense.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for everything I think I understood what you said, I think I'll have to change the fulltext index to accept requests less than four characters and modify the list of blocked words. What do you think?
Sorry been really busy so only responding now. There is no right or wrong answers with these types of questions only successful strategies. I would suggest that you experiment with the different approaches right now and then monitor and evaluate and if you need to change your strategy later on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.