MySQL Sample Databases

I saw a post on the forums.mysql.com site about the sample databases and I thought it might be worth a post to give a quick overview to them for others.

The Sample databases can be found here:  http://dev.mysql.com/doc/index-other.html

• employee data Launchpad
• world database  Gzip | Zip 
• sakila database TGZ | Zip

You can load these databases via the MySQL command line:


$ tar -vxf sakila-db.tar.gz
$cd sakila-db
$ mysql -u root -p < sakila-schema.sql
Enter password:
$ mysql -u root -p < sakila-data.sql
Enter password:

$ gzip -d world_innodb.sql.gz
$ mysql -u root -p -e "DROP SCHEMA IF EXISTS world";
Enter password:
$ mysql -u root -p -e "CREATE SCHEMA world";
Enter password:
$ mysql -u root -p world < world_innodb.sql
Enter password:


You get the idea. Sakila example database has the Drop SCHEMA and CREATE SCHEMA commands in the file so no need to do that step for that SCHEMA.

You can also use MySQL Workbench to load this data.

• Create a connection handle to you database. 
• Use this newly created connection handle to set up a Server Administration instance. 
• Double click on your new instance.
• Under Data Export / Restore you should see a Data import.
• Import from a Self-Contained File
• File path will be the location of your sakila-schema.sql then repeat for sakila-data.sql
• You can select a schema or create a new one in the case of world.
• Select start import and then you will be on the Import Progress view.

You now have access to the sample databases in your database. 

$ mysql -u root -p

> use sakila

> show tables;

> select * from actor limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Via workbench :

• Close the admin tab
• Select your connection handle under the SQL Development
• You can either just type in select * from actor limit 3; and hit the lighting bolt.
• Or you can type parts of the command and double click on table name or column names to have it populate the names for you. Then select the lighting bolt. 

Now you have data to start playing and learning with.

If you want to add tables to this you can use the MySQL command line or SQL Development and right click on "Tables" under the Schema of your choice and "create new table"

Check in on your status variables in MySQL.

So you have your database running as well as expected.
But is it ? Could it be operating better?

When is the last time you checked on some of your status variables ?

Some key status variables to monitor are:

• Sort_merge_passes
• I monitor clients systems. This is one variable that I see a vast range of values for. 
• If you see a high value for this status you should seriously consider looking into adjusting the sort_buffer_size. Peter's blog post can be a helpful start in evaluating your options. 
• http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size
• sort_buffer_size
• A valid value takes some review and testing. Adjustments can help your performance with order by and group by result sets. 
• Again review Peter's post to help you evaluate. 
• http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size
• Select_scan
• "The number of joins that did a full scan of the first table" 
• Do you really want full scans?
• Handler_read_first
• "If this value is high, it suggests that the server is doing a lot of full index scans;"
• again do you want full scans? 
• Key_reads
• "If Key_reads is large, then your key_buffer_size value is probably too small.
• Select_full_join
• "If this value is not 0, you should carefully check the indexes of your tables."

So to put it simply.... check your status !

MySQL Distributions Survey

I have created this general MySQL Distributions Survey. The results will be available at the end of the survey. All questions are required ( only 4 questions ) I have tried to target each survey to the language that this blog is presented in.
Results are not going to any of the MySQL Distributions but here for public viewing.

Please take the survey here:
http://www.surveymonkey.com/s/KRJFK5F

Export CSV directly from MySQL

First another blog post about this is here:

• http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/

 But since I saw this posted on the forums.mysql.com I thought I would give a little longer example.

So for this example I am using the World database. It is available free to download here:

• http://dev.mysql.com/doc/index-other.html


mysql>desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+



mysql> SELECT ID, Name, CountryCode , District , Population
 INTO OUTFILE '/tmp/City_data.csv'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 ESCAPED BY '\\'
 LINES TERMINATED BY '\n'
 FROM City ;
Query OK, 4079 rows affected (0.04 sec)



# more /tmp/City_data.csv
1,"Kabul","AFG","Kabol",1780000
2,"Qandahar","AFG","Qandahar",237500
3,"Herat","AFG","Herat",186800
4,"Mazar-e-Sharif","AFG","Balkh",127800
5,"Amsterdam","NLD","Noord-Holland",731200
6,"Rotterdam","NLD","Zuid-Holland",593321
7,"Haag","NLD","Zuid-Holland",440900
8,"Utrecht","NLD","Utrecht",234323
9,"Eindhoven","NLD","Noord-Brabant",201843


UPDATE OPTIONS with LIKE REGEXP SUBSTRING and LOCATE

A recent forum post made me stop and think for a moment..
http://forums.mysql.com/read.php?10,589573,589573#msg-589573

The problem was the user wanted to update just the word audi and not the word auditor.
It was solved by taking advantage of the period easily once I stopped trying to use SUBSTRING and LOCATE.  They wanted a quick and easy fix after all.


root@localhost [test]> CREATE TABLE `forumpost` (
    ->   `name` varchar(255) DEFAULT NULL
    -> ) ENGINE=InnoDB;

root@localhost [test]> insert into forumpost value ('An auditor drives an audi.'),('An auditor drives a volvo.');

root@localhost [test]> select * from forumpost;
+----------------------------+
| name                       |
+----------------------------+
| An auditor drives an audi. |
| An auditor drives a volvo. |
+----------------------------+


