Cannot make sense of indexes

[mattal999]

Hi guys,

 

First off, my MySQL version is 5.1 Community. I have a table, imuzic_mp3 which contains approx. 450,000 rows of data.

 

Here is the table structure:

 

CREATE TABLE `imuzic_mp3` (
  `id` mediumint(7) NOT NULL AUTO_INCREMENT,
  `url` text COLLATE latin1_general_ci NOT NULL,
  `filename` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `title` tinytext COLLATE latin1_general_ci NOT NULL,
  `artist` tinytext COLLATE latin1_general_ci NOT NULL,
  `artwork` tinytext COLLATE latin1_general_ci NOT NULL,
  `length` varchar(11) COLLATE latin1_general_ci NOT NULL,
  `size` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `downloads` smallint( DEFAULT '0',
  `last_checked` datetime DEFAULT NULL,
  `check_now` int(3) NOT NULL DEFAULT '0',
  `valid` int(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `valid` (`valid`),
  KEY `valid_downloads_title` (`valid`,`downloads`,`title`(20)),
  KEY `valid_title_artist_downloads` (`valid`,`title`(20),`artist`(20),`downloads`),
  KEY `valid_title_downloads` (`valid`,`title`(20),`downloads`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

The slow query:

 

SELECT * FROM imuzic_mp3 WHERE valid=1 AND (title LIKE '%fireflies%' OR artist LIKE '%fireflies%') LIMIT 0, 25

 

Using EXPLAIN on the query returns:

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	imuzic_mp3	ref	valid,valid_downloads_title,valid_title_artist_downloads,valid_title_downloads	valid_title_downloads	4	const	139508	Using where

 

Indexes:

 

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
imuzic_mp3	0	PRIMARY	1	id	A	429518	NULL	NULL	 	BTREE	 
imuzic_mp3	1	valid	1	valid	A	2	NULL	NULL	 	BTREE	 
imuzic_mp3	1	valid_downloads_title	1	valid	A	2	NULL	NULL	 	BTREE	 
imuzic_mp3	1	valid_downloads_title	2	downloads	A	57	NULL	NULL	YES	BTREE	 
imuzic_mp3	1	valid_downloads_title	3	title	A	214759	20	NULL	 	BTREE	 
imuzic_mp3	1	valid_title_artist_downloads	1	valid	A	2	NULL	NULL	 	BTREE	 
imuzic_mp3	1	valid_title_artist_downloads	2	title	A	214759	20	NULL	 	BTREE	 
imuzic_mp3	1	valid_title_artist_downloads	3	artist	A	429518	20	NULL	 	BTREE	 
imuzic_mp3	1	valid_title_artist_downloads	4	downloads	A	429518	NULL	NULL	YES	BTREE	 
imuzic_mp3	1	valid_title_downloads	1	valid	A	2	NULL	NULL	 	BTREE	 
imuzic_mp3	1	valid_title_downloads	2	title	A	214759	20	NULL	 	BTREE	 
imuzic_mp3	1	valid_title_downloads	3	downloads	A	214759	NULL	NULL	YES	BTREE	 

 

Now, the query takes ~4 seconds to execute, which is a lot more than another similar query in which the only difference is that there is no title LIKE or artist LIKE and it is limited to 10 rows instead of 25. That query runs in ~0.001 seconds.

 

Any ideas on why it may be taking so long? The query returns 6 rows in 4 seconds.

[fenway]

You can't use an INDEX with LIKE.

[mattal999]

Wow. That's a bummer. Any ideas what I can do instead of using LIKE then?

[daydreamer]

http://www.sphinxsearch.com/ is good if your on a linux server and can install stuff. its much faster than like apparently. I have set it up its quite easy to do.

[mattal999]

Actually, I managed to make it run in 0.0034(s) by using:

 

MATCH(title, artist) AGAINST('fireflies' IN BOOLEAN MODE)

 

Thanks for the suggestion though!

[fenway]

Actually, I managed to make it run in 0.0034(s) by using:

 

MATCH(title, artist) AGAINST('fireflies' IN BOOLEAN MODE)

 

Thanks for the suggestion though!

Just remember that FULLTEXT is funny.