MySQL – Change the Limit of Row Retrieved in MySQL Workbench

January 12, 2014

MySQL Workbench is default IDE for MySQL. It can be installed on Windows, Linux or Mac OSX. Here is the one question which new users of MySQL Workbench often ask –

“When I execute my SQL Query from my application, it retrieves correct numbers of the row, but when I execute the same query in MySQL Workbench it retrieves only 1000 records. What is the reason behind it?”

This is a very valid question and here is the answer –

MySQL Workbench by defaults limit the numbers of rows any query can retrieve. The initial default value is set to 1000. That means it does not matter how many records your query is retrieving it will only record a maximum of 1000 rows. This limit is implemented for two major reasons. 1) It prevents accidents where users have not written, WHERE clause and execute query which retrieves all the rows from the table. 2) Let us assume there is a query which has multiple tables and it is retrieving thousands of the rows, when it will execute it may quite possibly hang the entire MySQL Workbench till the query has completed. To avoid a situation like this where your resources of Server or Client are blocked by unwanted queries; MySQL Workbench has limited the number of rows to be retrieved by any single query to 1000.

You can easily change this limit by going to MySQL Workbench >> Edit >> Preferences >> SQL Queries tab. Over here you will option to Limit Rows. You can set this to very high value or uncheck the option. When you uncheck that option, it will retrieve all the rows from a query (equivalent to no limits).