9

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:

enter image description here

  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.

webmasters
  • 5,663
  • 14
  • 51
  • 78

2 Answers2

4

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.

LSerni
  • 55,617
  • 10
  • 65
  • 107
  • Ty very much, where should I add the CREAT INDEX syntax? So you mean modify the table structure? – webmasters Sep 12 '12 at 14:36
  • The command can be sent to MySQL through any administration interface (command line, phpMyAdmin, HeidiSQL, ...); the table structure isn't affected directly. If the index doesn't work, you can delete it with "ALTER TABLE cronjob_reloaded DROP INDEX test_index;". The data is not affected either. – LSerni Sep 12 '12 at 14:53
2

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 !

Community
  • 1
  • 1
metalfight - user868766
  • 2,722
  • 1
  • 16
  • 20
  • I have tried to add this to the query: USE INDEX (carid) but I got this error: Key 'carid' doesn't exist in table 'cronjob_reloaded' – webmasters Sep 12 '12 at 14:29
  • You need to CREATE an INDEX if you want to do this. See my answer for a possibility - and do not trust it, but experiment. – LSerni Sep 12 '12 at 14:34
  • You probably meant to do USE INDEX PRIMARY. You can also do FORCE INDEX PRIMARY and see which is faster. – Cargo23 Sep 12 '12 at 15:58