When attempting to understand how a SQL statement is executing, it is sometimes recommended to look at the explain plan. What is the process one should go through in interpreting (making sense) of an explain plan? What should stand out as, "Oh, this is working splendidly?" versus "Oh no, that's not right." ...
NOTE: the original question is moot but scan to the bottom for something relevant. I have a query I want to optimize that looks something like this: select cols from tbl where col = "some run time value" limit 1; I want to know what keys are being used but whatever I pass to explain, it is able to optimize the where clause to nothing...
In output of explain command I found two terms 'Seq Scan' and 'Bitmap heap Scan'. Can somebody tell me what is the difference between these two types of scan? (I am using PostgreSql) ...
I'm helping maintain a program that's essentially a friendly read-only front-end for a big and complicated MySQL database -- the program builds ad-hoc SELECT queries from users' input, sends the queries to the DB, gets the results, post-processes them, and displays them nicely back to the user. I'd like to add some form of reasonable/he...
Hi, After performing EXPLAIN on a query: explain select name from t1 where name like '%smthing%' UNION ALL select name from t2 where name like '%smthing%' UNION ALL select name from t3 where name like '%smthing%' UNION ALL select name from t4 where name like '%smthing%' composed by the UNION of 4 tables I get this: id select_type...
I'm a relative novice when it comes to databases. We are using MySQL and I'm currently trying to speed up a SQL statement that seems to take a while to run. I looked around on SO for a similar question but didn't find one. The goal is to remove all the rows in table A that have a matching id in table B. I'm currently doing the follow...
Are there any good ways to objectively measure a query's performance in Oracle 10g? There's one particular query that I've been tuning for a few days. I've gotten a version that seems to be running faster (at least based on my initial tests), but the EXPLAIN cost is roughly the same. How likely is it that the EXPLAIN cost is missing ...
I'm new to Oracle and to SQL Developer. I'm trying to optimize a query but don't quite understand some of the information returned from Explain Plan. Can anyone tell me the significance of the OPTIONS and COST columns. In the OPTIONS column, I only see the word FULL. In the COST column, I can deduce that a lower cost means a faster query...
While Windows releases of MySQL administration and profiling tools seem to be a dime a dozen, I'm having a very hard time finding very many designed for Linux. Note that I'm not looking for monitoring software ala Nagios/Munin here. This would be something more along the lines of a SQLYog. While I could write some basic scripts to han...
I have inherited a somewhat messy query that I am working on refactoring to be performant. During this process, one of the things I did due to personal preference was change all of the ANSI-99 join syntax from the "inner join" and "left outer join" statements to be predicates in the query. I noticed two very strange things that I wou...
I always thought that a node in an execution plan can only be executed after its children have executed, and thus the total cost of a node has to be greater or equal than the cost of the child nodes. However, this is not always the case, like in the following example: Plan hash value: 2810258729 ----------------------------------------...
I'm using geokit (acts_as_mappable) in a rails application, and the performance of radial or bounds searches degrades considerably when there is a large number of models (I've tried with 1-2million but the problem no doubt kicks in earlier than this). Geokit does all its calculations based on lat and lng columns in the table (latitude ...
Consider the following query: select FEE_NUMBER from CARRIER_FEE CF left outer join CONTYPE_FEE_LIST cfl on CF.CAR_FEE_ID=cfl.CAR_FEE_ID and cfl.CONT_TYPE_ID=3 where CF.SEQ_NO = ( select max(CF2.SEQ_NO) from CARRIER_FEE CF2 where CF2.FEE_NUMBER=CF.FEE_NUMBER and CF2.COMPANY_ID=CF.COMPANY_ID group by CF2.FEE_NUMBER) g...
Does anyone know of a tool that can be used to compare (relatively complex) query plans? I'm not looking for a guide to query plans, but just a tool that enables me to quickly see e.g. the different index use. EDIT: just to make it clear, I'm not looking for information about the plan, but a tool that can quickly point out the differenc...
I have a complex MySQL query that joins three tables and self-joins one table to itself. There is a Master and a Slave that have identical data and indices. The Master is a powerful box compared to the Slave, yet the query runs 10x faster on the Slave (during a period of light load for the Master). The execution plans are vastly diffe...
What is the meaning of Select tables optimized away in MySQL Explain plan? explain select count(comment_count) from wp_posts; +----+-------------+---------------------------+-----------------------------+ | id | select_type | table,type,possible_keys, | Extra | | | | key,key_len,ref,rows | ...
I have this mysql query that I am trying to analyze. It is very slow, the visitor table here is about 50K entries, this query never returns. When I tried an explain statement, I found out that the index is not being used on the visitor table, In spite of the index being available. Now this is the great puzzle I need help solving. Any hin...
I'm using Oracle 10g Express on Windows XP, and on a Macbook OS X 10.5.8 running PHP 5.3.1 with PDO and Oracle Instant Client 10.2.0.4.0_4, I run the following script: <?php $pdo = new PDO("oci:dbname=winders:1521/xe", "system", "XXXXXX"); ... $sql = "WITH NumberedBugs AS ( SELECT b.*, ROW_NUMBER() OVER (ORDER BY bug_id) AS RN ...
I spent over an hour today puzzling myself over a query plan that I couldn't understand. The query was an UDPATE and it just wouldn't run AT ALL. Totally deadlocked: pg_locks showed it wasn't waiting for anything either. Now, I don't consider myself the best or worst query plan reader guy, but I find this one exceptionally difficult. I'm...
When I attempt to do something like SELECT Max(ObjectId) FROM Objects; I see that in the explain-plan that this is performed by doing a sort. Now, sorting (which I guess would require something in the complexity of O(nlogn)) must be much costlier than just scanning each row and remembering the max value (which could be done in O(n)). ...