Exporting MySQL, PostgreSQL and SQL Server schema structure

Exporting MySQL's schema structure using different IDEs

Exporting PostgreSQL's schema structure

Exporting SQL Server's schema structure

The importance of databases indexes

Creating the right indexes for your database, is the foundation for optimal database and SQL query performance. If you're currently investing all of your efforts in query optimization, you should know that there are more aspects to think about.

EverSQL Query Optimizer's algorithm takes many factors into consideration to calculate the best indexes for your query. Among those factors are the query structure, table sizes, existing indexes and their cardinality, column types and sizes, the connections between the tables and columns in the query and many other factors.

Therefore, when optimizing with EverSQL, we don't only ask for the query to optimize, but also require the schema structure, to provide you with the best query optimization and indexing recommendations.

Export schema structure using MySQLDump

We strongly recommend that you'll provide EverSQL Query Optimizer with a schema structure exported using mysqldump, to an XML format. The reason for this recommendation is because mysqldump's XML structure dump is the only format that includes extra data (such as tables size, indexes cardinality and more) which can be used to provide better indexing and query optimization recommendations.

mysqldump is a database backup command line utility created by Oracle. The utility is provided as part of the MySQL Server package.

For InnoDB engine users, the following command can be used to export the schema structure using mysqldump:

mysqldump --xml --no-data --single-transaction=true -h localhost -u root -pPassword schema_name > path/to/dump/file

If you're using MyISAM, use the following command:

mysqldump --xml --no-data --lock-tables=false -h localhost -u root -pPassword schema_name > path/to/dump/file

Please make sure to change the following parameters to adjust the command to your environment:

  • "localhost" - should be replaced with your MySQL server name.
  • "root" - should be replaced with a MySQL user with permissions to dump the schema structure.
  • "Password" - the MySQL user's password.
  • "schema_name" - The name of your schema.
  • "path/to/dump/file" - The path to export the schema structure to.

Note: performing the steps above will also exports extra meta data to the file (i.e, table sizes and cardinality information). This information is used by the indexing algorithm of EverSQL Query Optimizer to provide the optimal indexing recommendations.

Once you executed the command and exported the schema structure, you're all ready to upload it to EverSQL Query Optimizer.

Export schema structure using phpMyAdmin

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web.

Please follow these steps to export the schema structure using phpMyAdmin:

  1. On the left menu, click your database name.
  2. On the right pane, choose Export from the top menu.
  3. From the Format dropdown, choose XML.
  4. From the Export Method options, choose Custom - display all options
  5. Uncheck Export Contents from the Data dump options section.
  6. Click Go.

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using JetBrains's DataGrip

DataGrip is a unified visual tool for database architects, developers, and DBAs. DataGrip provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. DataGrip is available on Windows, Linux and Mac OS X.

Please follow these steps to export the schema structure using DataGrip:

  1. From the Databases view, choose your database and right-click on Schemas 
  2. Choose "SQL Scripts" and then "SQL Generator"
  3. Under Generate, choose "Creation script completely"
  4. Click on the Save into file option on the right side

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using Sequel Pro

Sequel Pro is a Mac database management application for working with MySQL databases.

Please follow these steps to export the schema structure using Sequel Pro:

  1. From the File menu, choose Export
  2. At the top menu, choose SQL as the export method
  3. Uncheck the option: Content
  4. Uncheck the option: DROP TABLE syntax
  5. Uncheck the option: Output Blog as Hex
  6. Click Export

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

Please follow these steps to export the schema structure using MySQL Workbench:

  1. From the Server menu, choose Data Export
  2. On the left side, choose the database to export.
  3. Choose "Dump structure only" as the dump method.
  4. Uncheck the options: Dump Stored Procedures and Functions, Dump Events, Dump Triggers
  5. Under Export Options, choose Export to Self-Contained File
  6. Click Start Export

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using HeidiSQL

HeidiSQL is a free and open-source administration tool for MySQL and its forks, as well as Microsoft SQL Server and PostgreSQL.

Please follow these steps to export the schema structure using HeidiSQL:

  1. From the Tools menu, choose Export Database as SQL
  2. On the left side, choose the database to export.
  3. Uncheck Create and Drop next to Databases
  4. Uncheck Drop next to Tables
  5. Check Create next to Tables
  6. Choose No Data to extract only the schema structure.
  7. Choose Single SQL file as the output method.
  8. Click Export

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using SQLYog

SQLyog is a GUI tool for the RDBMS MySQL. It is developed by Webyog.

Please follow these steps to export the schema structure using SQLYog:

  1. From the Tools menu, choose Backup Database as SQL dump
  2. At the top pane, choose Export as SQL: structure only
  3. On the left side, choose the database to export.
  4. On the left side, uncheck all Object types except Tables.
  5. Uncheck all options on the right side pane.
  6. Click Export

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export schema structure using DbForge Studio Express

A free mysql client that provides basic functionality for database development. It includes database Object Editor, security configuration tools, advanced Data Editor, and Export/Import from CSV and ODBC

Please follow these steps to export the schema structure using DbForge Studio Express:

  1. From the Database menu, choose Backup and Restore => Backup Database
  2. Click Next to navigate to the Backup Content screen.
  3. Choose the structure option and uncheck the Data option.
  4. Uncheck all Object types except Tables.
  5. Click Backup

You're now ready to upload your schema structure to EverSQL Query Optimizer.

Export PostgreSQL schema structure using pg_dump

pg_dump is a popular utility for backing up a PostgreSQL database.
You can export the schema structure using pg_dump, directly from the PostgreSQL database server, by running the following command:

pg_dump -s DB_SCHEMA_NAME > schema.sql

Please make sure you set the host name, username and database name in the commands above before executing them.

If you have pg_dump installed on your local computer and you'd like to export the schema structure from a remote PostgreSQL database, please use the following command:

pg_dump -s -h HOST_NAME -U USER_NAME -C DB_SCHEMA_NAME > schema.sql

Please make sure you set the arguments in the commands above before executing them.

Once you executed the command and exported the schema structure, you're ready to upload it to EverSQL Query Optimizer.

How can I download pg_dump without installing PostgreSQL?
You can download the binary files for pg_dump (and other PostgreSQL tools) from this page. Please make sure you download the package that is aligned with the PostgreSQL database version you have installed.

Export schema structure using SQL Server Management Studio

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL Server databases. SSMS provides tools to configure, monitor, and administer database instances of SQL Server.

Please follow these steps to export the schema structure using SSMS (images below):

  1. At the left pane, right click the database you would like to export the schema structure for.
  2. Choose Tasks => choose Generate Scripts
  3. Click next at the welcome screen.
  4. On the "Select the database objects to script" screen, choose "Select specific database objects" and choose only "Tables" from the list.
  5. Click advanced => select the option "Types of data to script" and choose "schema only".
  6. Choose the path to export the file to, and click next.
  7. Click Finish.

You're now ready to upload your schema structure to EverSQL Query Optimizer.