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
36
votes
2 answers

What does eq_ref and ref types mean in MySQL explain

When we prefix an SQL query with the keyword "explain" we get a table with some columns. Please tell me what is the "type" column. What does eq_ref and ref mean in that context.
Patruni Srikanth
  • 741
  • 1
  • 7
  • 14
26
votes
4 answers

How do I use DB2 Explain?

How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2? I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them --…
Trevel
  • 801
  • 1
  • 7
  • 13
24
votes
2 answers

How to interpret the output of MySQL EXPLAIN?

I want to select the content of the column text from entrytable. EXPLAIN SELECT text FROM entrytable WHERE user = 'username' && `status` = '1' && ( `status_spam_user` = 'no_spam' || ( `status_spam_user` = 'neutral' && …
R_User
  • 10,682
  • 25
  • 79
  • 120
18
votes
2 answers

Mysql Explain Query with type "ALL" when an index is used

I ran a query in Mysql like below: EXPLAIN SELECT * FROM( SELECT * # Select Number 2 FROM post WHERE parentid = 13 ORDER BY time, id LIMIT 1, 10 ) post13_childs JOIN post post13_childs_childs ON…
ahoo
  • 1,321
  • 2
  • 17
  • 37
17
votes
3 answers

What does Using join buffer (Block Nested Loop) mean with EXPLAIN mysql command in the Extra column?

I am trying to optimize my query. And getting Using join buffer (Block Nested Loop) for one of the table with EXPLAIN SELECT command. I have no idea what does it mean. I tried googling about, but I haven't found the explanation.
123
  • 2,169
  • 3
  • 11
  • 35
16
votes
1 answer

MySQL EXPLAIN EXTENDED filtered column (obviously it's not a percentage)

I've been searching for this and they all state some sort of percentage, explain this: EXPLAIN EXTENDED SELECT * FROM PageAccess ORDER BY AccessId DESC LIMIT 20; SELECT COUNT(*) FROM PageAccess; Giving: id, select_type, table, type, possible_keys,…
Alec Teal
  • 5,770
  • 3
  • 23
  • 50
16
votes
1 answer

How to determine what is more effective: DISTINCT or WHERE EXISTS?

For example, I have 3 tables: user, group and permission, and two many2many relationships between them: user_groups and group_permissions. I need to select all permissions of given user, without repeats. Every time I encounter a similar problem, I…
defuz
  • 26,721
  • 10
  • 38
  • 60
15
votes
2 answers

MySQL. Creating an index for "OR" queries

I have a table of 200k entries with columns of INT's. I want to create an index to make queries faster. This is the query I would like to execute: SELECT A,B,C,D,E FROM table WHERE A=23 and (B=45 or C=43). I created the following indexes: B, ACD,…
Daniel Vila Boa
  • 670
  • 5
  • 13
15
votes
3 answers

MySQL Explain: what's causing 'Using temporary; Using filesort'

I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. Mostly, I'm wondering why it's using filesort intead of…
bradvido
  • 2,743
  • 7
  • 32
  • 49
15
votes
4 answers

Why the rows returns by "explain" is not equal to count()?

mysql> select count(*) from table where relation_title='xxxxxxxxx'; +----------+ | count(*) | +----------+ | 1291958 | +----------+ mysql> explain select * from table where relation_title='xxxxxxxxx'; +----+-------------+---------+- | id |…
ZA.
  • 10,107
  • 10
  • 37
  • 39
14
votes
1 answer

What does loop in explain analyze statement mean?

I am profiling my query. postgres=# explain analyze select * from student; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on…
Mangu Singh Rajpurohit
  • 10,806
  • 4
  • 68
  • 97
13
votes
2 answers

How can I use SQLAlchemy to do "mysql explain"

I have a sql like: DBSession().query(Model).filter(***) and I want to explain this sql using SQLAlchemy.
Tallmad
  • 1,951
  • 4
  • 22
  • 29
12
votes
2 answers

JDBC Oracle - Fetch explain plan for query

Im wondering how I can fetch the explain plan using Java. Reason I need this is because we have a framework where special users can craft reports. These reports sometimes build huge queries in which we want to explain on the fly and store the cost…
Chris Dale
  • 2,222
  • 2
  • 26
  • 39
12
votes
2 answers

Mysql: inner join on primary key for 2 IDs gives "Range checked for each record"

I get a "Range checked for each record (index map: 0x1)" in EXPLAIN SELECT when doing an INNER JOIN on a PRIMARY key with 2 values (using either IN or OR constructs) Here is the query: SELECT * FROM message AS m INNER JOIN user AS u ON u.id =…
Lideln Kyoku
  • 952
  • 9
  • 20
11
votes
2 answers

MySQL command Explain ignore LIMIT?

I use MySQL server version 5.5.14 and now I am trying this simple SQL query with Explain command: EXPLAIN SELECT id, name, thumb FROM `twitter_profiles` LIMIT 10; and it shows me this…
Jakub Mach
  • 1,109
  • 3
  • 10
  • 19
1
2 3
…
39 40