Why is query running slow on nested loops?
Also note that in production, the temp table #Ids doesn’t exist, it’s a Table Valued Parameter of the same 10’000 Ids passed on to the Stored Procedure. As pointed out by the accepted answer, the problem was the OR clause in the category_link JOIN. However, the code suggested in the accepted answer is very slow, slower even than the original code.
Is there a slow query logging feature in MySQL?
As discussed in Chapter 2, the standard slow query logging feature in MySQL 5.0 and earlier has serious limitations, including lack of support for fine-grained logging. Fortunately, there are patches that let you log and measure slow queries with microsecond resolution.
What can affect the execution time of a query?
Other factors—such as storage engine locks (table locks and row locks), high concurrency, and hardware—can also have a considerable impact on query execution times.
How much time is spent in nested loops?
As you can see, almost 90% of the time is spent in the Nested Loops (Inner Join). Here’s extra information on those Nested Loops: Note that the table names don’t match exactly because I edited the query table names for readability, but it’s pretty easy to match (ads_alt_category = category).
How are nested loops join and performance spools work?
The static inner-side row set can therefore be cached and reused multiple times via the spool. A nested loops join performance spool never rebinds. A row count spool is little more than a Table Spool with no columns.
How are lazy table spools used in nested loops?
The lazy table spool is populated once during the first loop iteration. The spool rewinds its contents for each subsequent iteration of the join. With nested loops join, the inner side of the join is a static set of rows because the join predicate is on the join itself.
What to do when SQL query joins multiple tables?
Create an index on these tables or define the joined field as a PRIMARY KEY. As i can see, t1 table is the one which is being joined with all the tables, instead of putting them in a single query with so many joins, you can possibly try a Union of different queries something like this.
How long does it take to join a table in SQL?
After joining 12 tables, there was no significant change in query execution time. By the time I had joined the 13th table the execution time jumped to a 1 second; 14th table 4 seconds, 15th table 20 s, 16th 90 seconds. Keijro’s suggestion to use a correlated subqueries instead of joins e.g.