MySQL Control Center Returns 1000 Rows

The MySQL Control Center is a cross platform GUI for connecting to a MySQL database server and running database queries. By default the MySQL Control Center is configured to return 1000 records when you run a select query. This is designed to prevent returning huge datasets that consume large amounts of bandwidth.

Although only returning 1000 rows can sometimes be too limiting, it can be useful to prevent accidentally returning far too much data, something which I have been doing far too much recently with Microsoft SQL Server. There are two ways you can change this default behaviour in the MySQL Control Center.

Use MySQL LIMIT syntax

The first solution is to write your database query using MySQL limit syntax to control the first and number of records returned from your select query. So instead of running eg:

SELECT * FROM sometable WHERE somefield LIKE '%foo%'

which would by default only return 1000 rows, you could write it like one of the following query examples to return the first 5000 records:

SELECT * FROM sometable WHERE somefield LIKE '%foo%' LIMIT 5000
SELECT * FROM sometable WHERE somefield LIKE '%foo%' LIMIT 0, 5000

Of course you could also run a query like the following to return the second 1000 records:

SELECT * FROM sometable WHERE somefield LIKE '%foo%' LIMIT 1000, 1000

This is easy enough to do, but not very convenient if you frequently need to return more than 1000 records when running a query. Fortunately this default is just a setting which you can change on a server by server basis.

Change the default Control Center settings

When registering a server with the MySQL Control Center a dialog like the one below allows you to enter a name for the server, its hostname and login details. There is a second tab labelled "MySQL Options" where the number of rows returned by a query can be specified. This is circled in the second screenshot below in red.

 

Note that by default the number of rows returned is 1000, as shown in the example above. This can be set to 0, which will always return all rows returned by a SQL query, or a specific number. If it is set to 10,000 for example, then the maximum number of records returned by a query will be 10,000. If there are any less than this number then only those matching the select query will be returned.

After a database has already been registered you can still change the default (or your modified) setting by editing the server registration preferences at a later time. Open up the MySQL Control Center and right-click the server name in the list of MySQL servers. A menu will pop open giving you the options to disconnect, edit, delete, rename or create a new window. Select the edit option and you will get to the server registration properties window as illustrated earlier. An example of doing this is shown below. The popup menu is circled in red.

Please note that MySQL have ceased development work on the MySQL Control Center to concentrate on development of the MySQL Administrator and MySQL Query Browser.