How to optimize SQL query with two where clauses?
You can just use UNION to merge results of 2nd and 3d queries More about UNION.
You can just use UNION to merge results of 2nd and 3d queries. More about UNION.
First thing that comes to mind is to union the two: SELECT * FROM tbl1 JOIN tbl2 ON something = something WHERE 1 AND ('$date' BETWEEN planA AND planB ) UNION ALL SELECT * FROM tbl1 JOIN tbl2 ON something = something WHERE 1 AND date = '$date' You have provided too little to make optimizations. We don't know anything about your data structures.
Even if most slow queries are usually due to the query itself or index setup of the used tables, you can try to find out where your bottleneck is with using the MySQL Query Profiler, too. It has been implemented into MySQL since Version 5.0.37. Before you start your query, activate the profiler with this statement: mysql> set profiling=1; Now execute your long query.
With mysql> show profiles; you can now find out what internal number (query number) your long query has. If you now execute the following query, you'll get alot of details about what took how long: mysql> show profile for query (insert query number here); (example output) +--------------------+------------+ | Status | Duration | +--------------------+------------+ | (initialization) | 0.00005000 | | Opening tables | 0.00006000 | | System lock | 0.00000500 | | Table lock | 0.00001200 | | init | 0.00002500 | | optimizing | 0.00001000 | | statistics | 0.00009200 | | preparing | 0.00003700 | | executing | 0.00000400 | | Sending data | 0.00066600 | | end | 0.00000700 | | query end | 0.00000400 | | freeing items | 0.00001800 | | closing tables | 0.00000400 | | logging slow query | 0.00000500 | +--------------------+------------+ This is a more general, administrative approach, but can help narrow down or even find out the cause for slow queries very nice. A good tutorial on how to use the MySQL Query Profiler can be found here in the MySQL articles.
I've done what you suggested and the part "Copying to tmp table is the main bottleneck here. How do I optimize this? – Henson Jan 22 at 10:50 This is a result to your Group-By-Clause in the Query and using different tables.
If some of columns from a GROUP BY clause are not indexed or if the columns in the GROUP BY clause are from different tables, MySQL will do this. So if the Indexes on the columns are set, then I'd use the UNION-solution the other people in here suggested. – Bjoern Jan 22 at 11:50.
For more information, see Parallel Query Processing. Rewrite the query. If the query uses cursors, determine if the cursor query could be written using either a more efficient cursor type (such as fast forward-only) or a single query.
Single queries typically outperform cursor operations. Because a set of cursor statements is typically an outer loop operation, in which each row in the outer loop is processed once using an inner statement, consider using either a GROUP BY or CASE statement or a subquery instead. For more information, see Cursor Types (Database Engine) and Query Fundamentals.
If an application uses a loop, consider putting the loop inside the query. Often an application contains a loop that contains a parameterized query, which is executed many times and requires a network round trip between the computer running the application and SQL Server. Instead, create a single, more complex query using a temporary table.
Only one network round trip is necessary, and the query optimizer can better optimize the single query. For more information, see Procedural Transact-SQL and Transact-SQL Variables.Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query.
SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection. Use query parameterization to allow reuse of cached query execution plans. If a set of queries has the same query hash and query plan hash, you might improve performance by creating one parameterized query.
Calling one query with parameters instead of multiple queries with literal values allows reuse of the cached query execution plan. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes and Execution Plan Caching and Reuse. If you can not modify the application, you can use template plan guides with forced parameterization to achieve a similar result.
For more information, see Specifying Query Parameterization Behavior by Using Plan Guides. Make use of query hints only if necessary. Queries using hints executed against earlier versions of SQL Server should be tested without the hints specified.
The hints can prevent the query optimizer from choosing a better execution plan. For more information, see SELECT (Transact-SQL). Use the query_plan_hash to capture, store, and compare the query execution plans for queries over time.
For example, after changing the system configuration, you can compare query plan hash values for mission critical queries to their original query plan hash values. Differences in query plan hash values can tell you if the system configuration change resulted in updated query execution plans for important queries. You might also decide to stop execution for a current long-running query if its query plan hash in sys.
Dm_exec_requests differs from its baseline query plan hash, which is known to have good performance. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes. Make use of the query governor configuration option.
The query governor configuration option can be used to prevent system resources from being consumed by long-running queries. By default, the option is set to allow all queries to execute, no matter how long they take. However, you can set the query governor to limit the maximum number of seconds that all queries are allowed to execute for all connections, or just the queries for a specific connection.
Because the query governor is based on estimated query cost, rather than actual elapsed time, it does not have any run-time overhead. It also stops long-running queries before they start, rather than running them until some predefined limit is hit. For more information, see query governor cost limit Option and SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).
Optimize reuse of query plans from the plan cache. The Database Engine caches query plans for possible reuse. If a query plan is not cached, it can never be reused.
Instead, uncached query plans must be compiled each time they are executed, which results in poorer performance. The following Transact-SQL SET statement options prevent cached query plans from being reused. In addition, the SET ANSI_DEFAULTS option affects the reuse of cached query plans because it can be used to change the ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, and the QUOTED_IDENTIFIER SET options.
Note that most of the SET options that can be changed with SET ANSI_DEFAULTS are listed as SET options that can affect the reuse of query plans. Use the sp_configure stored procedure for server-wide changes. For more information, see sp_configure (Transact-SQL).
Use the SET clause of the ALTER DATABASE statement. For more information, see ALTER DATABASE (Transact-SQL)Change OLE DB and ODBC connection settings. For more information, see Client Network Configuration.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.