rand(Ø)

> beautiful code & photos

about <

Posts tagged: mysql

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

Architecture LAMP sur cluster HA actif-passif

Pour cette nouvelle année 2012, je vais commencer gros avec un article qui traine depuis des mois maintenant. En fait, ce sera un gros clin d’œil à la (seule) période réellement passionnante de cette seconde année de Master Ingénierie Informatique : la mise en œuvre d'une architecture HA (High Availability).

De plus, ayant échoué (de peu) à ce projet durant ma formation malgré l'investissement personnel, j'ai souhaité le refaire par moi-même car cela m'a passionné et plus tard, j'espère que j'aurais l'occasion de faire plus que configurer des vhosts !

Accès rapide

LinuxHA-Logo

Au vu de la taille du sous-menu, cet article

> Continue Reading