| Forum: FullText |
|---|
| Topic: Slow Full-Text Search when table grows constantly |
|---|
| Slow Full-Text Search when table grows constantly [message #2752] |
Tue, 18 March 2008 02:20 |
 cod_ferrow
Messages: 1
Registered: March 2008 |
Junior Member |
|
|
Hello,
I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields that contain over 20 000 characters on each row.
The thing is, as I could observe, that when multiple different full-text searches are made in appropiate period of time, the query doesn't take so much to respond (though i couldn't quite say it's fast), but when no search is made, let's say, for over 2-3 hours, the same query takes up to 30-40 seconds to respond.
The query is simple:
SELECT
id
day_processed,
date_processed,
site_id,
MATCH (content1, content2) AGAINST ('some text here')
FROM details
WHERE (1 = 1)
AND MATCH (content1, content2) AGAINST ('some text here' IN BOOLEAN MODE)
The table is defined like this:
CREATE TABLE `details` (
`id` int(11) NOT NULL auto_increment,
`day_processed` date default NULL,
`date_processed` datetime NOT NULL default '2008-01-01 00:00:00',
`url` varchar(500) NOT NULL default 'http://',
`content1` varchar(1000) character set utf8 default NULL,
`content2` varchar(20000) character set utf8 default NULL,
`site_id` int(3) unsigned NOT NULL default '0',
`price` varchar(15) default NULL,
`phone` varchar(50) default NULL,
`email` varchar(40) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `url` USING HASH (`url`(200)),
KEY `date_processed` (`date_processed`),
KEY `id_site` (`id_site`),
KEY `day_processed` (`day_processed`),
FULLTEXT KEY `content` (`content1`,`content2`)
) ENGINE=MyISAM AUTO_INCREMENT=156243 DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC
The MySQL Server is configured as Dedicated Server Machine (with MySQL Instance Configurator), it has dual core processor and 2Gb of RAM.
I would appreciate any suggestion for ways to improve speed. Please let me know if you need any additional information.
Best regards
|
|
|
| Topic: Japanese characters full text search |
|---|
| Japanese characters full text search [message #2559] |
Thu, 31 January 2008 02:53 |
arthurica
Messages: 7
Registered: January 2008 |
Junior Member |
|
|
I am having some problems searching using the MATCH function for japanese words...
Here is the situation.. As you might know japanese words are not separate with spaces.. Here are 2 words (a composite one and a simple one)
1. ストライプジャケット (strive jacket)
2. ジャケット (jacket)
When I search for the first word I can find it. When I search for the second word (using MATCH function ojn a full text field) it will not find records containing the first word. It does makes sense to me since the words are not separated by spaces however how can I make it work?
I would like to mention that in some cases this will work even if the words are not separate by spaces so I guess I might be missing something
Is there an alternate solution for this? Most of the sites from Japan seems to have a solution for this and this applies for other sites as well
Any suggestion is really appreciate
|
|
|
| Topic: How to write a UDF for MySQL fulltext tokenization? |
|---|
| How to write a UDF for MySQL fulltext tokenization? [message #2452] |
Sun, 06 January 2008 01:56 |
howachen
Messages: 7
Registered: February 2007 |
Junior Member |
|
|
I want to write a UDF for tokenize Chinese so that MySQL fulltext can cover that, any docs/article I can get started with?
Thanks. 
[Updated on: Sun, 06 January 2008 01:56]
|
|
|
| Topic: AND as the default modifier for boolean search |
|---|
 AND as the default modifier for boolean search [message #2078] |
Wed, 17 October 2007 07:28 |
Spuerhund
Messages: 4
Registered: August 2007 |
Junior Member |
|
|
I would like to offer my users the same behaviour that they are already used to. Let's take for example Google. Google has "AND" as the default operator, which means that you get less and less results with every word that you add to your first query.
In MySQL the default modifier is (according to the manual
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html):
(no operator)
By default (when neither + nor - is specified) the word is optional, but the rows that contain it are rated higher. This mimics the behavior of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.
That sounds nice, but acctually in most cases it just sucks. The result gets worse when you add another keyword to a one-word query and most users are confused when they now get results which didn't appear in the first result set, because they wanted to "filter" the first result even more.
Unfortunately, in all the years i haven't found a single user so far who was willingly to read the "HOW TO" explanation of my search function and went through a short crash course of boolean operators and "strange signs" (many don't even know where + and - are hiding on their keyboard). That's just a fact.
Therefore it would be very convenient to change the default operator to "+", so that without any given operator MySQL uses AND in case more than one word is given.
So far i haven't found a pure MySQL solution and it looks like i have to write my own query parser to split every query by "..." and (...) phrases, then by words, etc. and then add the missing "+" sign in front of every word without a modifier. Friendly, i am not eager to do that, as this should be done by the DB and not by some application logic on top.
What could be done? Any suggestions are welcome ... 
|
|
|
Members
Search
Help
Register
Login
Home
