I had run into a similar situation once when I had to try out Apache Solr at home to see how it indexes and makes the searches faster, I always felt that the data was not enough to see the actual performance of the solr indexing.
I was so happy to read yesterday about the Open World Database alpha which provides a wide range of data for this purpose licensed under the GNU GPL v3 license.
The Open World Database alpha comes as a very light distribution (less than 4mb file [worlddb.7z]) and can be downloaded from here. This distribution contains a sql file targetted for the mysql database. This script file contains the ddls (table definitions) and dmls (the data to be inserted for each of these tables).
To import this data into a MySQL database, execute the following steps -
Pre-requisites:
MySQL database server need to be downloaded and installed.
You can skip these steps if you already have MySQL downloaded and installed in your machine.
- Download the latest distribution [mysql-5.5.28-winx64.zip] of MySQL from here. You can use any version of MySQL for this exercise.
- Extract the distribution to some folder as shown below
In a command prompt, navigate to the bin folder of the installed MySQL distribution. In order to start the MySQL server, we need to type the following command and hit enter. This will start the server and keep it running until you close the command prompt.
mysqld
Step 2: Connect to MySQL server
In another command prompt, navigate to the bin folder of the installed MySQL distribution. With the MySQL server running, type the following command as shown below. Here we are trying to connect using the default root credentials. When prompted for a password just hit enter.
mysql -u root -p
Step 3: Create a new MySQL database
Once all of the pre-requisites are completed, we can now go ahead and create a database to store the Open World Database alpha
From the mysql prompt, you can type the following command as shown below:
create database worlddb;
Step 4: Import the SQL file
For importing the SQL file to the newly created worlddb, we need to connect to the worlddb database. For this we need to run the following command
use worlddb;
Next step is to import the Open World Database alpha sql file into the worlddb. For this we need to run the following command. I had extracted the Open World Database alpha distribution to a folder on my disk. See the below picture for more clarity.
source <<path to worlddb.sql>>
Once you hit enter, you can see the tables getting created and the data getting inserted into the database. We need to wait rougly around 30 minutes for the load to get completed. Again it depends on your machine resources as well.
Step 5: Verify the tables and data
Once the data is loaded, we can verify the data typing the following script. This will give all the tables and the number of records in each table. With each distribution of the Open World Database, the number of records or table structure is subject to change.
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'worlddb';
To summarize, we learned how to create a MySQL database and load the Open World Database data into it. You can follow the website to Open World Database alpha by clicking here. Stay tuned for new posts that will basically use the data imported as part of this post.
mysql -u root -p
Step 3: Create a new MySQL database
Once all of the pre-requisites are completed, we can now go ahead and create a database to store the Open World Database alpha
From the mysql prompt, you can type the following command as shown below:
create database worlddb;
Step 4: Import the SQL file
For importing the SQL file to the newly created worlddb, we need to connect to the worlddb database. For this we need to run the following command
use worlddb;
Next step is to import the Open World Database alpha sql file into the worlddb. For this we need to run the following command. I had extracted the Open World Database alpha distribution to a folder on my disk. See the below picture for more clarity.
source <<path to worlddb.sql>>
Once you hit enter, you can see the tables getting created and the data getting inserted into the database. We need to wait rougly around 30 minutes for the load to get completed. Again it depends on your machine resources as well.
Step 5: Verify the tables and data
Once the data is loaded, we can verify the data typing the following script. This will give all the tables and the number of records in each table. With each distribution of the Open World Database, the number of records or table structure is subject to change.
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'worlddb';
To summarize, we learned how to create a MySQL database and load the Open World Database data into it. You can follow the website to Open World Database alpha by clicking here. Stay tuned for new posts that will basically use the data imported as part of this post.
