Is it possible to index all fields in this mysql query?

Question

I have this mysql query and I'm not sure what are the consequences of indexing all the fields in the query. I mean, is it okay to index all fields in a CASE, Statement Statement and Where Statement? Are there any implications for indexing fields?

SELECT roots.id as root_id, root_words.*, CASE WHEN root_words.title LIKE '%text%' THEN 1 WHEN root_words.unsigned_title LIKE '%normalised_text%' THEN 2 WHEN unsigned_source LIKE '%normalised_text%' THEN 3 WHEN roots.root LIKE '%text%' THEN 4 END as priorities FROM roots INNER JOIN root_words ON roots.id=root_words.root_id WHERE (root_words.unsigned_title LIKE '%normalised_text%') OR (root_words.title LIKE '%text%') OR (unsigned_source LIKE '%normalised_text."%') OR (roots.root LIKE '%text%') ORDER by priorities 

Also, how can I further increase the query speed higher?

Thanks!

Answer 1

• You are indexing columns in tables, not in queries.

• None of the search criteria that you specified can use indexes (since search terms start with a wild card).

• You need to make sure the id column is indexed in order to speed up JOIN . (Presumably, it has already been indexed as a PRIMARY KEY in one table and FOREIGN KEY in another).

To speed up this query, you will need to use full-text search. Adding indexes will not speed up this particular query and will cost you time on INSERT, UPDATE and DELETE.

Answer 2

Warning. Indexes speed up search time, but slow down insertion and updating.

Answer 3

In order to answer the consequences of indexing each field, when using indexes, a performance hit occurs when indexed data is changed either through inserts, updates, or deleted. This is because SQL must support the index. This is a balance between how often data is read in comparison to how often it changes.

In this particular query, the only index that could help would be in your JOIN clause in the roots.id and root_words.root_id .

None of the checks in your WHERE can be indexed due to the leading '%' . This causes SQL to scan each row in these tables for the corresponding value.

If you can remove the leading '%' , then indexes in these fields will be useful to you ... if not, you should study the full-text search implementation; but be careful, this is not trivial.

Answer 4

Indexing will not help when used in conjunction with LIKE '%something%' .

It is like looking up words in a dictionary that have ae . The dictionary (or index in this case) is organized based on the first letter of the word, then the second letter, etc. He does not have a mechanism for all words with ae to be close to each other in them. You are still reading the entire dictionary from start to finish.

Indexing the fields used in the CASE clause will most likely not help. Indexing helps simplify the search for records in a table. The CASE clause is the processing of found records, not their search in the first place.

Optimizers can also struggle with optimizing several unrelated OR conditions, such as yours. The optimizer tries to narrow down the effort to complete your request, but it's hard to do when unrelated conditions can make the record acceptable.

In general, your query will be useful from indexes on roots(root_id) and / or roots(id) , but not much more.

If you specify additional fields, then there are two main costs: - Increased recording time (insert, update or delete) due to additional indexes for writing to
- Increased disk space