CODEWITHSUNDEEP
mysqldatabaseindexingfull-text-search
user1597331
user1597331

Reputation:

INDEX Mysql for string

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.

Upvotes: 1

Views: 1944

Answers (1)

Namphibian
Namphibian

Reputation: 12211

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.

Upvotes: 1

Related Questions