Hot answers tagged optimizer
33
"I'm more wondering why the query optimizer would ever use the plan it currently does."
To put it another way, the question is why the following plan looks cheapest to the optimizer, compared with the alternatives (of which there are many).
The inner side of the join is essentially running a query of the following form for each correlated value of ...
14
When I run your script to create a statistics only database and the query in the question I get the following plan.
The Table Cardinalities shown in the plan are
tblFEStatsPaperHits: 48063400
tblFEStatsBrowsers : 339
So it estimates that it will need to perform the scan on tblFEStatsPaperHits 339 times. Each scan has the correlated predicate ...
13
In my book even one scan of 50M rows is unacceptable... My usual trick is to materialize the distinct values and delegate the engine with keeping it up to date:
create view [dbo].[vwFEStatsPaperHitsBrowserID]
with schemabinding
as
select BrowserID, COUNT_BIG(*) as big_count
from [dbo].[tblFEStatsPaperHits]
group by [BrowserID];
go
create unique clustered ...
12
I would have guessed that when a query includes TOP n the database
engine would run the query ignoring the the TOP clause, and then at
the end just shrink that result set down to the n number of rows that
was requested. The graphical execution plan seems to indicate this is
the case -- TOP is the "last" step. But it appears there is more going
...
8
I have rather bad news for you in this regard
MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange
May 23, 2011 : http://stackoverflow.com/a/6092216/491757
Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan ...
8
This bug has been known for years, and won't ever be fixed. I first reported it 6 years ago but it has existed far longer than that. These are just estimated costs and don't really have any bearing on the optimizer itself and whether you can trust it. It's simply the showplan output that has some questionable math.
...
7
The recent 5.6 version has added this feature.
See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer
Typical Usage:
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET ...
7
The query optimizer does have n-ary operators, though the execution engine has rather fewer. To illustrate, I'm going to use a simplified version of your tables - (SQL Fiddle).
SELECT DISTINCT
number
INTO foo
FROM master..spt_values
WHERE
number < 1000;
SELECT DISTINCT
number
INTO boo
FROM master..spt_values
WHERE
number between 300 ...
7
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error). The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general:
DECLARE
@PageSize bigint = 10,
@PageNumber integer = 3;
WITH Numbered AS
(
SELECT TOP ...
7
One good source to refer to on FORCE INDEX would be the book MySQL Database Design and Tuning.
On page 120 paragraph 4, it says:
Where does FORCE INDEX fit in? FORCE INDEX (first enabled in version
4.0.9) is very similar to USE INDEX; the main difference between the two options is that FORCE INDEX demands that MySQL use the index (if
possible) in ...
6
As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
If you use a local variable in a query predicate instead of a
parameter or literal, the optimizer resorts to a reduced-quality
estimate, or a guess for selectivity of the predicate. Use parameters
or literals in the query instead of local variables
...
5
Certain functions that are known to be runtime constants go through the process called constant folding. By 'folding' a constant an expression is evaluated early in the query execution, the result is cached and the cached result instead when needed. The expression in your query DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) is, afaik, a runtime constant and thus ...
5
For SQL Server we have the option of the FORCE ORDER hint. The only comparable I'm aware of for MySQL is STRAIGHT_JOIN.
STRAIGHT_JOIN is similar to JOIN, except that the left table is always
read before the right table. This can be used for those (few) cases
for which the join optimizer puts the tables in the wrong order.
However, I'm inclined to ...
5
As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the ...
5
Is this normal for a forced join order to make the query estimates to
be completely inaccurate (and thus query times unpredictable)?
The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy.
Should I just expect that I'll have to either ...
4
You can get some of that information by turning on the configuration parameter log_planner_stats. Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs. It only explores an alternative plan until it can determine that it is slower than the current best plan. So alternative ...
4
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
With a slight fiddling of your query I get an equal cost estimate (50/50) and equal IO stats:
; WITH cte AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #objects
)
SELECT *
FROM cte
WHERE r >= 30 AND r < 40
ORDER BY r
SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY
This avoids the additional ...
4
When you use TOP, the Optimizer sees an opportunity to do less work. If you ask for 10 rows, then there's a good chance it doesn't need to consume the whole set. So the TOP operator can be pushed much further to the right. It will keep requesting rows from the next operator (on its right), until it has received enough.
You point out that without TOP, the ...
3
The two queries have a very big difference:
----- query 1
SELECT COUNT(*)
FROM customers
WHERE ID > 10000
AND country = 'US' ;
----- query 2
SELECT *
FROM customers
WHERE ID > 10000
AND country = 'US' ;
While the second query returns all rows that match the WHERE conditions, the first one has an aggregate function (COUNT()) in the SELECT ...
3
I posted this answer in response to the same question on StackOverflow, so here is a re-post:
An important distinction to make is that according to IBM, zIIP is only available for "eligible database workloads", and those "eligible" loads are mostly targeted for large BI/ERP/CRM solutions that run on distributed servers, which are connecting through DDF ...
2
You have to keep in mind two major aspects about using the MEMORY Storage Engine
ASPECT #1
The MEMORY table behaves like a MyISAM table in that it performs a full table lock when doing INSERTs, UPDATEs, and DELETEs (MySQL Documentation says Locking granularity : Table). I would be very concerned with a high lock ratio if it bottlenecks your I/O ...
2
Index_Condition_Pushdown is used for Queries with the range, ref, eq_ref, and ref_or_null access methods, i.e when there is a need to access full table rows.
Where as Index Condition Pushdown is enabled by default; it can be controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag.
To Switch:
SET [GLOBAL|SESSION] ...
2
Back on October 12, 2012, I wrote this post : When should I think about upgrading our RDS MySQL instance based on memory usage?
I gave the following chart
MODEL max_connections innodb_buffer_pool_size
--------- --------------- -----------------------
t1.micro 34 326107136 ( 311M)
m1-small 125 1179648000 ( 1125M, ...
2
OPTIMIZE TABLE basically does three(3) things
Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics
Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp ...
1
The best I can immediately mention is to simply run EXPLAIN on a query.
There is a graphical way to do this
pt-visual-explain
mk-visual-explain (Source Code)
As for steps to optimizing a query, a parse tree is created. Rather than explaining the parser here, please read my post from Mar 11, 2013 (Is there an execution difference between a JOIN condition ...
1
SQL Server does have 3-way set operations; the CONCATENATION operator accepts n inputs. Given, for example, ten tables:
CREATE TABLE Test01 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test02 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test03 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE ...
1
Another option to optimizer hints if you cannot even touch the query is to use Plan Guides. However, since your query statement is dynamic with many unparameterized tokens, I doubt it's possible.
Since you know your data, I think you're better off just staying the path of query hints. Statistics have been known to be lies, lies and damn lies. It can only ...
1
The point of zIIP processors is that you can't run z/OS code on them. Your COBOL code won't run on them. However, according to this article DB/2 for z/OS is an eligible workload to run on a zIIP processor. I presume your COBOL code has embedded SQL. Embedded SQL architectures are actually preprocessors that generate code that sends the query off to the ...
1
You may have missed that spot in the chapter of the manual you quote:
Note also that since ANALYZE uses random sampling while producing
statistics, the results will change slightly after any new ANALYZE.
Emphasis mine.
If you actually mean "how many" rows in your question: the query planner has selectivity estimators for certain operators in the ...
Only top voted, non community-wiki answers of a minimum length are eligible
