Resolved: Queries with LIKE('Mar%') and LIKE('Mar_') are returning the output with 1000 rows each
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?
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.
- It prevents accidents where users have not written, WHERE clause and execute query which retrieves all the rows from the table.
- 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.
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.