Query is very slow, can i combine or optimized this-CodePudding

Good day,

I have a query that takes a long time and I can't figure out how to optimize it.

SELECT 
a.*,
(SELECT 
        COUNT(*)
    FROM
       sp_produkte
    WHERE
        bemerkung = '9' AND prodId = a.id) as count
FROM
   produktions_liste a
WHERE
    sp_id = 1
        AND DATE(endzeit) = CURDATE() - 11
ORDER BY id DESC
LIMIT 20;

This query only checks how often something with the same prodId was posted in another table. Is it possible to pre-select this data for the same day in order not to have to search through the entire table in each row?

Table produktions_liste

id	prodId	somestuff
1	1	***
2	2	***
3	3	***
4	4	***

Table sp_produkte

id	prodId
1	1
2	1
3	2
4	3

What I would like

id	prodId	somestuff	count
1	1	***	2
2	2	***	1
3	3	***	1
4	4	***	0

CodePudding user response：

First, I would rephrase the predicate:

AND DATE(endzeit) = CURDATE() - 11

to:

AND endzeit >= CURDATE() - 11 and endzeit < CURDATE() - 10

This will allow the usage of the index:

create index ix1 on produktions_liste (sp_id, endzeit);

Second, I would consider to add an index for the scalar subquery in the select list. This one could take the form:

create index ix2 on sp_produkte (bemerkung, prodId);

This second index will make scans much faster on the secondary table.

Try both changes (together or separately). If the query is still slow, please post the execution plan.