SELECT COUNT with low performance in mysql

Question

I use a query within PHP to display the list of related categories and subcategories, get back more than 10,000 results (right) in 3 seconds on average, but after I implemented a subquery to check the amount of related subcategories, the query oscillated between 15-17 seconds.

No SUBQUERY (3s on average):

$sql_query = "SELECT t.* FROM `table` t 
WHERE 
    t.`tbl_id` = '".mysqli_fetch_array($dbconn,$id)."' 
ORDER BY 
    (CASE WHEN t.`tbl_nome` = 'Outros' THEN 1 ELSE 0 END) ASC, 
t.`tbl_nome` ASC;

With SUBQUERY (15> on average):

$sql_query = "SELECT t.*,
(SELECT COUNT(n.`tbl_id`) FROM `categoria` n WHERE n.`tbl_pai` = t.`tbl_id` LIMIT 1) 
AS `numSubs` 
FROM 
    `table` t 
WHERE 
    t.`tbl_id` = '".mysqli_fetch_array($dbconn,$id)."' 
ORDER BY 
    (CASE WHEN t.`tbl_nome` = 'Outros' THEN 1 ELSE 0 END) ASC, 
t.`tbl_nome` ASC;

I added COUNT to check for other records in the field tbl_pai has different NULL ID and is the ID of the category to be consulted.

DOUBT:

There is another way to check if other fields have the current category ID in the field tbl_pai 1 only, which has the parent field with the current category ID. ? So optimize the query and stop slowing down?

Answer 1

Your query has inconsistencies (such as LIMIT in the COUNT), but without data for a more elaborate answer I can only suggest the following:

Create an index for your table:

ALTER TABLE categoria ADD INDEX categoria_index (tbl_pai);

More information on the answer to the question Indexes in Mysql queries and What is the INDEX index in Mysql for?.

Answer 2

You can use other strategies, such as building a View by summarizing the number of sub-variables per tbl_parent:

Select tbl_pai, count(*) as qtde from categoria Group by tbl_pai

So, whenever you want, you can make a Join with this view.

In the model you are using, with 10 thousand categories, it will do a lot of processing item by item, I think you can try a code similar to the code below that calculates the amount of items per parent category at once and then Join with this result. (check the code, I’m no environment to test, but it would be something like below)

SELECT 
    t.campo1, t.campo2, t.campo3..., sum(n.qtde) as numSubs  --- como será um agrupamento. adicionar todos 
                                                             --- os campos necessário e depois adicionar no group by
From
    table t

    -- assumindo que todos os itens tem um filho pelo menos
    inner join (
        select  categoria.tbl_pai, 
                count(*) as qtde
        from    
                categoria 
        group by 
                tbl_pai) as n 

                on n.tbl_pai = t.tbl_id

WHERE 
    t.tbl_id = '<<condicao>>' 

GROUP BY 
    t.campo1, t.campo2, t.campo3, t.campo4.... -- adicionar todos os campos necessários

ORDER BY 
        (CASE WHEN t.tbl_nome = 'Outros' THEN 1 ELSE 0 END) ASC, 
        t.tbl_nome ASC;

Remember to list all the 'table' fields that you want to show both at the beginning of select, and at group by.