The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query.
1
vote
1answer
30 views
Will OPTIMIZE TABLE have any impact on my data?
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.
But before doing so, I would (of course) like to know if ...
3
votes
0answers
41 views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
5
votes
1answer
116 views
How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]
Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
4
votes
1answer
113 views
Type conversion in expression may affect “CardinalityEstimate” in query plan choice?
I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.
We constraint the events on ...
1
vote
1answer
170 views
MySQL optimization
I'm trying to optimize a MySQL Server to be able to serve as many connections as it can.
The server is in AmazonAWS RDS and has currently the following resources:
--7.5 GB memory, 4 ECUs (2 ...
6
votes
1answer
95 views
Ok to drop indexes on FK's if they have no stats in the DMVS
I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.
Are you 100% safe to drop these ...
6
votes
3answers
255 views
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
2
votes
2answers
390 views
Why won't SQL Server optimize the UNIONs?
Consider these queries (SQL Fiddle):
Query 1:
SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;
Query 2:
SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
...
-3
votes
1answer
77 views
want to optimize this query by removing two SELECT and two JOINS
SELECT
t3.transaction_pci_details_id,
t3.terminal_id,
t3.transaction_no,
t4.transaction_id,
t3.transaction_type,
t3.reversal_flag,
t3.transmission_date_time,
t4.retrivel_ref_no,
t3.card_no,
...
0
votes
1answer
209 views
Query performance and join hints, plan cost, and duration
Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases.
Background
I have a reporting database using ...
8
votes
2answers
865 views
How (and why) does TOP impact an execution plan?
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
6
votes
1answer
818 views
OPTION FORCE ORDER improves performance until rows are deleted
I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.
...
4
votes
2answers
504 views
Is there a way to hint to query optimizer to MySQL which constraints should be done first?
This is my current query:
SELECT BusinessID as ID,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
SELECT *
FROM
...
4
votes
2answers
229 views
Can I optimize for the zIIP processor?
Is it possible to change SQL in a z/OS mainframe COBOL application so that it becomes eligible to be directed to the IBM System z Integrated Information Processor (zIIP)?
3
votes
1answer
136 views
How does the MySQL Query Optimizer react to a SELECT COUNT sentence?
Let's say we have a table called "customers" with these columns ("id", "name", "country", "created_at).
There's an index by ("country", "created_at").
If
SELECT COUNT(*)
FROM customers
WHERE ID ...
23
votes
3answers
2k views
Unexpected scans during delete operation using WHERE IN
I've got a query like the following:
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
...
3
votes
1answer
196 views
Does SQL Server evaluate functions once for every row?
I have a query like this:
SELECT col1
FROM MyTable
WHERE
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
BETWEEN col2
AND col3
;
This gives a tooltip on the execution plan ...
8
votes
1answer
645 views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
2
votes
1answer
61 views
PostgreSQL + query planner + amount of entities
I'm working on college work about how query planner uses statistics and makes the most optimal query.
I have read the 57.1. Row Estimation Examples article and I know how's the PostgreSQL calculates ...
2
votes
1answer
892 views
Mysql Memory table getting many locks
On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.
This table has the following activity:
1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of ...
1
vote
1answer
58 views
How we can enable index_condition_pushdown in MySQL..?
I have optimizer_switch variable as
mysql> show variables like 'optimizer_switch';
+------------------+-------------------------------------------------------------------------------------------+
...
6
votes
1answer
732 views
Using FORCE INDEX
I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O.
It was something to do with the MySQL Query Optimizer not needing to go ...
