How to replace COUNT (*) in innoDB

Navigation
2

I migrated data from a MyISAM database to InnoDB and some VIEW were extremely slow (on average 15x slower), after much research I found this answer from @maniero and it compares the two engines.

In his response I noticed that InnoDB is slow with clause COUNT(*) so I removed COUNT(*) from SELECT within VIEW and here is the result:

310x faster.

The query I am running is this (Decreases to become more readable): 

SELECT DISTINCT
        'a'.'ASSINATURAS_ID' AS 'ASSINATURAS_ID',
        'pd'.'PEDIDOS_DETALHES_Descricao_Produto' AS 'PEDIDOS_DETALHES_Descricao_Produto',
        'pd'.'FK_PRODUTO' AS 'FK_PRODUTO',
        (SELECT 
                COUNT(*)
            FROM
                'licencas'
            WHERE
                ('licencas'.'FK_PEDIDO' = 'p'.'PEDIDOS_ID')) AS 'TotalDownloadSubscriptionCount',
    FROM
        (('n_assinaturas' 'a'
        JOIN 'pedidos' 'p' ON (('p'.'PEDIDOS_ID' = 'a'.'FK_PEDIDO')))
        JOIN 'planos_conta' 'pc' ON (('pc'.'ID_PLANOS_Conta' = 'p'.'FK_PLANOS_Conta')))

Is there an option to COUNT(*) in InnoDB ?

    
asked by anonymous 23.08.2018 / 19:48

1 answer

0

SQL_CALC_FOUND_ROWS ()

It is an internal function of MySQL that calculates the row records of the executed query, even if you set a value for limit the function will calculate the total number of record in the table.

FOUND_ROWS ()

This is the function that stores the number of lines that the SQL_CALC_FOUND_ROWS function executed.

You can find the full explanation in the documentation for MySQL

For your query, you will not get both records at the same time your query would look like this: 

SELECT DISTINCT SQL_CALC_FOUND_ROWS 
       'a'.'ASSINATURAS_ID' AS 'ASSINATURAS_ID'... 
        // restante da query

After executing the query, you need to make a new select with the function FOUND_ROWS() 

SELECT FOUND_ROWS() as total;

Another option would be to create a procedure to perform this count by passing the table name parameter;

23.08.2018 / 20:39
Daily notifications at a specific time set by the user XSD with Schema with restriction "pattern, enumeration, max and min" Java