Optimize querie that returns the set of records of multiple tables

Question

I have custom to use the following querie structure to return the amount of table records from my mysql database:

SELECT
  (SELECT COUNT(tab1_codigo) FROM tabela1 where alguma coisa) AS total1,
  (SELECT COUNT(tab2_codigo) FROM tabela2 where alguma coisa) AS total2,
  (SELECT COUNT(tab3_codigo) FROM tabela3 where alguma coisa) AS total3      
FROM tabela4;

"something" refers to some specific filter that I perform within each querie.

Honey works just fine! However, in order to make this querie more optimized, I tried to delimit the amount of records, staying as follows:

SELECT
  (SELECT COUNT(tab1_codigo) FROM tabela1 where alguma coisa limit 50) AS total1,
  (SELECT COUNT(tab2_codigo) FROM tabela2 where alguma coisa limit 50) AS total2,
  (SELECT COUNT(tab3_codigo) FROM tabela3 where alguma coisa limit 50) AS total3      
FROM tabela4 limit 1;

When running the querie, I realized that an error is returned because of the "limit 50" clause. Only the clause "limit 1" (inserted at the end) was accepted without errors. Could someone explain to me a little bit about how this kind of querie structure works? Is it possible for me to use the limit clause within these queries or some more appropriate means of working with this type of structure?

Answer 1

When you wear one COUNT() it goes through all the records within your query.

So you first have to use the LIMIT in research with WHERE and then use the COUNT(), this way you will have the count of records when it is less than the stipulated limit or it will bring the limit.

You can make the query using the LIMIT inside subqueryes as in the EXAMPLE down below:

SQL:

SELECT * from 
  ( select count(*) as total_cores from 
    (
      select cor from `cores` where cor like 'Ág%' limit 20
    ) as A1
  ) as A2
join 
  (select count(*) as total_nomes from 
    (
      select nome from `nomes` where nome like 'A%' limit 20
    ) as B1
  ) as B2;
  

I created an example of tables and how to use the code above in SQL Fiddle

http://sqlfiddle.com/#! 9/f501372/20