So now let us update it the quick and easy way by taking advantage of the period


root@localhost [test]>UPDATE forumpost SET name = REPLACE(name, 'audi.', 'toyota.') WHERE name LIKE '%audi.';
Query OK, 1 row affected (0.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [test]> select * from forumpost;
+------------------------------+
| name                         |
+------------------------------+
| An auditor drives an toyota. |
| An auditor drives a volvo.   |
+------------------------------+


But... what about the valid options of SUBSTRING and LOCATE.....


root@localhost [test]> insert into forumpost value ('An auditor drives an audi.');
root@localhost [test]> insert into forumpost value ('An auditor drives an audi car');
root@localhost [test]> select * from forumpost;
+-------------------------------+
| name                          |
+-------------------------------+
| An auditor drives an toyota.  |
| An auditor drives a volvo.    |
| An auditor drives an audi.    |
| An auditor drives an audi car |
+-------------------------------+


First test your options so you make sure you can find what you are after..


root@localhost [test]> SELECT * FROM forumpost WHERE name REGEXP 'audi car$';
+-------------------------------+
| name                          |
+-------------------------------+
| An auditor drives an audi car |
+-------------------------------+


root@localhost [test]> SELECT * FROM forumpost WHERE name LIKE '%audi car%';

+-------------------------------+

| name                          |

+-------------------------------+

| An auditor drives an audi car |

+-------------------------------+

That really did not do too much since we just changed the period for the word car.  So keep going....

We need to pull just the word audi from the line with audi car.


root@localhost [test]> SELECT SUBSTRING(name,-8,4), name FROM forumpost WHERE SUBSTRING(name,-8,4) = 'audi';
+----------------------+-------------------------------+
| SUBSTRING(name,-8,4) | name                          |
+----------------------+-------------------------------+
| audi                 | An auditor drives an audi car |
+----------------------+-------------------------------+


The SUBSTRING allowed me to pull the first 4 characters after I counted back 8 characters from the end.

So what if you do not know the location of the characters?
To start with you should review you data to make sure you know what you are after. But the characters might move around your string so lets work with LOCATE.

I will add another row just for tests.


root@localhost [test]>  insert into forumpost value ('An auditor drives an red audi car');
Query OK, 1 row affected (0.04 sec)

root@localhost [test]> select * from forumpost;
+------------------------------------+
| name                               |
+------------------------------------+
| An auditor drives an toyota.       |
| An auditor drives a volvo.         |
| An auditor drives an audi.         |
| An auditor drives an audi car      |
| An auditor drives an audi blue car |
| An auditor drives an red audi car  |
+------------------------------------+


So regardless of the ending we can see that audi always after auditor so we just need to skip over that word. The word auditor is in the first 8 character so skip those.


root@localhost [test]> SELECT LOCATE('audi', name,8), name FROM forumpost WHERE LOCATE('audi', name,8) > 0 ;
+------------------------+------------------------------------+
| LOCATE('audi', name,8) | name                               |
+------------------------+------------------------------------+
|                     22 | An auditor drives an audi.         |
|                     22 | An auditor drives an audi car      |
|                     22 | An auditor drives an audi blue car |
|                     26 | An auditor drives an red audi car  |
+------------------------+------------------------------------+


OK so we found the ones we are after. Now we need to write the update statement.

We cannot use the replace this time.


 UPDATE forumpost SET name = REPLACE(name, LOCATE('audi', name,8), 'mercedes') WHERE LOCATE('audi', name,8) > 0 ;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 4  Changed: 0  Warnings: 0


Notice it found the rows but did not change anything.

So try again and I do not want to assume the location 8. I want the 2nd value of audi.
So  a test shows that with SUBSTRING_INDEX I can skip the 1st one and USE CONCAT


SELECT name , CONCAT ( SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )  as newvalue
FROM forumpost
WHERE LOCATE('audi', name,10) > 0 ;
+-----------------------------------+-----------------------------------------+
| name                              | newvalue                                |
+-----------------------------------+-----------------------------------------+
| An auditor drives an audi.        | An auditor drives an  mercedes .        |
| An auditor drives an audi.        | An auditor drives an  mercedes .        |
| An auditor drives an audi car     | An auditor drives an  mercedes  car     |
| An auditor drives an red audi car | An auditor drives an red  mercedes  car |
+-----------------------------------+-----------------------------------------+

root@localhost [test]> UPDATE forumpost SET name = CONCAT(SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )
WHERE LOCATE('audi', name,10) > 0 ;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@localhost [test]> select * from forumpost;
+-----------------------------------------+
| name                                    |
+-----------------------------------------+
| An auditor drives an  mercedes .        |
| An auditor drives a volvo.              |
| An auditor drives an  mercedes .        |
| An auditor drives an  mercedes  car     |
| An auditor drives an red  mercedes  car |
+-----------------------------------------+
5 rows in set (0.00 sec)


Now, granted the grammer with the use of "an" is invalid but that is another story.

More information on these can be found here:

• PATTERN MATCHING
• LIKE OPERATOR
• REGEXP OPERATOR
• SUBSTRING FUNCTION
• SUBSTRING_INDEX FUNCTION
• LOCATE FUNCTION