June 11th, 2008 at 11:03 am

Shell script to optimize all tables in all databases (MySQL)

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.

PLAIN TEXT
CODE:
  1. #!/bin/bash
  2.  
  3. MYSQL_LOGIN='-u root --password=YOURPASSWORD'
  4.  
  5. for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
  6. do
  7.         TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
  8.         echo "Switching to database $db"
  9.         for table in $TABLES
  10.         do
  11.                 echo -n " * Optimizing table $table ... "
  12.                 echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN >/dev/null
  13.                 echo "done."
  14.         done
  15. done

Comments: 0 Categories: Codebits Tags: , ,

December 15th, 2007 at 5:26 pm

Count over two SQL tables

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):

PLAIN TEXT
MySQL:
  1. SELECT DISTINCT tour_id, COUNT(ticket_tour_id)
  2. FROM tours
  3. RIGHT JOIN tickets
  4. ON tour_id = ticket_tour_id
  5. GROUP BY ticket_tour_id
  6. ORDER BY departure;

Comments: 0 Categories: Codebits Tags: ,