How to replace COUNT(*) in innoDB

Question

I migrated data from a bank Myisam for Innodb and some VIEW were extremely slow (on average 15x slower), after much research found that answer from @Maniero and in it he makes a comparison between the two Engines.

In his reply I noticed that Innodb is slow with clause COUNT(*) that’s when I removed the COUNT(*) of SELECT within the VIEW and here is the result:

310x faster.

The query I’m running is this(Decreases to be 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`)))

There is an option to COUNT(*) in the Innodb ?

Answer 1

SQL_CALC_FOUND_ROWS()

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

FOUND_ROWS()

It is the function that stores the quantity of lines that function SQL_CALC_FOUND_ROWS executed.

you can find the full explanation in the documentation of 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 you create a procedure to perform this count by passing the table name by parameter;

Note: my knowledge about procedures are few I tried to set an example but it did not work xD, but I’ll put the idea in case someone wanted to complement:

DELIMITER $$
CREATE PROCEDURE numero_de_registros ( in tabela varchar(200) )
BEGIN
    SELECT SQL_CALC_FOUND_ROWS * FROM tabela limit 1;
    SELECT FOUND_ROWS() as total;
END $$
DELIMITER ;

// para chamar seria algo
call numero_de_registros(tabela);