[mysql] rows are deleted but the database still has the same size

Problem
Our cache grew too big, so we wanted to remove rows that are older than X days. However, after removing the majority of the rows, the database still had the same size.

Solution
I found the solution here. Use the command “OPTIMIZE TABLE <tablename>;“. You can also do that with the graphical interface of phpMyAdmin (“tick the checkbox next to the table name you want to decrease, and in the ‘With selected’ drop-down under the list of tables, choose ‘Optimize’“).

Some notes on MySQL

Log in to MySQL as administrator:

mysql -u root -p

Show databases:

mysql> show databases;

Show tables in the database mysql:

mysql> use mysql;
mysql> show tables;

Administration program for the mysqld daemon: mysqladmin.

Accessing phpmyadmin: http://localhost/phpmyadmin.

Another GUI administration program: mysql-admin (note the ‘-‘).

Create a database called “finance”:

$ mysqladmin -u root -p create finance

Create a user who can work on the previously created finance database:

mysql> GRANT select, insert, update, delete, create, drop, index, alter,
create temporary tables, lock tables
ON finance.*
TO 'myuser'@'localhost'
IDENTIFIED BY 'mypass';

Now we have a user called “myuser” with the password “mypass” who can work on the “finance” database from localhost only.

Log in with the newly created user:

$ mysql -u myuser -p
mysql> use finance;
mysql> show tables;

Multiple inserts with MySQL/PHP

Problem

You want to insert multiple records in a MySQL table (in a loop, for instance). How to improve the performance?

Solution #1

One way is to regroup all the inserts in one SQL command:

INSERT INTO x (a,b)
VALUES
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

Credits go here.

Solution #2

Another way is to regroup the inserts in a transaction:

mysql_query("START TRANSACTION");
mysql_query("INSERT ...");
mysql_query("INSERT ...");
mysql_query("INSERT ...");
mysql_query("COMMIT");   // or "ROLLBACK" if you changed your mind

Credits go here.

MySQL timestamp to Unix timestamp [PHP]

Problem

In MySQL, you have a field with timestamp type. Timestamp is used to track the elapsed time since ‘1970-01-01 00:00:00 UTC’. However, if you read it from the database, you get a date (2010-12-06 18:27:07) instead of its integer equivalent (1291678027). How to convert the date format to Unix timestamp?

Use case: I created a table with cached values. In each record, I have a “last_update” field of type timestamp. In my PHP script, I compare the current time with this last_update field. If the difference is more than X minutes, then I need to update the cache table. For calculating the time difference, first I convert the MySQL timestamp to Unix timestamp.

Solution

// from MySQL to UNIX timestamp
function convert_datetime($str)
{
    list($date, $time) = explode(' ', $str);
    list($year, $month, $day) = explode('-', $date);
    list($hour, $minute, $second) = explode(':', $time);

    $timestamp = mktime($hour, $minute, $second, $month, $day, $year);

    return $timestamp;	// return type: int
}

Usage:

$time_from_db = convert_datetime($tmp);
$time_now = time();
$time_diff = $time_now - $time_from_db;

Credits

I found this solution here.

Foreign keys with phpMyAdmin [MySQL]

Problem

You are using MySQL and you want to set foreign keys with phpMyAdmin.

Solution

By default, phpMyAdmin uses the storage engine MyISAM. If you want foreign keys, you must use the engine InnoDB instead. Steps:

• choose the table you want to modify
• choose the tab Operations
• here, change the Storage Engine to “InnoDB” (do this with all the tables in the current database)
• choose the tab Structure, then follow the link Relation view
• now you can set the foreign keys and decide what to do on delete/update