MySQL/InnoDB result set limited to 1000 records?

Question

I'm loading a RDB with dummy data to practice query optimization. MySQL Workbench executed 10,000 INSERTs without returning an error into my customers table. Yet, when I SELECT * from that table I am only getting back exactly 1000 records in the result set. I am using InnoDB as my table engine

According to this link I should have unlimited records available and a 64TB overall sizelimit.. Im inserting 10,000 records with 4 VARCHAR(255)columns and 2 BOOLEAN columns each and I don't think that tops 1 TB. Am I wrong in this assumption?

Is the result grid limited to 1000 records? Is there an alternative to InnoDB which supports foreign keys? Is the problem that VARCHAR(255) is way too large and I need to reduce to something like VARCHAR(50)? What am I not understanding.

THANK YOU IN ADVANCE

MySQL Workbench is imposing a 1,000 row limit. See https://superuser.com/questions/240291/how-to-remove-1000-row-limit-in-mysql-workbench-queries — Bill Karwin, Jul 27 '19 at 05:30

score 0 · Answer 1 · answered Jul 26 '19 at 23:31

No, it is not limited to 1000 records. I have InnoDB complex tables of more than 50 million records with blobs and multiple indexes. InnoDB is perfectly fine, you don't have to look for another engine. Could you be more precise about the context where you executed the query? Was it from a programming language? command line mysql client? Another Mysql client?

score 0 · Answer 2 · answered Jul 27 '19 at 00:05

0

Many database query tools limit the number of rows returned. Try selecting some data from a high row number to see if your data is there (it should be).

answered Jul 27 '19 at 00:05

daShier

2,056
2
8
14

When I ran `SELECT` statements on records above _id = 1000 they were displayed no problem. Thank you – MagicGAT Jul 29 '19 at 17:11

score 0 · Accepted Answer · answered Jul 27 '19 at 08:06

0

In the query editor toolbar there's a drop down where you can limit the number of records you want to have returned. Default is 1000, but you can change that in a wide range, including no limitation.

answered Jul 27 '19 at 08:06

Mike Lischke

48,925
16
119
181

Excellent! Perfect answer. Thank you. – MagicGAT Jul 29 '19 at 17:10
@MagicGAT if you find my answer the correct one that helped you most then accept it (see the check mark below the voting buttons on the left). – Mike Lischke Jul 30 '19 at 08:26
1

Checked. Glad I can mark that because I can’t upvote yet, based on my reputation. – MagicGAT Jul 30 '19 at 19:07

score 0 · Answer 4 · answered Jul 29 '19 at 17:54

0

I thought this would be useful for future reference: In Microsoft SQL Server Management Studio, under Tools->Options->SQL Server Object Explorer->Value for Select Top <n> Rows Command change the number of rows returned:

answered Jul 29 '19 at 17:54

daShier

2,056
2
8
14

I’m using MySQL Workbench but hopefully this can help someone using SQL Server. Thanks daShier – MagicGAT Jul 30 '19 at 19:11

MySQL/InnoDB result set limited to 1000 records?

4 Answers4