Автор оригинала: Вурдалак
— если ввести заведомо несуществующий номер страницы (больше реального количества), то я выдаю последнюю страницу. Заранее же узнать какой LIMIT подставить в запрос с SQL_CALC_FOUND_ROWS узнать нельзя.
С этим согласен.
Хотя возврат сообщения - "страница вне диапазона" - вполне решение. Раз уж пользователь такой умный, что вводит несуществующие номера страниц.
Автор оригинала: Вурдалак
— выборки не из всех таблиц подлежат кешированию. Если таблица постоянно обновляется, то в кешировании смысла нет.
-~{}~ 23.02.10 13:46:
Я вообще-то говорил о кеше mysql.
Но почему нет? Или таблица обновляется ВСЯ целиком?
Автор оригинала: Вурдалак
И всё-таки: получается, что SQL_CALC_FOUND_ROWS — говно?
Добрался я таки до базы...
15млн записей. При выборке по двум полям возвращается от 30 до 90 записей
Без индексов
Код:
mysql> show profiles;
+----------+-------------+-----------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+-----------------------------------------------------------------------------+
| 1 | 22.11624000 | select * from tours where aFCK = -101 and htlCoK = -202 |
| 2 | 21.81856400 | select sql_calc_found_rows * from tours where aFCK = -101 and htlCoK = -202 |
| 3 | 0.02533200 | select found_rows() |
| 4 | 8.07275200 | select count(*) from tours where aFCK = -101 and htlCoK = -202 |
+----------+-------------+-----------------------------------------------------------------------------+
4 rows in set (0.00 sec)
С индексами по 2м полям aFCK, htlCoK
Код:
mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------+
| 14 | 0.25131400 | select sql_no_cache count(*) from tours where aFCK = -102 and htlCoK = -201 |
| 15 | 0.21732100 | select sql_no_cache * from tours where aFCK = -102 and htlCoK = -201 |
| 16 | 0.02705800 | select sql_no_cache sql_calc_found_rows * from tours where aFCK = -102 and htlCoK = -201 |
| 17 | 0.00026900 | select sql_no_cache found_rows() |
| 18 | 0.02203900 | select sql_no_cache count(*) from tours where aFCK = -102 and htlCoK = -201 |
| 19 | 0.02309600 | select sql_no_cache sql_calc_found_rows * from tours where aFCK = -102 and htlCoK = -201 |
| 20 | 0.00010700 | select sql_no_cache found_rows() |
| 21 | 0.00010800 | select sql_no_cache found_rows() |
| 22 | 0.02371500 | select sql_no_cache sql_calc_found_rows * from tours where aFCK = -102 and htlCoK = -201 |
| 23 | 0.33617400 | select sql_no_cache sql_calc_found_rows * from tours where aFCK = -103 and htlCoK = -203 |
| 24 | 0.00022900 | select sql_no_cache found_rows() |
| 25 | 0.02457000 | select sql_no_cache * from tours where aFCK = -103 and htlCoK = -203 |
| 26 | 0.02254700 | select sql_no_cache count(*) from tours where aFCK = -103 and htlCoK = -203 |
| 27 | 0.02316000 | select sql_no_cache sql_calc_found_rows * from tours where aFCK = -103 and htlCoK = -203 |
| 28 | 0.00026800 | select sql_no_cache found_rows() |
+----------+------------+------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)
Обращаю внимание на то, что хотя указывается SQL_NO_CACHE
первый запрос по условию всегда много дольше последующих
Код:
mysql> show profiles;
+----------+-------------+------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+------------------------------------------------------------------------------------------+
| 26 | 0.02254700 | select sql_no_cache count(*) from tours where aFCK = -103 and htlCoK = -203 |
| 27 | 0.02316000 | select sql_no_cache sql_calc_found_rows * from tours where aFCK = -103 and htlCoK = -203 |
| 28 | 0.00026800 | select sql_no_cache found_rows() |
| 29 | 30.71694900 | select sql_no_cache sql_calc_found_rows * from tours limit 15000001, 10 |
| 30 | 0.00010700 | select found_rows() |
| 31 | 0.23293200 | select SQL_NO_CACHE sql_calc_found_rows * from tours where aFCK = -104 and htlCoK = 203 |
| 32 | 0.00010900 | select SQL_NO_CACHE found_rows() |
| 33 | 0.02204100 | select SQL_NO_CACHE * from tours where aFCK = -104 and htlCoK = 203 |
| 34 | 0.10406100 | select SQL_NO_CACHE * from tours where aFCK = -104 and htlCoK = 204 |
| 35 | 0.02537700 | select SQL_NO_CACHE * from tours where aFCK = -104 and htlCoK = 203 |
| 36 | 0.02523600 | select SQL_NO_CACHE COUNT(*) from tours where aFCK = -104 and htlCoK = 203 |
| 37 | 0.13862100 | select SQL_NO_CACHE * from tours where aFCK = -204 and htlCoK = 203 |
| 38 | 0.02486600 | select SQL_NO_CACHE COUNT(*) from tours where aFCK = -204 and htlCoK = 203 |
| 39 | 0.02602100 | select SQL_NO_CACHE SQL_CALC_FOUND_ROWS * from tours where aFCK = -204 and htlCoK = 203 |
| 40 | 0.00026700 | select found_rows() |
| 41 | 0.24381700 | select SQL_NO_CACHE COUNT(*) from tours where aFCK = -214 and htlCoK = 213 |
| 42 | 0.03009800 | select SQL_NO_CACHE * from tours where aFCK = -214 and htlCoK = 213 |
| 43 | 0.02215800 | select SQL_NO_CACHE SQL_CALC_FOUND_ROWS * from tours where aFCK = -214 and htlCoK = 213 |
| 44 | 0.00010600 | select found_rows() |
+----------+-------------+------------------------------------------------------------------------------------------+
19 rows in set (0.00 sec)
Код:
mysql> show profile FOR QUERY 41;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000074 |
| Opening tables | 0.000015 |
| System lock | 0.000004 |
| Table lock | 0.000007 |
| init | 0.000025 |
| optimizing | 0.000013 |
| statistics | 0.000541 |
| preparing | 0.000020 |
| executing | 0.000005 |
| Sending data | 0.242889 |
| end | 0.000009 |
| query end | 0.000003 |
| freeing items | 0.000204 |
| logging slow query | 0.000004 |
| cleaning up | 0.000004 |
+--------------------+----------+
15 rows in set (0.00 sec)
mysql> show profile FOR QUERY 42;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000070 |
| Opening tables | 0.000015 |
| System lock | 0.000003 |
| Table lock | 0.000007 |
| init | 0.000039 |
| optimizing | 0.000013 |
| statistics | 0.000104 |
| preparing | 0.000016 |
| executing | 0.000003 |
| Sending data | 0.029443 |
| end | 0.000010 |
| query end | 0.000002 |
| freeing items | 0.000367 |
| logging slow query | 0.000003 |
| cleaning up | 0.000003 |
+--------------------+----------+
15 rows in set (0.00 sec)
mysql> show profile FOR QUERY 43;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000072 |
| Opening tables | 0.000014 |
| System lock | 0.000004 |
| Table lock | 0.000006 |
| init | 0.000039 |
| optimizing | 0.000012 |
| statistics | 0.000104 |
| preparing | 0.000015 |
| executing | 0.000003 |
| Sending data | 0.021809 |
| end | 0.000012 |
| query end | 0.000003 |
| freeing items | 0.000060 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------+----------+
15 rows in set (0.04 sec)