rand(Ø)

> beautiful code & photos

about <

Posts tagged: sql

MySQL: Table count for all databases

Answers I found on the internet were all based on the table information_schema.tables but truth is, this table contains table schemas of databases, not the database list.

What happens when you also need to include databases with no table?
Answer with this query:

SELECT SCHEMA_NAME AS 'Database', COUNT(information_schema.tables.TABLE_SCHEMA) AS 'Table Count'  
FROM information_schema.SCHEMATA  
LEFT JOIN information_schema.tables ON information_schema.tables.TABLE_SCHEMA = information_schema.SCHEMATA.SCHEMA_NAME  
WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'tmp')  
GROUP BY SCHEMA_NAME;  
+----------+-------------+
| Database | Table Count |
+----------+
> Continue Reading

MySQL: adding an index on a big-ass table

Okay, I'll admit it: SQL and big data things aren't at the top of my skillset.

No earlier than today, I resolved an issue we had in our customer databases: adding a missing index.
Seems easy at first but if the table is HUGE (yea, 8GB+ is big for me) with about 246MM rows (!!), it gets damn slow and the downtime doesn't last for an eye blink.

Reminder: ALTER TABLE allows read operations while altering the table then blocks reads and writes ops when the alteration is done and needs to go live. This may vary according to the configuration

> Continue Reading