When working with databases, optimizing SQL queries is essential to achieve better performance and reduce response times. In this post, we'll cover...
For further actions, you may consider blocking this person and/or reporting abuse
ORDER BY and LIMIT still sorts all the records, otherwise you'd never be able to do paged sorted lists.
Not sure how it can be "bad practice" to use a %searchitem% if that's how the data is! Presumably a good idea to use other ANDed statements to try to reduce the list so that the final scanned set is as limited as possible - presuming such limitations are viable in the scenario.
I'm confused about your DISTINCT example, definitely not true for MySQL as MySQL can optimize DISTINCT queries by using an index to quickly find and remove duplicate rows. This can be a significant performance improvement for queries that return a large number of rows.
MySQL cannot optimize GROUP BY queries in the same way, because the GROUP BY clause requires MySQL to calculate aggregate functions for each group of rows. This can be a significant performance penalty for queries that return a large number of rows.
In general, you should use the DISTINCT clause whenever you only need to return unique rows from a table. You should use the GROUP BY clause whenever you need to calculate aggregate functions for groups of rows.
Simply the database engine gets the first 10 rows ASC/DESC, so no need to sort 100,000 rows, when the SQL engine will find 10 will stop
limit-optimization
MySQL is really smart and can figure out when you only want a few rows from a big table. When you use LIMIT, sometimes MySQL can use a special trick to find those rows really fast, If you use LIMIT with an ORDER BY clause, MySQL will stop sorting the rows as soon as it finds enough rows to show you. This is really fast if the table is already sorted in a certain way
DISTINCTandGROUP BY- Sometime the scenario change, but the behavior and implementation is different - DISTINCT uses temp table, while GROUP BY uses Hashdistinct-optimization
group by and distinct
@miketalbot