How to make this query run faster
I would like to know if this query can be faster or how to make it faster, if possible.
$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.' '. $total .'</a></li>';
}
echo '<li class="spaceIN"></li><li class="letter">A</li>';
I make a copy of this script and paste it like 100 times and it is really slow loading after that.
source to share
Along with the SQL suggestions in the other answers - how about this rather than that this query is fired every time someone visits this page (assuming it does) - the accounts are stored in the database instead and the Cron job runs script update them regularly in the background. Then, request your saved account on this page, which will obviously be much faster
source to share
You can replace your inline queries in the list with select
functions sum
above boolean expressions:
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
source to share
Add category column, int or enum, depends on how often you add / change categories. You can use:
SELECT COUNT(*) as c, category FROM videos GROUP BY category;
then. Waaayu better to have specific categories than to do string data on every request. Also the "%" at the beginning is slower since it cannot use indexes.
source to share