Search for common records in different tables in one query

Question

I am currently using this query, is there any kind of lookup for this query that will work faster.

SELECT SUM(result1), SUM(result2), SUM(result3) FROM ( ( SELECT 0 as result1,0 as result2,COUNT(*) as result3 FROM table1 ) UNION ( SELECT count(*) as result1,0 as result2,0 as result3 FROM table2 ) UNION ( SELECT 0 as result1,count(*) as result2,0 as result3 FROM table3 ) ) as allresult 

Answer 1

An alternative solution to the above request is as follows:

 SELECT (SELECT COUNT(1) FROM table2) AS result1, (SELECT COUNT(1) FROM table3) AS result2, (SELECT COUNT(1) FROM table1) AS result3; 

Answer 2

Add table names to WHERE clause and run the following query:

 SELECT T.Name AS TableName, S.Row_count AS RecordsCount FROM sys.dm_db_partition_stats S INNER JOIN sys.tables T ON T.object_id = S.object_id Where Object_Name(S.Object_Id) IN ('Employees','Country') 

Answer 3

A very simple way to save the load on this request:

Use UNION ALL instead of UNION. UNION ALL will return duplicates if there is any single difference between this and the waht you are using, just UNION, is that UNION removes these duplicates by reducing performance. In other words, it executes UNION ALL, and then returns and deletes duplicate entries.

This should increase the performance of your queries.

Answer 4

(Copying a comment from this answer )

You can get the row count for a table from INFORMATION_SCHEMA as follows (but see the description below):

 SELECT table_rows FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name IN ('table1', 'table2', 'table3'); 

However , the MySQL documentation notes that these values ​​are not accurate for InnoDb tables: "For InnoDB tables, the number of rows is an approximate estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)" If you are using MyISAM, this approach may be sufficient.