Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello, Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Yes, it could. It is the optimization that wasn't implemented yet. (but it's in the TODO) Once again, thanks for the response. Could you give an estimation (even if rough) of how soon this optimization will be implemented? Thanks, TK Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi! On Dec 04, tk wrote: 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. The difference is that you only need to read 10 rows from the disk without SQL_CALC_FOUND_ROWS. With SQL_CALC_FOUND_ROWS MySQL goes on and reads all rows, it takes time. Finding relevant rows and sorting is based on index only, row data are not read. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search speed issue with SQL_CALC_FOUND_ROWS
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Donnerstag, 4. Dezember 2003 16:13
To: [EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello Sergei, Gunnar, and others, thank you for your quick responses. One little mystery remains: Why does one need to read all the row data (with SQL_CALC_FOUND_ROWS) to get the total number of results when using a limit? When the index is used to find relevant rows and sort the results, the code certainly must know how many total results there are. Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Thanks and regards, TK --- Sergei Golubchik [EMAIL PROTECTED] wrote: Hi! On Dec 04, tk wrote: 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. The difference is that you only need to read 10 rows from the disk without SQL_CALC_FOUND_ROWS. With SQL_CALC_FOUND_ROWS MySQL goes on and reads all rows, it takes time. Finding relevant rows and sorting is based on index only, row data are not read. Regards, Sergei __ 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]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi! On Dec 05, tk wrote: Hello Sergei, Gunnar, and others, thank you for your quick responses. One little mystery remains: Why does one need to read all the row data (with SQL_CALC_FOUND_ROWS) to get the total number of results when using a limit? When the index is used to find relevant rows and sort the results, the code certainly must know how many total results there are. Yes, but it's different code, it works on a different level and knows nothing about SQL_CALC_FOUND_ROWS :( Could one not store the total while using the index and use select FOUND_ROWS() without SQL_CALC_FOUND_ROWS to retrieve the total? Yes, it could. It is the optimization that wasn't implemented yet. (but it's in the TODO) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
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 10
++
| 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 10
+---+--+---+--+
| 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
