Which query is more fast and less redundant in mySQL innoDB?

Question

SELECT * FROM inv, clients 
WHERE   inv.client_id = clients.ID  
AND     ( inv.type = "STA"   
OR        inv.type = "FIN" )

OR

SELECT * FROM invoice, clients 
WHERE  inv.client_id = clients.ID  
AND MATCH (inv.type) AGAINST ("STA FIN")

I know these are 2 questions, but maybe they go together

Answer 1

Short answer: Use inv.type IN ("STA", "FIN") and INDEX(client_id, type).

Long answer:

MATCH...AGAINST is terribly slow unless you have a FULLTEXT index on type. However, this is a misuse of FULLTEXT, so I do not recommend it.

AND ( inv.type = "STA" OR inv.type = "FIN" ) is automatically turned into AND inv.type IN ("STA", "FIN"), which is reasonably fast. If there is INDEX(type), it might use that index and be really fast. The utility of this depends on the cardinality -- "type" sounds like a column with very few different values, hence an index may be useless.

Another issue: Don't use the antiquated "commajoin", use JOIN...ON:

SELECT *
    FROM invoice
    JOIN clients ON inv.client_id = clients.ID  -- How the tables relate
    WHERE inv.type IN ("STA", "FIN")  -- filtering

You should have

inv:  INDEX(client_id, type)

Using that 'composite' index takes care of both the ON and the WHERE, while avoiding the "low cardinality" issue I mentioned above.