How can one optimize xyz queries

The pattern matching query itself is taking only 5 seconds independently. Matching millions of unique URLs is clearly not a problem. The next suspect on the list is the multiple JOIN statements. Perhaps the excessive JOIN s have contributed to the slowdown? I was ready to start tweaking the query to achieve any possible perf gains possible. My team and I brainstormed 2 tactics to try out:.

The interesting question to ask therefore, is that Why were both the overall query with JOINs and the subquery itself, independently fast, but dramatically slow when combined? Since nothing else was working, I decided to take a look at it anyway.

Which means that we do pattern matching on our URLs, then take an intersection with all the urls common with the recordings. Which means that not only was this operation expensive, it ran multiple times. This query is slow. Except, this is a crucial operation. Do not tune because you see some figure in a report that you don't like.

For example, no end user has ever telephoned the help desk to complain that "there are too many buffer busy wait events". Although this site is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari. Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects.

If one table involved in a statement does not have statistics, and optimizer dynamic sampling isn't performed, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed. This can be done by specifying hints. If the index is physically smaller than the table which is usually the case it will take less time to scan the entire index than to scan the entire table.

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well.

Usually the previous plan is not available. Hopefully more observations will lead you in a proper direction. Is this explain plan using the Explain plan command? Please use better mechanism. The explain during run time can be different from what you are seeing here, reason can be many like bind peeking, missing statistics, etc. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy. Already have an account? Sign in. By signing up, you agree to our Terms of Use and Privacy Policy.

Enter the email address associated with your account. We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. I added the indexes after all the data was already in the table. Does MySQL auto index old data? Adding the indexes didn't take more than half a second, though I'm not sure if it really did index the data. And I couldn't find a command to force reindexing a table.

Or is it better to use a single-column index for every column instead of a huge compound index with all columns?

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow.

Learn more. Asked 7 years, 3 months ago. Active 7 years, 3 months ago. Viewed 88 times. Any help would be appreciated. Thank you :.