4 Minute Full Text Index searches
Hi,
New here so I'm reposting this from the newbie category after I was told this was the better place for my question.
I have a database with about 500,000 rows that contains about 400mb of data. I've been having some real problems doing some searches of late, with some FULLTEXT INDEX searches taking upto 4 minutes to produce a reply.
The funny thing (to me at least, a newb) is that the like %$word% searches are pretty quick.
I have a FULLTEXT index set on the title and the description fields, along with a unique key on another field to prevent some duplicate records. I have the query cache enabled but I think it is too small for this data set. After some advice in the newbie area, I redid this index to just one field (description) but that only shaved a minute off the overall search (4 min to 3 min)
I was kind of under the impression that INDEX searches are faster than LIKE % searches. Does the string length (LIKE "%12345%") make a difference (I'm noticing longer ones seem a bit slower than shorter ones). But the numbers on the index search (4 minutes) really have me scratching me head like I've missed some configuration.
select * from ishows WHERE MATCH (title, description) AGAINST ('music');
49102 rows in set (4 min 19.61 sec)
49102 rows in set (4 min 53.92 sec)
select * from ishows WHERE MATCH (description) AGAINST ('music');
49102 rows in set (3 min 05.07 sec)
select * from ishows where description like "%music%";
54765 rows in set (14.97 sec)
54765 rows in set (29.46 sec)
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 49999872 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.00 sec)
TABLE `ishows` (
`id` int(11) default NULL,
`title` varchar(255) default NULL,
`link` varchar(255) default NULL,
`description` text,
`url` varchar(255) default NULL,
`type` varchar(255) default NULL,
`length` varchar(20) default NULL,
`date` varchar(100) default NULL,
`sid` int(11) NOT NULL,
`updated` datetime default NULL,
UNIQUE KEY `DoubleUnique` (`iid`,`eurl`),
FULLTEXT KEY `title` (`title`,`description`)
) ENGINE=MyISAM AUTO_INCREMENT=1830727 DEFAULT CHARSET=latin1