从数据库收集统计数据

Question

I have an app that's competition based, and includes (lets say) 10 types of biscuit (cookies) and 6 types of drinks. I'm using MODx Revolution 2.2 as the CMS.

Entrants basically specify which kind of drink they'd like and enter a barcode (relating to the type of biscuit).

I want to present this inside the CMS as a statistic, but I imagine it's quite resource intensive to query the database for each COUNT().

$biscuit1 = $modx->query("SELECT COUNT(*) FROM my_table WHERE barcode = '1234'");
$biscuit1 = $modx->fetch(PDO::FETCH_ASSOC);
$biscuit1 = $biscuit1[0];

So my questions are:

• Is doing 16 database queries (possibly more) bad practice?
• If there is a better way of gathering statistics on datasets, what is it?

Note: I had a thought of creating another statistics table, where I run these queries in a cron job on the hour. Would this help?

Answer 1

COUNT(*) is not all that resource intensive. Also it would help if barcode was indexed. Also you can grab the data for all barcodes in one query rather than having to run 16 queries, one for each

$result = $modx->query("SELECT barcode,COUNT(*) FROM my_table GROUP by barcode");