Re: FULLTEXT and large database

[Brent Baisley]

Mysql is not sorting 200 rows, its sorting the entire found set and  
then returning the first 200 rows after it's sorted properly. So if  
your search found 2 million matches, it's going to sort the 2 million  
records, then return the first 200 records. Specifying a limit saves  
times on I/O since Mysql will only pass X records to your front end.

I'm guessing that this query is finding more records than could be  
useful, at least for processing by a human. You may want to consider  
creating a summary table that will allow one to get and overview of the  
information, and give them clues as to where they want to drill down,  
instead executing arbitrary queries. The data is not going to change,  
since it's historical information, so you could even create graphs and  
stuff. Kind of similar to some techniques used in datawarehousing.

On May 25, 2004, at 12:27 PM, James Drabb wrote:
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Harald Fuchs
Sent: Tuesday, May 25, 2004 11:18 AM
To: James Drabb
Subject: Re: FULLTEXT and large database
I seem to get inconsistent results when using the FULLTEXT index
speed-wise on 3 million+ records.  Here is a query that has been
in the "Sorting results" stage for 200 seconds.  There is a
LIMIT clause of 200, so it cannot take MySQL 200 seconds to
sort 200 records.

You've got that wrong.  MySQL has to sort *the entire result set*
(before LIMITing) in order to find the 200 most recent entries.
Thus my question how many rows the MATCH would return without the
LIMIT.
OHHHH!.  Well then it would be a few hundred thousand to possibly a few
million.
I cannot really remove any data since the admins would like 30 days
worth
And right now there is only about 3 days worth.  This project is still
in
pilot, so maybe we may need to limit to 14 days of data or so.
Jim Drabb
--  
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 
=-
=-
He who receives an idea from me, receives instruction himself without
lessening mine; as he who lights his taper at mine, receives light
without
darkening me. -- Thomas Jefferson; 1813
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 
=-
=-
James Drabb JR
Senior Programmer Analyst
Darden Restaurants
Business Systems
JDrabb at Darden dot com

--
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]