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.