15

In my database, I have a table that contains a companyId, pointing to a company, and some text. I would like to do a FULLTEXT search, but as I always make requests against a specific companyId I'd like to use a composite key that combines my companyId and the fulltext index. Is there anyway to do that ? As I guess this is not possible, what is the optimal way to create indexes so that the following query is fastest ?

The request will always be

SELECT * FROM textTable 
WHERE companyId = ? (Possibly more conditions) AND 
  MATCH(value) AGAINST("example")

Should I create my indexes on integer columns normally and add one fulltext index ? or should I include the value column in the index ? Maybe both ?

e4c5
  • 52,766
  • 11
  • 101
  • 134
hilnius
  • 2,165
  • 2
  • 19
  • 30
  • Why don't you create both, show the execution plan to see which index is used, and then make up your mind? – trincot Aug 17 '16 at 09:27
  • Have you checked this already? http://stackoverflow.com/questions/1173418/sql-server-normal-index-vs-fulltext-index also this (note that is over 3 years old, though): https://makandracards.com/makandra/12813-performance-analysis-of-mysql-s-fulltext-indexes-and-like-queries-for-full-text-search – p.marino Aug 17 '16 at 12:02
  • @trincot I could do that for the indexes I have in mind, but I won't be able to create indexes I haven't thought of. And I'm trying to get insights I can't find by myself. p.marino: My question is not to determine wether I should use a fulltext index or a normal one, but on how to create a composite key combining them (and If I can't, the closest approach) – hilnius Aug 17 '16 at 12:07
  • Not an expert with mySql query engine but in general it should not be your job to create a "composite index". Define the two indexes then the query optimizer should be smart enough to use the one on CompanyId to get a subset of records (say 100 over a complete set of 100000) and then to use the second index to filter out those that do not have your match value. Have you created both indexes, run a few sample queries and then checked what the optimizer is doing? You are not supposed to second guess the query optimizer like this. – p.marino Aug 17 '16 at 12:32
  • 4
    It is not possible to combine a fulltext index with a normal index in any way. It is not even possible to use a fulltext index for a partitioned table (if you would e.g. partition by `companyId`, that would kinda be like another index). You can use a `join` (or a subselect) like [here](http://stackoverflow.com/questions/11217713/mysql-any-way-to-help-fulltext-search-with-another-index), that allows you to use another index and might be faster depending on your data. But the fulltext search itself will not get faster by that (e.g. by limiting the rows it has to FT-search in to one `companyId`). – Solarflare Aug 17 '16 at 13:50
  • 1
    One thing that could work though (but it is a really strange workaround, and I would not like to use this myself) is to have a unique company-specific text (e.g. `companyIDABC`, `companyIDABD` and so on) for your companyIds, and add this column to your fulltext index (a composite fulltext index), and then use `match(companyIDtext, value) against ("+example +companyIDABC" in Boolean mode)`. It will make the fulltext search a little slower (and has to be in `boolean mode`), but will return less rows the other conditions have to be tested with. You can combine this with the `join` method. – Solarflare Aug 17 '16 at 13:58
  • Perfect answer(s) @Solarflare, thank you ! – hilnius Aug 18 '16 at 08:37
  • See https://dba.stackexchange.com/questions/167200/mysql-use-both-fulltext-and-normal-index – Maxim Krizhanovsky Jan 16 '18 at 14:17
  • This question has no satisfactory answers in the worst case (especially, when several tables are to be involved in the FTS or there are several columns to be searched independently per requirements). Is it because there is no answer / no way to do in MySQL? – Roman Susi Aug 17 '18 at 04:25

3 Answers3

1

Depending on how large the text field is, MySQL will store the data to disk, so including it in a compound index won't do you much good. I this scenario, the index should simply be the companyId, which will have a reference back to the PK, which will then parse each of the text fields.

The general rule of thumb is to filter early, so filtering first by companyId (int - 4 bytes) is preferable. Now, if the text field is small enough, you can add it as the second value in the index to prevent the second round-trip, but understand that this will impact INSERT performances significantly.

The best option for this type of scenario may be to use a NoSQL database to handle the text lookup, if the text fields are large; they're pretty exceptional with that type of job.

ChoNuff
  • 814
  • 6
  • 12
0

Why you're selecting everything? this is not going to help your query performance. You must select only the columns that you need. This would reduce the amount of time taking in scanning the table.

For FULLTEXT search, your method would sort the results by relevance and then uses the index lookup based on your WHERE clause. So, you need to revert that into a straight forward lookup by adding the index lookup within your SELECT clause.

The query should be something like this : 

 SELECT 
    MATCH(value) AGAINST("example")
 FROM 
    textTable

you can add more filtering based on WHERE clause, or better you may involve IF statement or CASE or any other function if you would.

something like this : 

 SELECT 
    IF(MATCH(value) AGAINST("example"), 'Your Returned value if TRUE', 'Your returned value if FALSE')
 FROM textTable

This will do a full table scan, and this is faster.

For indexing part, You can use index on companyId and include value with it. You can do the same with any other columns that you use in your SELECT, WHERE, and ORDER BY.

iSR5
  • 3,274
  • 2
  • 14
  • 13
0

Sometimes it is okay to create more indexes, each one specific for particular task.

owl
  • 154
  • 1
  • 8