The MYSQL "explain query" shows "key" NULL

Question

I have made mysql explain the following query:

SELECT carid,pic0,bio,url,site,applet 
FROM cronjob_reloaded 
WHERE 
carid LIKE '%bmw%' 
OR 
carid LIKE '%mer%' 
OR 
age BETWEEN '5' AND '10' 
OR 
category IN ('used') 
ORDER BY CASE 
    WHEN carid LIKE '%bmw%' OR carid LIKE '%mer%' THEN 1 
    WHEN age BETWEEN '5' AND '10' THEN 2 
    ELSE 3 
END 

And here is the explain result:

EXPLAIN SELECT carid, pic0, bio, url, site, applet
FROM cronjob_reloaded
WHERE carid LIKE '%bmw%'
OR carid LIKE '%mer%'
OR carid IS NOT NULL
AND age
BETWEEN '5'
AND '10'

What I do not understand it this:

1. Why is the key NULL?

2. Can I make this query faster? It takes 0.0035 sec - is this slow or fast for a 1000 rows table?

In my table carid is the primary key of the table.

Answer 1

MySQL did not find any indexes to use for the query.

The speed of the query depends on your CPU, and for so few rows, also on available RAM, system load, and disk speed. You can use BENCHMARK to run the query several times and time it with higher precision (e.g. you execute it 100,000 times and divide the total time by 100,000).

As for the indexing issue: your WHERE clause involves carid, age, category (and indirectly performerid). You ought to index on category first (since you ask a direct match on it), age, and finally carid.

CREATE INDEX test_index ON cronjob_reloaded ( category, age, carid );

This brings together most of the information that MySQL needs for the WHERE phase of the query in a single index operation.

Adding performerid may speed this up, or not, depending on several factors. I'd start without and maybe test it later on.

Update: the original query seems to have changed, and no performerid appears anymore.

Finally, 1000 rows usually requires so little time that MySQL might even decide not to use the index at all since it's faster to load everything and let the WHERE sort out its own.