Hi TK,
There was an optimizer bug in MySQL 4.0
This bug is fixed in 4.0.17 (not yet released)
# Fixed bug when the optimiser did not
# take SQL_CALC_FOUND_ROWS into account
# if LIMIT clause was present. (Bug #1274)
Kind regards
Gunnar
> -----Original Message-----
> From: wassuuuub@stripped [mailto:wassuuuub@stripped]
> Sent: Donnerstag, 4. Dezember 2003 16:13
> To: mysql@stripped
> 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
