Wow, thanks Brent, nice to learn something new (I'm not much of a 'server' guy)
Tried the iostat and got some results while performing a general query:
mysql> SELECT count(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('DISK');
+--+
| count(*) |
+--+
| 4975 |
+--
Grumble, grumble. I was hoping vmstat would tell us more. In a
"nutshell", every system has a bottleneck and the bottleneck is always
CPU, RAM, I/O or Network. Nutshells are great since they make things
seem so simple. You've got a 1 in 4 chance sf picking the right piece
that is your bottlenec
Thanks for the vmstat tip. I ran vmstat 1 on the query on a slightly quicker query so
I wouldn't have a ton of numbers to post from the vmstat.
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('Pink');
+--+
| COUNT(*) |
+--+
|12231 |
+--+
1
Those times shouldn't be that far apart. What is the hardware (RAM,
CPU, etc.) and OS you are running this on? Is there anything else
running on it that might be causing memory to page out?
If you are running Unix, try running: vm_stat 1
That will show you memory stats every second. Watch the
Thanks for all your help guys,
Using COUNT(*) I get the following:
mysql> SELECT COUNT(*) FROM product_fulltext WHERE MATCH(search_text) AGAINST('blue
jeans');
+--+
| COUNT(*) |
+--+
|51513 |
+--+
1 row in set (48.58 sec)
The EXPLAIN for the fulltext queries look like
If you really want to test the raw query speed, then do a SELECT
COUNT(*). That will return just a count of the number of records found,
eliminating any speed issues caused by display processing or transfer
speeds from the database to the interface. Adding a limit usually
speeds things up since
Hi Brent,
Oops, sorry about forgetting to post the key buffer size, its:
> key_buffer_size 262144000
As far as the "combined column" I'm using, I did it to make the fulltext as simple as
possible and keep the index at only 1 column. I am aware that I can create an index
for multiple columns but
On your two word plus searches, `New York` for example, have you tried using
`IN BOOLEAN MODE` to reduce the number of false positives?
AGAINST("New York" IN BOOLEAN MODE)
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/12/04 8:33 AM
Subject: RE: Fullt
Hi Victor,
The fulltext index was created on 1 column only that is of type "text".
The benchmarks I get are very inconsistant...
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('China')
>Query took 20.17 seconds
SELECT product_id FROM product_fulltext WHERE MATCH
Missed your original message. What do you mean you concatenated the
columns you wanted to search on and placed them in a table? You should
just create the full text index on multiple columns. For instance, in a
contacts table, you may have firstname, lastname. So you would create a
full text in
: Fulltext Search takes 17 sec.
Hmmm... looks like it may be impossible to do fulltext searching on 3
million rows as few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL
4.0.17 (plan to upgrade soon.. will this help??) and I created the index
that took
Hmmm... looks like it may be impossible to do fulltext searching on 3 million rows as
few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL 4.0.17 (plan
to upgrade soon.. will this help??) and I created the index that took 9 minutes to
process
12 matches
Mail list logo
