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