Hello,
Thanks for the response.
There is one thing that is not clear however.
Regardless of whether or not I perform the fulltext
search with or without the SQL_CALC_FOUND_ROWS
keyword, the results that I get are exactly the same.
Also, the notion of stopping after the limit is
reached cannot apply in the fulltext search or
otherwise we would only get the first 10 matches but
not the first 10 "most relevant" matches. This leads
me to believe that the fulltext search must be looking
at all the rows in both cases since it otherwise would
not find the same first 10 most relevant records.
Hence the question why there should be a difference in
time.
Just to check, I also performed a search with a limit
that was greater than the number of rows in my table
and the first 10 records were again the same.
Here are the results:
rows: about 100,000
colums: average of 500 words
--- RUN 1 ---
test run with SQL_CALC_FOUND_ROWS (pc was rebooted)
---
SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+--------+
| id |
+--------+
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
+--------+
10 rows in set (94.16) sec
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+-------+----------+---------------+----------+
| table | type | possible_keys | key |
+-------+----------+---------------+----------+
| main | fulltext | abstract | abstract |
+-------+----------+---------------+----------+
---------+--------+------+-------------+
key_len | ref | rows | Extra |
---------+--------+------+-------------+
0 | | 1 | Using where |
---------+--------+------+-------------+
SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10;
select FOUND_ROWS()
17501 rows
--- RUN 2 ---
test run without SQL_CALC_FOUND_ROWS (pc was rebooted)
---
SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+--------+
| id |
+--------+
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
+--------+
10 rows in set (0.11) sec
EXPLAIN SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+-------+----------+---------------+----------+
| table | type | possible_keys | key |
+-------+----------+---------------+----------+
| main | fulltext | abstract | abstract |
+-------+----------+---------------+----------+
---------+--------+------+-------------+
key_len | ref | rows | Extra |
---------+--------+------+-------------+
0 | | 1 | Using where |
---------+--------+------+-------------+
--- RUN 3 ---
test run without SQL_CALC_FOUND_ROWS and with high
limit (pc was rebooted)
---
SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
limit 100000
+--------+
| ppt_id |
+--------+
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
...
17501 rows in set (94.22) sec
EXPLAIN SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
limit 100000
+-------+----------+---------------+----------+
| table | type | possible_keys | key |
+-------+----------+---------------+----------+
| main | fulltext | abstract | abstract |
+-------+----------+---------------+----------+
---------+--------+------+-------------+
key_len | ref | rows | Extra |
---------+--------+------+-------------+
0 | | 1 | Using where |
---------+--------+------+-------------+
So to summarize the question:
To get the "most relavent" first 10 results, fulltext
seach must be going through all records with or
without the SQL_CALC_FOUND_ROWS keyword, so why would
there be such a huge difference in time.
Thanks,
TK
--- Matt W <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Yes, you would have similar results with any query
> that uses
> SQL_CALC_FOUND_ROWS. That's because MySQL has to see
> how many rows would
> be found without the LIMIT. So in your case, it
> can't just abort the
> query after it finds 10 rows. All rows that match
> the WHERE need to be
> found.
>
> You might want to try your fulltext search IN
> BOOLEAN MODE to see if
> that runs any faster. :-)
>
>
> Hope that helps.
>
>
> Matt
>
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> Sent: Thursday, December 04, 2003 9:13 AM
> Subject: fulltext search speed issue with
> SQL_CALC_FOUND_ROWS
>
>
> > I have some_table with 100,000 rows and with an
> > average of 500 words in some_column of each row.
> When
> > i do a fulltext search on this table using a query
> > such as the following, all of my results are under
> 0.1
> > seconds:
> >
> > SELECT something
> > FROM some_table
> > WHERE MATCH (some_column) AGAINST
> ('some_search_term')
> > LIMIT 0,10
> >
> > However, when i add the SQL_CALC_FOUND_ROWS
> keyword
> > like in the following query, some queries take
> longer
> > than 1 minute:
> >
> > SELECT SQL_CALC_FOUND_ROWS something
> > FROM some_table
> > WHERE MATCH (some_column) AGAINST
> ('some_search_term')
> > LIMIT 0,10
> >
> > How can there be a huge difference in speed if
> both
> > queries always return the exact same results?
> >
> > Thanks,
> > TK
>
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
