CODEWITHSUNDEEP
mysqlsqlselectcountunion
nitish koundade
nitish koundade

Reputation: 801

Find total records in various tables in a single query

Currently I m using this query ,Is there any substitution for this query,which will work more 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

Upvotes: 6

Views: 234

Answers (4)

Ken Keenan
Ken Keenan

Reputation: 10538

(Copying my comment from this answer)

You can get the row counts for a table from the INFORMATION_SCHEMA as follows (but see caveat 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 exact for InnoDb tables: "For InnoDB tables, the row count is only a rough 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.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Alternate solution of above query is as below:

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

Upvotes: 3

Anton
Anton

Reputation: 420

Very simple way to shave some performance load off this query:

Use UNION ALL instead of UNION. UNION ALL will return duplicates if there are any but the only difference between that and waht you are using, just UNION, is that UNION removes these duplicates at the expense of decreased performace. In other words it does a UNION ALL and then goes back and removes the duplicate entries.

It should increase your querys performance

Upvotes: 1

Bala
Bala

Reputation: 704

Add the table names in the WHERE clause and execute the below 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')

Upvotes: 1

Related Questions