PHP - медленная загрузка страницы при зацикливании результатов из базы данных MySQL
У меня есть PHP-код, который выбирает результаты из базы данных MySQL - названия игрушек (в toys таблица) для users и количество игрушек и уникальных пользователей и отображает на веб-странице.
Названия игрушек могут быть либо одним словом, либо комбинацией слов. Мы хотим получить эти результаты из toys у которого есть toys_text поле, содержащее все слова словосочетаний из words таблица (пример - если words таблица имеет запись под названием blue car - И ТО И ДРУГОЕ blue а также car должен присутствовать в toys Таблица toys_text поле для матча). Со временем база данных меняется с добавлением новых игрушек и добавлением пользователей и удалением устаревших игрушек.
Получающаяся страница загружается очень медленно, в тесте GT-metrix она занимает около 10 секунд. Google Analytics сообщает о проблеме с моим кодом. Мой код имеет подзапрос следующим образом -
<?php
$WordQ = $db->query("SELECT * FROM words ORDER BY `words`.`words` ASC");
$i = 1;
while($row = $WordQ->fetch(PDO::FETCH_ASSOC)){
$Word = $row['words'];
$WordsArr = explode(" ", $row['words']);
$query = "";
if(count($WordsArr) > 1){
$query = "SELECT t_id,name,toys_text FROM toys WHERE toys_text
LIKE '%".implode("%' AND toys_text LIKE '%", $WordsArr)."%'";
}else{
$query = "SELECT t_id,name,toys_text FROM toys WHERE toys_text
LIKE '%$WordsArr[0]%'";
}
$countIds = array();
$countNames = array();
$Data = $db->query($query);
while($data = $Data->fetch(PDO::FETCH_ASSOC)){
$countIds[] = $data['t_id'];
$countNames[] = $data['name'];
}
?>
<tr>
<td><?php echo $i;?></td>
<td><?php echo $row['words'];?></td>
<td><a href="showtoys.php?word=<?php echo urlencode($Word); ?
>" target="_blank"><?php echo count(array_unique($countIds)); ?>
</a></td>
<td><a href="showtoys.php?word=<?php echo urlencode($Word); ?
>" target="_blank"><?php echo count(array_unique($countNames));
?></a></td>
</tr>
<?php $i++;} ?>
Я попытался удалить подзапрос, но страница по-прежнему медленно загружается. Пожалуйста, объясните, как сделать загрузку страницы быстрее. Ниже приведен код без подзапроса -
<?php
$query = "SELECT * FROM words ORDER BY `words`.`words`
ASC";
$result = mysqli_query($con, $query);
$i = 1;
while($row = mysqli_fetch_array($result)){
$Word = $row['words'];
$WordsArr = explode(" ", $row['words']);
$query = "";
if(count($WordsArr) > 1){
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT
t.name) AS 'cnt' from toys t WHERE toys_text LIKE '%".implode("%' AND
toy_text LIKE '%", $WordsArr)."%'";
}else{
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT t.name) AS
'cnt' from toys t WHERE
toys_text LIKE '%$WordsArr[0]%'";
}
$Data1 = mysqli_query($con, $query1);
$total1 = mysqli_fetch_assoc($Data1);
?>
<tr>
<td><?php echo $i;?></td>
<td><?php echo $row['words'];?></td>
<td><a href="showtoys.php?word=<?php echo
urlencode($Word); ?>" target="_blank"><?php echo
$total1['count']; ?></a></td>
<td><a href="showtoys.php?keyword=<?php echo urlencode($Word);
?>" target="_blank"><?php echo $total1['cnt']; ?></a></td>
</tr>
<?php $i++;} ?>
Это моя структура таблиц MySQL -
слова:
S.No Имя Тип
id Primary int(11)
слова varchar(60)
пример - белый автомобиль, воздушный шар, синий автобус
введите enum('words', 'фразу')
игрушки:
S.No. Имя Тип
t_id Первичный бигинт (20)
toys_text FULLTEXT Указатель varchar (255)
user_id BTREE Index bigint (20)
имя BTREE Index char(20)
Mysql - 10.1.30-MariaDB с хранилищем InnoDB.
Редактировать -
Скажи я в words стол, игрушка, названная как little blue car, Тогда истинные совпадения будут - у меня есть littlecar который blue, мой littlecar из blue цвет очень быстрый.
Ложные совпадения будут -
У меня есть маленькая машина. Моя маленькая машина очень хорошая.
Это означает, что все слова должны присутствовать в toys_text поле для истинного соответствия.
Я изменил запрос на
if(count($WordsArr) > 1){
$query1 = "select COUNT() as 'count',
COUNT(DISTINCT t.name) AS 'cnt' from toys t WHERE MATCH (toys_text) AGAINST
('".implode("'+'", ($WordsArr))."' IN BOOLEAN MODE)";
}else{
$query1 = "select COUNT() as 'count', COUNT(DISTINCT t.name) AS 'cnt' from
toys t WHERE MATCH (toys_text) AGAINST ('%$WordsArr[0]%')";
Теперь он дает 0 для слов с большим количеством и для словосочетаний. Этих слов нет в стоп-листе Mysql. Это какое-то ограничение поиска FULLTEXT?
4 ответа
По предложению @Sammitch, изменил запрос с помощью MATCH() в логическом режиме с циклом foreach. Теперь страница загружается в третий раз, около 3 секунд.
Это мой код -
<?php
$query = "SELECT * FROM words ORDER BY `words`.`words`
ASC";
$result = mysqli_query($con, $query);
$i = 1;
while($row = mysqli_fetch_array($result)){
$Word = $row['words'];
$WordsArr = explode(" ", $row['words']);
$query = "";
if(count($WordsArr) > 1){
foreach ($WordsArr as $value) {
$value1='';
$value1 .= " +"."(".$value."*".")";
}
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT
t.name) AS 'cnt' from toys t WHERE MATCH (toys_text) AGAINST
('$value1' IN BOOLEAN MODE)";
}else{
$query1 = "select COUNT(*) as 'count', COUNT(DISTINCT t.name) AS
'cnt' from toys t WHERE
MATCH (toys_text) AGAINST ('$WordsArr[0]* IN BOOLEAN MODE')";
}
$Data1 = mysqli_query($con, $query1);
$total1 = mysqli_fetch_assoc($Data1);
?>
<tr>
<td><?php echo $i;?></td>
<td><?php echo $row['words'];?></td>
<td><a href="showtoys.php?word=<?php echo
urlencode($Word); ?>" target="_blank"><?php echo
$total1['count']; ?></a></td>
<td><a href="showtoys.php?keyword=<?php echo urlencode($Word);
?>" target="_blank"><?php echo $total1['cnt']; ?></a></td>
</tr>
<?php $i++;} ?>
Вы генерируете и запускаете SQL из вывода оператора SQL в цикле. Просто сделайте объединение между запросами, одну обратную передачу в базу данных, один цикл для отображения результатов.
Я думаю, что проблема исходит от цикла PHP, который выполняет запросы SQL. Таким образом, чтобы сократить время, вы должны выполнять не много запросов, а строгий минимум. В вашем случае немного сложно найти чистый путь, но он должен быть!
Возможно, есть способ создать представление всех слов (одно за другим) вашей таблицы "toys" с помощью функции sql Substring. Тогда функция подзапроса является не "подобным", а простым "=", что намного быстрее. https://mariadb.com/kb/en/library/create-view/
Я предлагаю использовать хранимую процедуру, чтобы сделать ваш запрос быстрее, потому что вы использовали цикл while, тогда у вас есть другой запрос внутри него. это быстрее, если вы просто вызываете процедуру, а не создаете запрос в php.
попробуйте проверить эту ссылку для ссылок: https://dev.mysql.com/doc/refman/5.7/en/stored-routines.html
Я гарантирую вам, что он сделает гладкий запрос.
и если вам просто нужно получить счетчик тех игрушек, которые состоят из слова, я предлагаю просто запросить все в первой части перед циклом while, используя Join, затем подсчитать их и сгруппировать по слову.
$stringWord= str_replace(' ', '%', $WordsArr);
$query1 = "call toysQuery(".$stringWord.")";
тогда в вашей процедуре используйте параметр varchar.