If you have many databases with many write operations, it may be that the table overhead takes a good amount of space. (This is due to the fact that MySQL doesn’t free the space of deleted entries.) With “optimizing” the tables, you can free that space.

Save the following script as /usr/local/sbin/optimizealltables.sh, make it chmod 700 (very important!), then replace YOURPASSWORD with your root MySQL password.

#!/bin/bash
 
MYSQL_LOGIN='-u root --password=YOURPASSWORD'
 
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN  >/dev/null
                echo "done."
        done
done

A bit of SQL that saved my butt lately: Have you ever needed to relate a value from one table to the count of another value in another table? Given you have two tables, one for a timetable, the other for tickets, and you want to find out how many tickets are sold for each tour in the timetable, the following will give it to you (tours.tour_id is related to tickets.ticket_tour_id, who’d have thought of that):

SELECT DISTINCT tour_id, COUNT(ticket_tour_id)
FROM tours
RIGHT JOIN tickets
ON tour_id = ticket_tour_id
GROUP BY ticket_tour_id
ORDER BY departure;