高效查询只返回行数

Question

I have five different queries running on my about page showing basic data like the number of news stories we have on the site. I am using queries like this:

$sql4 = "SELECT `ride_id` FROM `tpf_rides` WHERE `type` LIKE '%Roller Coaster%'" ;
$result4 = $pdo->query($sql4);
$coasters = $result4->rowCount();

but wonder if there is a more efficient way. I've tried to minimize the load by only pulling id's but because I only need the count can the load be lightened even more?

Also these queries only really need to run once or twice per day, not every time the page is loaded. Can someone point me in the direction of setting this up? I've never had to do this before. Thanks.

Answer 1

Yes there is a more efficient way. Let the database do the counting for you:

SELECT count(*) as cnt
FROM `tpf_rides`
WHERE `type` LIKE '%Roller Coaster%';

If all the counts you are looking for are from the tpf_rides table, then you can do them in one query:

SELECT sum(`type` LIKE '%Roller Coaster%') as RollerCoaster,
       sum(`type` LIKE '%Haunted House%') as HauntedHouse,
       sum(`type` LIKE '%Ferris Wheel%') as FerrisWheel
FROM `tpf_rides`;

That would be even faster than running three different queries.