MySQL Forums
Forum List  »  Full-Text Search

4 Minute Full Text Index searches
Posted by: Andrew Leyden
Date: June 21, 2007 05:09AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4 Minute Full Text Index searches
6252
June 21, 2007 05:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.