How to get the name of the table with the biggest number of rows in database

Question

I need to get the name of the table with the biggest number of rows, I can find the biggest number of rows with function count and than function max, but I don't know how to see from which table is that maximum, can anyone help?

Answer 1

Try this query:

SELECT table_name, table_rows
FROM   information_schema.TABLES
ORDER  table_rows;

for all informations, and this for the biggest one:

SELECT table_name, table_rows
FROM information_schema.TABLES WHERE table_rows = 
(SELECT MAX(table_rows) FROM information_schema.TABLES);

php:

$result = mysql_query("SELECT table_name, table_rows
    FROM information_schema.TABLES WHERE table_rows = 
    (SELECT MAX(table_rows) FROM information_schema.TABLES)");

if (!$result) {
    echo "error"
}

while ($row = mysql_fetch_assoc($result)) {
    echo $row['table_name'];
    echo $row['table_rows'];
}

Answer 2

show table status

Will give you the information you need.

Answer 3

$sql = "SHOW TABLES FROM $dbname";
$result = $mysqli->query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

while ($row = mysql_fetch_row($result)) {
    $count =  "select count(*) as cnt from : {$row[0]}\n";
    $result = $mysqli->query($count);
    $row_array=$result->fetch_array(MYSQLI_ASSOC);
    $print[$row[0]] = $row_array['cnt']; 
}
asort($print);

$print will hold the value of all rows count with key as table name. With asort() you can sort it in assending order.

Answer 4

To get it from any database directly, run this query

select TABLE_NAME
FROM information_schema.TABLES
where TABLE_ROWS = (SELECT max(TABLE_ROWS) from information_schema.TABLES);

if you want to run it in php, you cannot just echo the query you have just ran that is why you get the Resource#10 error. check this question

the solution to this is to use "mysql_fetch_assoc" as in Mikhail answer, which actually gives you the correct result: "time_zone_transition117210"

• time_zone_transition is the table name
• 117210 is the number of rows

Since you did not understand that this was your answer, It seems you wanted to limit the query to a certain schema. So your complete answer in php would be as follows

$result = mysql_query(
    "SELECT table_name, table_rows
    FROM information_schema.TABLES 
    WHERE table_rows = (
        SELECT MAX(table_rows) FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA ='YOUR_SCHEMA_NAME'
    )
    AND TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'"
);

while ($row = mysql_fetch_assoc($result)) {
    echo 'table name is: '. $row['table_name'] .' \n ';
    echo 'number of rows is: '. $row['table_rows'] .' \n ';
}

Do not forget to replace YOUR_SCHEMA_NAME with the actual name in both inner and outer queries.