如何使此查询运行得更快 - How to make this query run faster

How to make this query run faster

本文关键字：运行何使此查询 | 更新日期: 2023-09-27

我想知道这个查询是否可以运行得更快，或者如果可能的话，如何使它更快。

$result = mysql_query("select
(select count(1) FROM videos WHERE title LIKE '%Cars%')as Cars,
(select count(1) FROM videos WHERE title LIKE '%Bikes%') as 'Bikes',
(select count(1) FROM videos WHERE title LIKE '%Airplanes%') as 'Airplanes',
(select count(1) FROM videos WHERE title LIKE '%Trucks%') as 'Trucks',
(select count(1) FROM videos WHERE title LIKE '%Games%') as 'Games'");
$row = mysql_fetch_assoc($result);
foreach($row as $title => $total)
{
  echo '<li>
<a href="search.php?search='. $title . '&submit= ">'. $title.'&nbsp;&nbsp;'. $total .'</a></li>';
}
echo '<li class="spaceIN"></li><li class="letter">A</li>';

我复制了这个脚本并粘贴了100次，这样做后加载速度真的很慢。

像这个

select sum(title LIKE '%Cars%') as cars,
       sum(title LIKE '%Bikes%') as bikes
from videos

除了其他答案中的SQL建议之外，不如每次有人访问该页面时都运行该查询（假设就是这样），而是将计数存储在数据库中，并让Cron作业在后台定期运行脚本更新它们。然后查询该页面上存储的计数-这显然会大大加快

您可以将select列表中的内联查询替换为布尔表达式上的sum函数：

SELECT SUM (title LIKE '%Cars%') as Cars,
       SUM (title LIKE '%Bikes%') as 'Bikes',
       SUM (title LIKE '%Airplanes%') as 'Airplanes',
       SUM (title LIKE '%Trucks%') as 'Trucks',
       SUM (title LIKE '%Games%') as 'Games'
FROM   videos

添加类别列，int或enum取决于添加/更改类别的频率。您可以使用：

SELECT COUNT(*) as c, category FROM videos GROUP BY category;

那么。定义好类别比在每个查询中都要做字符串更好。此外，开头的"%"非常慢，因为它不能使用索引。