MySQLi性能，多个(单独)查询与子查询 [英] MySQLi performance, multiple (separate) queries vs subqueries

问题描述

我需要计算来自不同(！)表的行数，并将结果保存为某种统计量.该脚本非常简单并且可以按预期工作，但是我想知道使用单个查询(在这种情况下)有8个子查询是否更好，或者我是否应该使用单独的8个查询，或者是否有更好，更快和更多的查询先进的解决方案...

I need to count the number of rows from different(!) tables and save the results for some kind of statistic. The script is quite simple and working as expected, but I'm wondering if it's better to use a single query with (in this case) 8 subqueries, or if I should use separate 8 queries or if there's even a better, faster and more advanced solution...

我正在使用带有准备好的语句的MySQLi，因此单个查询可能看起来像这样:

I'm using MySQLi with prepared statements, so the single query could look like this:

$sql = 'SELECT
            (SELECT COUNT(cat1_id) FROM `cat1`),
            (SELECT COUNT(cat2_id) FROM `cat2`),
            (SELECT COUNT(cat2_id) FROM `cat2` WHERE `date` >= DATE(NOW())),
            (SELECT COUNT(cat3_id) FROM `cat3`),
            (SELECT COUNT(cat4_id) FROM `cat4`),
            (SELECT COUNT(cat5_id) FROM `cat5`),
            (SELECT COUNT(cat6_id) FROM `cat6`),
            (SELECT COUNT(cat7_id) FROM `cat7`)';

$stmt = $db->prepare($sql);
$stmt->execute();
$stmt->bind_result($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8);
$stmt->fetch();
$stmt->free_result();
$stmt->close();

而单独的查询看起来像这样(x 8):

while the seperate queries would look like this (x 8):

$sql = 'SELECT
            COUNT(cat1_id)
        FROM
            `cat1`';

$stmt = $db->prepare($sql);
$stmt->execute();
$stmt->bind_result($var1);
$stmt->fetch();
$stmt->free_result();
$stmt->close();

因此，与这种查询(例如统计信息，计数器..)相关的速度会更快或更更好"

so, which would be faster or "better style" related to this kind of query (e.g. statistics, counter..)

推荐答案

关于TerryE的示例和使用multi_query(！)的建议，我检查了手册并更改了脚本以适合我的需求..终于找到了解决方案看起来像这样:

Regarding to TerryE's example and the advice to use multi_query(!), I checked the manual and changed the script to fit my needs.. finally I got a solution that looks like this:

$sql  = 'SELECT COUNT(cat1_id) as `cat1` FROM `cat1`;';
$sql .= 'SELECT COUNT(cat2_id) as `cat2` FROM `cat2`;';
$sql .= 'SELECT COUNT(cat2_id) as `cat2_b` FROM `cat2` WHERE `date` >= DATE(NOW());';
$sql .= 'SELECT COUNT(cat3_id) as `cat3` FROM `cat3`;';
$sql .= 'SELECT COUNT(cat4_id) as `cat4` FROM `cat4`;';
$sql .= 'SELECT COUNT(cat5_id) as `cat5` FROM `cat5`;';
$sql .= 'SELECT COUNT(cat6_id) as `cat6` FROM `cat6`;';
$sql .= 'SELECT COUNT(cat7_id) as `cat7` FROM `cat7`;';

if ($db->multi_query($sql))
{ 
    do
    {
        if ($stmt = $db->store_result())
        {
            while ($row = $stmt->fetch_assoc())
            {
                foreach ($row as $key => $value)
                {
                    $count[$key] = $value;
                }
            }
            $stmt->free_result();
        }
    } while ($db->more_results() && $db->next_result());
}

与TerryE的示例有所不同，但是结果是相同的.我知道开头有7行几乎是相同的，但是一旦我需要WHERE子句或其他内容，我就更喜欢这种解决方案，而不是foreach循环，在该循环中我需要手动添加查询或使用异常与if { ... } ...

There are some differences to TerryE's example, but the result is the same. I'm aware that there are 7 line at the beginning that are almost identical, but as soon as I need a WHERE clause or something else, I prefer this solution to a foreach loop where I'd need to add queries manually or use exceptions with if { ... } ...

据我所知，我的解决方案应该没有问题，还是我错过了什么?

As far as I can see, there should be no problem with my solution, or did I miss something?

这篇关于MySQLi性能，多个(单独)查询与子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！