I have a table with 1 million rows that I need to make a report that groups the repeated names. I'm doing this SELECT instituicao, count(*) from base_wos GROUP BY instituicao; .
However, MySQL always only makes the group of the first 1000 records, not completely sweeping the table.
Increase number of rows returned by select group in MySQL
3
asked by anonymous 13.04.2014 / 01:27
2 answers
13
MySQL "no" has no configuration set by default to limit SELECT queries.
You're probably using MySQL Workbench that automatically places LIMIT 0,1000 on all your SELECTs.
To disregard this setting, simply use a LIMIT greater than 1000 in your query so workbench will read your query and you will see that you already have a defined limit and will not put any LIMIT in your query.
The other option is to remove the Workbench configuration ("SQL queries > Limit rows").
MySQL has a setting called sql_select_limit that is used to limit the SELECTs result even without using LIMIT, the default value of this setting is (2 ^ 32) -1 or (2 ^ 64) -1 , surely no one will display a report with a value greater than those. :)
MySQL Workbench does not change this setting, the program literally concatenates LIMIT 0,1000 in SELECTs if LIMIT is not defined.
13.04.2014 / 21:24
4
The MySQL Workbench has a configuration that by default is set to return the Limit Rows Count = 1000.
To change, already in the workbench and on your server, click Edit > Preferences > SQL Queries tab > In the field 'Query Results', uncheck 'Limit Rows'.
14.04.2014 / 01:36