Archive for the ‘mysql’ Category

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

October 24, 2017 Leave a comment

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.

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’“).

Categories: mysql Tags: ,

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'

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;
Categories: mysql Tags: ,

Multiple inserts with MySQL/PHP

January 13, 2011 Leave a comment


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:

 ('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.

Categories: mysql, php Tags: , ,

MySQL timestamp to Unix timestamp [PHP]

December 6, 2010 1 comment


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.


// 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


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


I found this solution here.

Categories: mysql, php Tags: , ,

Foreign keys with phpMyAdmin [MySQL]

November 25, 2010 Leave a comment


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


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
Categories: mysql Tags: ,