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.

MySQL Control Center

The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server. It uses Trolltech's QT toolkit and can be compiled to run on a variety of platforms including Windows and Unix variants.

(Please refer to the MySQL Control Center Returns 1000 Rows article if you are searching for help about how to change the default behaviour of returning only 1000 records).

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. I will create a review of these products at some point in the coming weeks.

I have mainly been using phpMyAdmin to administer databases on the web and in development on my laptop, but after doing a lot of recent work with Microsoft SQL Server which has a nice easy to use GUI, was starting to find a web based interface somewhat cumbersome. After a quick search on Google I discovered MySQL have actually created their own cross-platform GUI tool which is very nice to use.

The Control Center makes it easy to administer MySQL when you are either running MySQL on your local computer or when it can be connected to remotely on a network (generally a website database won't have a port open to the world so you'll need to use a tool like phpMyAdmin instead, as you most likely won't be able to connect to it with the Control Center).

There are many benefits in using the MySQL Control Center over a web based tool like phpMyAdmin: it supports syntax highlighting, so that table names, column names and sql keywords are highlighted; it's a lot faster; you don't need a webserver installed; you can save the query you are currently working on as a text file; you can select one query among several within the query textbox and just run that. The list of advantages goes on. I only wish I had started using this tool sooner!

First up (after installing it, of course), you need to register a server. The screenshot below shows editing a server's properties. Note that these screenshots are from the control center running on my Linux/KDE laptop. You can select from a variety of themes to make it look just like you want (I chose the liquid theme, as that's how my other KDE apps look)

Registering a server in the MySQL Control Center

You simply enter the MySQL server's hostname, your login details and away you go. There are other configuration options available but to get started that's really all you need.

The MySQL Control Center Manager

This screenshot shows the list of databases installed, and when you open up a database branch you can see all the tables that belong to it. Selecting a table shows the fields and assocated information in right pane.

Running a query with the MySQL Control Center Manager

Clicking a table or running a custom query in the query window will look similar to the above screenshot. You can hit the "F2" key to type in your own query which will be syntax highlighted. The right pane has a list of columns that were returned by the query; simply tick or untick the column names to add or remove them from the result set. To sort the result set in a different order, there's no need to run the query again specifying the order; simply click the column header and it will sort the cached data in that order. Note that by default the MySQL Control Center returns a maximum of 1000 rows at a time, and that reordering will only reorder the 1000 cached records. (For more information about how to change this default, or return more than 1000 records in a one-off query, refer to the MySQL Control Center Returns 1000 Rows article).

Editing a table's structure using the MySQL Control Center

It's easy to modify the structure of a table using the MySQL Control Center. The screenshot above shows the window for adding and modifying fields and indexes etc.