Retrieving top 25 tables (defined by field values)

Question

Hello,

The code below returns the top 25 most recently-created tables in a MySQL database.

$index = mysql_query("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='bookfeather' order by CREATE_TIME desc limit 25");

Each table has the following format:

id INT(11) NOT NULL auto_increment, site VARCHAR(350) NOT NULL, votes_up BIGINT(9) NOT NULL, votes_down BIGINT(9) NOT NULL, PRIMARY KEY(id), UNIQUE (site)

I would like to change the code to show:

1. Top 25 tables based on number of different entries for "site"

2. Top 25 tables based on sum of "votes_up"

How can I do this?

Thanks in advance,

John

Answer 1

1. Top 25 tables based on number of different entries for "site"

IIUC, "site" is UNIQUE, so you should be able to:

select
  TABLE_NAME,
  ifnull(TABLE_ROWS, 0) as SITES
from INFORMATION_SCHEMA.TABLES
  where TABLE_SCHEMA='bookfeather'
order by SITES desc limit 25

But substituting TABLE_ROWS for no. of unique SITE records with a wink and a nod is cheating -- better to redesign. :)

*2. Top 25 tables based on sum of "votes_up"*

If you cannot, for whatever reason, follow the excellent advice to redesign, you'll have to programatically iterate over each table. In your code you might loop over SHOW TABLES, accumulating the result of

select 'tblName', ifnull(sum(VOTES_UP), 0) as UPVOTES from tblName

sorting on the second field of your accumulated rows and pruning to the top 25.

A variant of this involves a summary table which keeps track of tables and sum()'d UPVOTES, perhaps re-written periodically by cron, updated on the fly by per-table triggers, or presented in a VIEW (re-CREATEd for each new table) which is a UNION of all constituent queries.

But don't do any of this. :) Switch to a single-table design (perhaps asking your storage engine to partition, if that's a concern).