Лучший способ поиска в таблице и получения результатов и количества результатов (MySQL)
У меня есть таблица «предметов» и таблица «ключевых слов предметов». Когда пользователь ищет ключевое слово, я хочу дать ему одну страницу результатов плюс общее количество результатов.
В настоящее время я делаю (для пользователя, который ищет "a b c":
SELECT DISTINCT {fields I want} FROM itemkeywords JOIN items
WHERE (keyword = 'a' or keyword='b' or keyword='c'
ORDER BY "my magic criteria"
LIMIT 20.10
а затем я делаю тот же запрос с подсчетом
SELECT COUNT(*) FROM itemkeywords JOIN items
WHERE (keyword = 'a' or keyword='b' or keyword='c'
Это может привести к получению довольно большой таблицы, и я считаю, что это решение ужасно отстойно ...
Но я не могу придумать ничего лучше.
Очевидная альтернатива - избежать двойного обращения к MySQL, который выполняет только первый запрос без предложения LIMIT, а затем переходить к правильной записи, чтобы отобразить соответствующую страницу, а затем к концу набора записей, чтобы подсчитать результат, кажется еще хуже...
Есть идеи?
ПРИМЕЧАНИЕ: я использую ASP.Net и MySQL, а не PHP.
Ответы 4
Добавьте SQL_CALC_FOUND_ROWS после выбора в вашем ограниченном выборе, затем выполните «SELECT FOUND_ROWS ()» после завершения первого выбора.
Пример:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
У вас есть 2 варианта:
В MySQL API должна быть функция, возвращающая количество строк. Используя более старый API, mysql_num_rows (). Это не сработает, если вы используете небуферизованный запрос.
Более простой способ - объединить оба ваших запроса:
SELECT DISTINCT {fields I want}, count(*) as results
FROM itemkeywords JOIN items
WHERE (keyword = 'a' or keyword='b' or keyword='c'
ORDER BY "my magic criteria"
LIMIT 20.10
Я провел несколько тестов, и на функцию count (*) не влияет предложение limit. Сначала я бы проверил это с DESCRIBE. Я не знаю, насколько это повлияет на скорость вашего запроса. Запрос, который должен выдавать только первые 10 результатов, должен быть короче, чем тот, который должен найти все результаты для подсчета, а затем первые 10., но я могу ошибаться здесь.
Вы можете посмотреть на MySQL SQL_CALC_FOUND_ROWS в своем первом операторе, за которым следует второй оператор SELECT FOUND_ROWS(), который, по крайней мере, не позволяет вам выполнять 2 запроса данных, но все равно будет иметь тенденцию выполнять сканирование всей таблицы один раз.
См. http://dev.mysql.com/doc/refman/5.0/en/select.html и http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
Лучше подумать: нужна ли вам эта функция В самом деле?
Если вы действительно беспокоитесь о производительности и вам действительно нужно выполнить два запроса, вы можете рассмотреть возможность кэширования общего количества совпадений, поскольку это не изменится, когда пользователь просматривает страницы результатов.
В прошлых приложениях я использовал оба метода (два запроса и один запрос без ограничения) в зависимости от того, сколько результатов я ожидал. Одна мысль, которая у меня была (хотя я никогда не пробовала), - это соединение с почти идентичным подзапросом, чтобы получить счет. Это было бы попаданием в 1 БД, но все еще есть ввод-вывод.