The tag has no usage guidance.

learn more… | top users | synonyms (1)

0
votes
3answers
78 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename....
3
votes
1answer
203 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
2
votes
1answer
26 views

How do I find what keys mysql will use for a query?

I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and ...
1
vote
1answer
26 views

How to optimize search restricted by boolean column?

I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get ...
0
votes
1answer
22 views

Disc IO for query in MongoDB

is there a way to tell how much data MongoDB is reading from disc and how much from cache for a query ? I looked at the explain()-Function but did not find this kind of informations. Thanks, Andre
2
votes
0answers
340 views

Why is this MySQL query “using where” instead of “using index”?

Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join op_adv_effects....
2
votes
0answers
148 views

Almost identical queries but large time difference in execution

Exhibit A SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at >...
1
vote
0answers
87 views

How to enable missing Tempspc column in Oracle explain plan

I am trying to debug "ORA-01652: unable to extend temp segment" from a data warehouse ETL batch job, but my plan_table_output view from explain plan does not show the "Tempspc" column. But I do not ...
1
vote
0answers
67 views

Explain partitions

What does the possible keys, reference and rows indicate in explain partitions? I have used partitions on the table and I executing the explain partitions with a query: select count(...
1
vote
0answers
373 views

Amazon Redshift: “*SELECT* 1” vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4

I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on ...
1
vote
0answers
81 views

Oracle sql developer tree shaped represantation of Explain_Plan

I want to ask what exactly happens to the following Explain_Plan table : It firsts Joins the two tables Products & Order_Items then the output is joined with Orders and then with the Customers....
1
vote
0answers
85 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
0
votes
0answers
117 views

Visual Explain Not working in MySQL Workbench

Is there some trick to getting the Visual Explain feature to work in MySQL Workbench? I am running version 6.3.6 on Windows 7. I've tried simple queries with just one join. I've tried it with complex ...
0
votes
0answers
57 views

EXPLAIN SELECT query is writing to tmp table

I am getting a "copying to tmp table" when running an EXPLAIN SELECT on a query. This is a pseudo-query showing the structure of the query (I can't post the actual query): SELECT colA , colB , ...
0
votes
0answers
139 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
0answers
255 views

MySQL explain query: Using temporary table vs more rows examined

EDITED: Can you tell me which explain plan is better?! First: explain SELECT SQL_NO_CACHE count(*) FROM a force index(uni) WHERE a.date BETWEEN 20141001 AND 20141031 AND ((a.cid = 157 AND a.tid IN ( ...
0
votes
0answers
69 views

Optimizing query - understanding explain

I've got a query (partly generated by django orm, but can be modified). For some reason, It's very slow (between 1.5 -3 seconds, without having lots of data on the system) . I've got an "explain" ...
0
votes
0answers
98 views

Explain plan shows a table not in the query

In trying to optimize a 2 minute query against an SAP R3 Oracle database, I examined the Explain Plan in Toad and found that most of the query's time is spent on a table that isn't even in the query. ...
0
votes
0answers
713 views

Oracle 11.2 Explain Plan takes too long

I'm using Oracle 11.2 SE on a 16GB Xeon E3 Linux machine as well as 11.2 XE on a 8GB i7 Win8 machine: SELECT banner FROM v$version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL ...
0
votes
0answers
91 views

explain plan ,joins and optimization

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance ...
0
votes
0answers
41 views

Large-query optimization: given my “explain” results, what stands out?

I've got a monster query, which runs quite quick in many cases, but in some cases very much slower. The EXPLAIN output below is from one of the slow cases. I'm guessing that the slowdown is related ...