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 you "limit" the amount of data being transferred
out of the database. If your front end and database are not on the same
machine, then the amount of data being transferred over the network can
have a huge impact.
What does your EXPLAIN look like for these queries?
On Aug 12, 2004, at 10:23 AM, [EMAIL PROTECTED] wrote:
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 basically
I just selected all the columns that may contain keywords for each
product and tossed them into 1 table with the PK as product_id. Just
thought this might help performance... maybe I'm wrong, I don't know.
If I can ever get the MATCH, AGAINST query down to a faster speed then
I will really focus on getting the top 3 for each category. Sorting
into categories in PHP would work but I'd need a much larger dataset
in order to insure that I got all the possible matches for each
category... "what if there was only 1 match in the 'Movies' category
and it was at the bottom of the results...?" I'm thinking that we
will have to use some other method such as listing by relavence
(fulltext ordering) then showing "Search within: Category X, Category
Y, Category Z..." links on the side (like Ebay I guess). Still the
problem arises as to which categories qualify. It would be nice to
order them by the category with the most matches but I doubt that can
be accomplished.
Previously I had not been using a LIMIT because I was going to do
processing for category grouping etc. However, if I do use a LIMIT,
the query speeds are almost totally dependant on the number of rows
returned:
WITH LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('san francisco') LIMIT 1000
Query doine in 2.01 seconds
Num Rows: 1000
WITHOUT LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('san francisco')
Query doine in 13.45 seconds
Num Rows: 9287
Strange. Is this typical or do I need to tweek my system variables?
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
