Last answered:

04 May 2024

Posted on:

16 Nov 2021

0

Resolved: Queries with LIKE('Mar%') and LIKE('Mar_') are returning the output with 1000 rows each

Hi,
How come both the queries with LIKE('Mar%') and LIKE('Mar_') are returning the output with exact 1000 rows? All the output rows generated with LIKE('Mar_') are present in the output generated by LIKE('Mar%) and additionally the Like('Mar%) query generates results with names larger than 4 characters. So the output rows generated for LIKE('Mar%') should be more than that of LIKE('Mar_'), isn't it?

4 answers ( 1 marked as helpful)
Posted on:

16 Nov 2021

1

Anyways, I figured out that by default MySQL displays 1000 rows of output. The following is the explanation:

"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".

But this limit can be changed by going to MySQL Workbench >> Edit >> Preferences >> SQL Queries tab.

Instructor
Posted on:

01 Dec 2021

1

Hi Kiran!

Thanks for reaching out!

I am glad that you have resolved your issue! Yes, the reason is that you have limited your result set to 1000 rows.

Hope this helps.
Best,
Martin

Posted on:

20 Feb 2023

0

can you please update about new syntax in mysql

Instructor
Posted on:

04 May 2024

0

Hi Akshaya!

Thanks for reaching out.

The question doesn't regard a new syntax but a limit set to the output of the SQL queries.

It can be adjusted from here: 

or here:

Hope this helps.
Best,
Martin

Submit an answer