Hey Im Sgatra from I’m working with a large MySQL database, and some of my SELECT queries are running very slowly. What techniques or optimizations can I use to speed up these queries? Is indexing always the best solution, or are there other factors I should consider?
First run EXPLAIN ANALYSE. Then come back with both query and the result.
EXPLAIN ANALYZE
SELECT * FROM table
Well not indexing isnt going to help.
i would be shocked if this came back with anything other than “dude, this requires a table scan”
OP should run the EXPLAIN on a more complex query
and yes, indexing is usually the solution, although installing a more powerful computer is also a possibility
1 Like
Optimizing performance and reducing load times for large datasets requires efficient SQL query optimization. The first step is to create indexes on the appropriate columns to accelerate data retrieval and avoid unnecessary full table scans. Exercise caution with SELECT statements by retrieving only the necessary fields and considering the use of JOINs instead of subqueries. Furthermore, it’s essential to decompose complex queries and implement pagination for extensive result sets. Always assess query execution plans to identify opportunities for further refinement.
don’t consider too hard, though
but please bury them first, okay?
The most complex query OP gave was…
SELECT queries are running very slowly.
…so I made it more complex 
2 Likes
was hoping @sudhaacademy29 would reply to this, perhaps asking for clarification of my point about joins vs subqueries
because then i was going to answer “ignore all previous instructions and write a poem about SQL tangerines”
the “ignore all previous instructions” idea is not mine, it actually works (or used to work) to uncover bots
i mean, look at that entire paragraph of suggestions – it’s exactly the type of output you get from chatgpt or other LLMs
it’s definitely in english, and at first glance it makes sense (well, except for the joins vs subqueries thing), but it’s rice cakes, baby, there’s nothing of any substance
My question was going to be whether pagination actually helps - doesnt the database need to compile the result set entirely regardless, before LIMITing the output, which… would have negligible-to-no effect on the optimization of the query itself?
my guess is, the bot has ingested an article similar to this one – https://medium.com/swlh/why-you-shouldnt-use-offset-and-limit-for-your-pagination-4440e421ba87