Stack Overflow
Stack Overflow

Questions tagged [explain]

Explain is a SQL command that shows the execution plan of a query.

Explain is an useful command used in various Database servers(like Oracle, MySQL, PostgreSQL, etc). It shows the execution plan of a query.

Oracle: 

EXPLAIN PLAN FOR SELECT …

PostgreSQL, MySQL:

EXPLAIN SELECT …

MSSQL doesn't support explain, but uses

"SET SHOWPLAN_TEXT ON".

instead.

599 questions
11
votes
3 answers

SELECT statement not using possible_keys

I have a table from a legacy system which does not have a primary key. It records transactional data for issuing materials in a factory. For simplicities sake, lets say each row contains job_number, part_number, quantity & date_issued. I added an…
Paul Wieland
  • 765
  • 2
  • 10
  • 29
10
votes
2 answers

Explain MySQL explain execution plan maths, difference between two plans

I've got a basic MySQL performance question related to explain. I have two queries that return the same result and I am trying to understand how to make sense of the EXPLAIN of the execution plans. The table has 50000 records in it and I am…
Stuart Wakefield
  • 6,294
  • 24
  • 35
10
votes
2 answers

Mysql - "Select like" not using index

I have been playing around with indexes on MySQL (5.5.24, WinXP), but I can't find the reason of why the server is not using one index when a LIKE is used. The example is this: I have created a test table: create table testTable ( id varchar(50)…
Diego Buzzalino
  • 111
  • 1
  • 1
  • 6
9
votes
1 answer

How reliable is the cost measurement in PostgreSQL Explain Plan?

The queries are performed on a large table with 11 million rows. I have already performed an ANALYZE on the table prior to the query executions. Query 1: SELECT * FROM accounts t1 LEFT OUTER JOIN accounts t2 ON (t1.account_no = t2.account_no …
ADTC
  • 8,999
  • 5
  • 68
  • 93
9
votes
2 answers

Mysql Order by clause using "FileSort"

I have a table structure like comment_id primary key comment_content comment_author comment_author_url When I fire query like explain SELECT * FROM comments ORDER BY comment_id It outputs the results as id select_type table type …
Akash
  • 4,956
  • 11
  • 42
  • 70
8
votes
1 answer

Hive explain plan understanding

Is there any proper resource from where we can understand explain plan generated by hive completely? I have tried searching it in the wiki but could not find a complete guide to understand it. Here is the wiki which briefly explains how explain plan…
Vishnu Vardhan
  • 93
  • 1
  • 7
8
votes
1 answer

Extra in EXPLAIN printing - 'Impossible WHERE noticed after reading const tables'

I have a simple table called 'million_words'. It has one row with two columns -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY & word VARCHAR(50 NOT NULL. I run this query -: EXPLAIN SELECT * FROM million_words WHERE word = '-anon' The Extra…
sanchitkhanna26
  • 2,143
  • 8
  • 28
  • 42
7
votes
2 answers

Why the EXTRA is NULL in Mysql EXPLAIN? Why >= is Using index condition?

mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB there is a table named t and it has two indexes…
Journey
  • 71
  • 2
7
votes
2 answers

How to execute an `explain select` on a laravel builder

I know I could get the raw query from the query log, paste in all the bound variables (also found in the query log), slap a explain at the front of the query, and run it directly in the mysql console to get the explanation for the query.... but is…
Skeets
  • 4,476
  • 2
  • 41
  • 67
7
votes
7 answers

Can someone explain this SQL query to me?

I'm reading this article and I'm trying to understand this SQL statement but I am still somewhat new to SQL. I'm not sure what comment and c refer to. I think one of them is the table name but I am not sure of the other. Also, apparently there is a…
John Smith
  • 8,567
  • 13
  • 51
  • 74
7
votes
5 answers

What is the "filtered" column in MySQL EXPLAIN telling me, and how can I make use of it?

The MySQL 5.7 documentation states: The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the…
Michael Moussa
  • 4,207
  • 5
  • 35
  • 53
7
votes
3 answers

Postgresql output EXPLAIN ANALYZE to file

I need to know how long a particular query will run (I'm expecting a very long run time). To do this, I've decided to run an EXPLAIN ANALYZE on the query set with only a piece of the entire dataset and extrapolate from there. But I have a problem;…
Thomas
  • 871
  • 2
  • 8
  • 21
7
votes
1 answer

Understand EXPLAIN in a mysql query

I am trying to interpret the explain of mysql in a query(written in two different ways),this is the table: create table text_mess( datamess timestamp(3) DEFAULT 0, sender bigint , recipient bigint , roger boolean, msg…
emacos
  • 541
  • 1
  • 8
  • 17
7
votes
2 answers

Where am I going wrong in using a Join in the mysql query - Explain result posted too

I have this query which takes about 3.5 seconds just to fetch 2 records. However there are over 100k rows in testimonials, 13k in users, 850 in courses, 2 in exams. SELECT t.*, u.name, f.feedback FROM testmonials t INNER JOIN user u ON u.id =…
user1421214
  • 909
  • 4
  • 17
  • 24
6
votes
1 answer

What does it mean MATERIALIZED in the select_type column in the result of MySQL Explain closure?

I just got the next result of MySQL Explain closure: And I, unfortunately, don't understand what does it mean MATERIALIZED in the last row of the select_type column. If somebody knows what does it mean please give me any advice to study in this…
Alexander Leon Bulatov
  • 79
  • 2
  • 9
1
2
3
…
39 40