MySQL - identifying bottlenecks in complex queries I apologize if this question is too general, I can post and example code, but it may not be reproducible because there is no access to the actual database.

Suppose I have a big MySQL query with a lot of joins and unions, as well as functions like concat, date, various time and date conversion functions. And it uses a lot of tables, nested select queries etc. Lets suppose it is a select query.

My questions is, where would one start, if they need to optimize this script to run faster? Currently its taking hours to complete. Furthermore, If I run stand-alone chunks of code from it (including some nested queries, etc.) they run much faster. Therefore there are one or a few bottlenecks. 

I am aware of profiling and bench-marking as well as explain functionality in MySQL they all help us to understand what MySQL does behind the scenes, but all provide the summary for the entire script overall. What would be the best way to identify these bottlenecks without profiling each each portion of the script separately? Are there best practices when faced with such problem?

Again, I apologize for asking a question that may be too broad. I can post and example code, but it may not be reproducible because there is no access to the actual database.
The MySQL documentation includes a section with general query tuning advice. However, if you read that, you'll see that it offers little in terms of specific advice.

That's because there aren't a lot of things that you can do that make every query you write better.

There are a few things you can do, though:


Develop an understanding of how the optimizer works: Look for articles that talk about query tuning in your DBMS of choice, and learn the things that most often result in sub-optimal query plans. Similarly, develop an understanding of your DBMS' query plans: how to see them, how to follow them, and what they mean.
Keep searches SARGable: That means making sure that your query will use indexes wherever possible. The biggest rule to follow for this is to avoid comparisons that require applying functions to your columns in comparisons. If the engine has to apply a function to the data from your table, a standard index can't be used (because the values in it aren't the values being compared).

If you can, write comparisons so the side with the most rows, with an index on the column involved, doesn't have a function applied on it. Apply a more complicated function to a column with fewer rows to worry about, if a function must be involved. Whenever possible, make sure that values to be compared to each other are of the same data type, and are using the same units, so they can be compared directly without needing functions.
Follow maintenance standards: While following the first step in my advice, you may notice some trends in what causes poor query plans. in SQL Server, I've noticed that one of the sings that a plan may be off is when the optimizer's estimate of the number of rows a particular step will return is far off from the actual number of rows returned. One of the most common reasons for these estimates to be off is if SQL Server's statistics (which help it determine the "cardinality" of a piece of a query - how many rows it's likely to bring back. If you've only got 100 rows from one table, the most effective way to join it to another table may be completely different than if you've got 1 million rows.

In SQL Server, these estimates are usually much better if you regularly run a job to update the statistics on your tables. Watch for things like this in MySQL, and make sure you're running the appropriate maintenance jobs as often as you need to.
Avoid pre-emptive "optimization": Some of the tricks used to force queries to perform better involve forcing the query to operate in a specific way; for instance, using hints to force a particular method to be used when joining table, or to use a specific index. In some circumstances, this sort of thing can help; however, whenever you use one of these, you limit the optimizer's options when figuring out the best way to run the query.

If you have a column in your table where there are 100 distinct values, but 90% or the rows all have 1 value, and the other 99 values are split amongst the other 105 of the rows. If you search for a value from that table (using a variable to pass in the value), then a run where the variable is set to the most common value, the optimizer isn't likely to use an index on that column; the value doesn't do a good job of narrowing down the number of rows the query has to deal with. If any of the other values is used, then the index might be useful, because it would tend to limit the number of rows fairly effectively.

If you force the query to always use an index on this column, then it will likely work well any time the value is one of the rare ones, but relatively poorly if the value is the most common one.
Be flexible: Keep learning, and try not to assume you know everything. Most DBMSes receive fairly regular updates, and these can change the rules. Know what works now, but keep an eye out for new articles, and new questions, to see if some new strategy has been added to the toolkit, or replaced an old one.

An example, again from the SQL Server world: for years, it was standard advice that you should regularly defragment indexes to keep performance good. While this isn't bad advice, in the past couple of years more and more people are saying that defragmenting your indexes isn't as necessary as they used to think. You see, when an index is defragmented, its statistics are normally updated as well; and, it seems that the updated statistics (helping provide better query results) are probably more important that the actual defragmenting; and, are less intrusive. A case where the old advice has been superseded by new advice (even for older version of the software).


Generally, assume the optimizer will do its job well 99%+ of the time; don't limit its options unless you see a real reason to do so; and then only after you've tried other ways to optimize the query. You don't know exactly how any query will perform until it's performing in a production environment. Write queries as wisely as you can, but try to start with a query you (and others who may not even be on your team now) can maintain in general. Only twist a query around in weird ways to improve performance when it's been proven to be necessary.