The MYSQL "explain query" shows "key" NULL

 

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:

在这里输入图像描述

  • Why is the key NULL?

  • 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.

    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 (eg 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.

    As per the docs:

    "If key is NULL, MySQL found no index to use for executing the query more efficiently."

    Please refer below link for Official document on it.

    Mysql Doc

    Edit :

    Here are the links for Index

    How mysql Index work's - SO

    How to create index

    Hope this help !

    链接地址: http://www.djcxy.com/p/63526.html

    上一篇: 获取最近和运行的应用程序列表不是进程

    下一篇: MYSQL“解释查询”显示“键”NULL