CODEWITHSUNDEEP
mysqlindexingfull-text-search
PHPCore
PHPCore

Reputation: 121

Can i use 2 different indexes on a fulltext search?

i'm not very very experimented with the indexes so that's why i'm asking this silly question. i searched like everywhere but i didn't get a clear answer.

I will have a table items with columns: id,name,category,price

Here will be 3 indexes:

  1. id - Primary Index
  2. name - FullText Index
  3. category,price - Composite Index

I estimate my table in future will get like 700.000-1.000.00 rows.

I need to do a fulltext search for name and where category is a specified category and order by price.

So my query will be this:

SELECT * FROM items
WHERE MATCH(name) AGAINST(‘my search’) and category='my category' order by price

My question is: How many index will be used to perform this search?

It will use 2 indexes?

[fulltext index] & [category,price] index - Will get results for words and then will use the next index to match my category and price order

It will use 1 index

[fulltext index] only - Will get results for words, but after will have to manually match my category and price order

I want my query to be fast, what are you opinions? I know the fulltext search is fast, but what happen if i apply clauses like: category and price order? will be same fast?

Upvotes: 0

Views: 75

Answers (1)

Namphibian
Namphibian

Reputation: 12221

MySQL will only ever use one index in any search. The reason being that using two indexes will require two searches. This will make the query much more slower. You can force MySQL to use a specific index in a query but this is not a good idea.

In summary: MySQL will only ever use one index it cant use two indexes.

Upvotes: 1

Related Questions