优化从select查询中选择的查询

Question

我正试图从我的应用程序中清点所有可疑的存款。

查询基本上是从内部select查询中选择、计数和分组结果。

使用此查询：

SELECT *, count(id) as repeated from (
    SELECT id, userAccount_id, `change_value_amount`,`change_value_currency_code`, JSON_EXTRACT(details, '$.depositId') as depositId From billing.transactions WHERE type_value = 'DEPOSIT_MADE'
) as q1 
where depositId is not null group by depositId having repeated > 1

现在，这个查询的执行时间是4-6秒，这真的很烦人。

我还尝试创建一个临时表，并根据temp表执行外层where子句：

create temporary table if not exists susDeposit as (SELECT id, userAccount_id, `change_value_amount`,`change_value_currency_code`, JSON_EXTRACT(details, '$.depositId') as depositId From billing.transactions WHERE type_value = 'DEPOSIT_MADE');

select id, userAccount_id, change_value_amount, change_value_currency_code, depositId, count(*) as repeated  from susDeposit
where depositId is not null group by depositId having repeated > 1

在工作台中，它以最大1秒的速度执行。

但是使用pdo时，它在5秒时执行，并且询问工作台和pdo中执行时间不同的原因。

守则是：

$tempTable = "CREATE TEMPORARY TABLE IF NOT EXISTS susDeposit as (SELECT id, userAccount_id, `change_value_amount`,`change_value_currency_code`, JSON_EXTRACT(details, '$.depositId') as depositId From billing.transactions WHERE type_value = 'DEPOSIT_MADE');";
        $finalSelect = "SELECT id, userAccount_id, change_value_amount, change_value_currency_code, depositId, count(*) as repeated  from susDeposit
        where depositId is not null group by depositId having repeated > 1";

        $query = $this->pdo->query($tempTable);
        $query = $this->pdo->query($finalSelect);
        $query->execute();
        $r = $query->fetch();

有任何方法来优化这个查询吗？

Answer 1

首先，不要隐藏您需要在JSON字符串中测试的列。

也就是说，在构建表时，将depositId放在自己的列中，以帮助优化器执行查询。

一旦您完成了这些更改并提供了SHOW CREATE TABLE，我将帮助您创建一个合适的复合索引(如果需要的话)。