Improving MySQL tables with Indexes

Question

I am very new to Indexes in MySQL. I know, I should probably have leart it earlier, but most projects been small enough for me to get away with out it ;)

So, now I am testing it. I did my test by running EXPLAIN on a query:

Query:

EXPLAIN SELECT a . *
FROM `tff__keywords2data` AS a
LEFT JOIN `tff__keywords` AS b ON a.keyword_id = b.id
WHERE (
b.keyword = 'dog' || b.keyword = 'black' || b.keyword = 'and' || b.keyword = 'white'
)
GROUP BY a.data_id
HAVING COUNT( a.data_id ) =4 

First, without indexes I got these results:

Then, with index on data_id and keyword_id i got this:

So as I understand, the number of rows MySQL has to search goes from 61k down to 10k which must be good right?

So my question is, am I correct here? And is there anything else I could think about when trying to optimize?

UPDATE:

Further more, after some help from AJ and Piskvor pointing out my other table and its column keyword not having index I got this:

Great improvement! Right?

Answer 1

As you see, the key used for table b is still NULL. You may want to add an index on b.keyword and match with

WHERE b.keyword IN ('dog','black','and','white')

This is functionally different from your WHERE clause, although it returns the same results.

As it looks, you may be interested in fulltext searching.