0

I am trying to create a SELECT query that combines 3 tables via LEFT JOIN and then MATCH specific keywords AGAINST the resulting table.

My testing so far reveals that the query is working fine and the output results are correct, however I have a doubt about FULLTEXT indexes when joining tables via LEFT JOIN.

The question is - should I add additional code to reflect the FULLTEXT index requirement for MATCH AGAINST when doing LEFT JOIN or only correct indexing of the relevant columns in the original tables is enough?

Reason for asking is this post. If I understand it correct my code should not work (since "You can't define fulltext indexes...across multiple tables in MySQL"). Therefore MATCH AGAINST for ma.name_url and v.model, would not work as they are not indexed (or this how I understand it, correct me if I am wrong :) ).

However, as the query output seems to be correct I need an expert advice if the below code is correct or I should add/modify something:

SELECT v.*, vt.color, ma.* 
          FROM tbl_items AS v 
     LEFT JOIN tbl_options
            AS vt on v.id = vt.v_id 
     LEFT JOIN tbl_names
            AS ma on v.make = ma.id 
         WHERE 
               ( 
                      ( 
                  MATCH (ma.name_url) AGAINST ('keyword1' IN BOOLEAN MODE) 
               OR MATCH (v.model) AGAINST ('keyword1' IN BOOLEAN MODE) 
                      ) 
           AND 
                      ( 
                  MATCH (ma.name_url) AGAINST ('keyword2' IN BOOLEAN MODE) 
               OR MATCH (v.model) AGAINST ('keyword2' IN BOOLEAN MODE) 
                      ) 
               ) 
           AND v.type = 4
Community
  • 1
  • 1
user3132858
  • 609
  • 1
  • 11
  • 27

1 Answers1

0

I think you are misreading the post. A single full text index only refers to one table. Actually, this is true for all other indexes, so I'm not sure why there is confusion. The index can refer to multiple columns within the table, but only to one table.

As for your query, I'm not sure which is more important -- the full text indexes or the regular indexes.

As your query is written, you are returning ma rows that do not match the match condition. Perhaps this is fine, but it looks suspicious.

And, if performance is an issue, you might find that breaking the query into separate subqueries (connected by UNION or UNION ALL) gives better performance. Each subquery can be optimized more efficiently.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So the fact that I have correctly defined Indexes and Full text indexes in the columns used in `MATCH AGAINST` is fine? What if I don't have `FULLTEXT` index on `name_url` in `tbl_names` for example? It seems that this does not affect the output? Also, yes first I am joining all `ma` rows and then I perform a `MATCH` to find which of those rows fit the condition. – user3132858 Sep 26 '15 at 12:42
  • @user3132858 . . . A boolean search does not require a FULLTEXT, although it is much slower. My point is that all the filtering may be on `v` and not on `ma`, so you will have `ma` rows were none of them match the conditions. – Gordon Linoff Sep 26 '15 at 22:29
  • thank you for the desire to help out...so basically you are saying that if I have a keyword that only matches an `ma` row, that row will not be in the query result? I tried that, search for only 1 keyword contained in just one row in `ma` and the result is as expected... – user3132858 Sep 27 '15 at 08:15
  • @user3132858 . . . No, I'm saying the opposite. If you have keywords that only match on `v`, you are going to get all the corresponding `ma` rows and they don't have the keywords. – Gordon Linoff Sep 27 '15 at 13:20