3
I have a table with 1 million lines that I need to do a report that groups the repeated names. I am doing so SELECT instituicao, count(*) from base_wos GROUP BY instituicao;.
However always the Mysql makes only the group of the first 1000 records, not completely scanning the table.
13
Mysql "no" has no default settings to limit SELECT queries.
You’re probably using it Mysql Workbench which automatically puts LIMIT 0.1000 in all your Selects (Default settings).
To disregard this setting just use one LIMIT larger than 1000 in your query so Workbench will read your query and see that you already have a limit defined and will not put any limit in your query.
The other option is to remove the Workbench setting ("SQL queries > Limit Rows").
Mysql has a configuration called sql_select_limit used to limit the output of Selects even without using LIMIT, the default value of this setting is (2 32)-1 or (2 64)-1, for sure no one will display a report with a value greater than these. :)
Mysql Workbench does not change this setting, the program literally concatenates LIMIT 0.1000 in the Selects if LIMIT is not set.
4
Mysql Workbench has a setting that by default is set to return Limit Rows Count = 1000.
To change, on Workbench and on your server, click Edit > Preferences > SQL Queries Tab > In the 'Query Results' fieldset, uncheck the 'Limit Rows' option'.
Browser other questions tagged mysql database
You are not signed in. Login or sign up in order to post.
Spinning here...
– Igoto
Yeah, that’s what I meant to say.
– Igoto
@Igob, are you using any UI? Mysql does not limit the query, who puts are some access interfaces to it. This can usually be solved with confusion. Your limit test worked simply by going over the original limit.
– Bacco
To make it clear, Igob: Are you using Mysql Workbench, and if so, does this limit problem occur only in Mysql Workbench? What other tools you used and in which of them the problem occurred?
– user7835
I used Mysql Workbench. I only had to set the value on it.
– Igoto