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
4
votes
3 answers

ActiveRecord::Relation#explain always runs the query in full

I'm trying to use the explain method in both Rails 3 and 4 to estimate the number of rows returned in what can be a particularly expensive query. It joins 3 tables and can result in table scans of 10 million row tables, which combined with the…
user2259664
  • 175
  • 1
  • 8
4
votes
1 answer

Why does MySQL pick the wrong index in JOIN query?

I use a simple JOIN query, however MySQL keep showing wrong index in the EXPLAIN plan. It select an index on a column that does not participate in the query. The query is based on the primary key. I tried removing the index but then the optimizer…
Gidi Kern
  • 51
  • 3
4
votes
1 answer

MySQL Primary Key pair order affects JOIN speed

I have 3 tables in MySQL: innodb_verion 1.1.8 version 5.5.29-log Table: artist Columns: id int(11) new_id varchar(50) Table: collection Columns: id int(11) new_id …
andeo2L
  • 111
  • 1
  • 2
4
votes
3 answers

MySql and inline SELECTs

I have a query that looks like this: select id , int1 , int2 , (select count(*) from big_table_with_millions_of_rows where id between t.int1 and t.int2) from myTable t where .... This select returns exactly one row. The id used in the inline…
davek
  • 22,499
  • 9
  • 75
  • 95
4
votes
2 answers

How to understand SQLite `EXPLAIN QUERY PLAN` result?

I have read that joins are better than subqueries. But EXPLAIN QUERY PLAN SELECT Queue.Id, NULL FROM Queue INNER JOIN LastQueue ON Queue.Id=LastQueue.Id gives Array ( [0] => Array ( [selectid] => 0 …
Oriol
  • 274,082
  • 63
  • 437
  • 513
4
votes
1 answer

Using IN() clause resulting in Filesort

I have a simple table -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY by_id INT UNSIGNED NOT NULL posted_on INT UNSIGNED NOT NULL My table engine is MyISAM. I have a multiple column index called combo1 on by_id,posted_on,id I run this query…
sanchitkhanna26
  • 2,143
  • 8
  • 28
  • 42
4
votes
1 answer

MySQL: random effect on ANALYZE TABLE

I have got 3 innodb tables, say A, B, and C. There is a query to join these three tables to generate results. SELECT A.a, B.b, C.c from A join B on A.id = B.a_id join C on C.id = B.c_id where A.a = 'example' and B.b < 10; In the beginning when I…
Chen Xie
  • 3,849
  • 8
  • 27
  • 46
4
votes
2 answers

mysql - OR operator not using index

I have a simple invitation table: CREATE TABLE `invitation` ( `invitation_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `inviter_id` int(10) unsigned NOT NULL, `invitee_id` int(10) unsigned NOT NULL, PRIMARY KEY (`invitation_id`), UNIQUE…
robinmag
  • 17,520
  • 19
  • 54
  • 55
3
votes
2 answers

Why INSERT query is 3-4 times slower than SELECT?

Application talks to YugabyteDB instance with YCQL driver(Gocql). We have below two queries (for example): SELECT col2, col3, col4, col5 FROM table1 WHERE primarykeycol = 11ab8b12 - a934 - 4f2e - 8a0d - e7eba3faa47f; INSERT…
overexchange
  • 15,768
  • 30
  • 152
  • 347
3
votes
1 answer

what does Using filesort mean in MySQL?

I want to know what does Using filesort mean in MySQL? explain select * from user order by user_id; It turn up when i use 'order by'. 1,SIMPLE,orderitems,,ALL,,,,,1,100,Using filesort The 'Using filesort' is in extra. Thanks for everyone!
user15075329
  • 33
  • 4
3
votes
1 answer

Postgresql EXPLAIN command

I have a query that has several filter conditions after WHERE clause. Also, most of the columns involved have indexes on them. When I run EXPLAIN command, I see: -> Bitmap Index Scan on feature_expr_idx (cost=0.00..8.10 rows=14…
Mandroid
  • 6,200
  • 12
  • 64
  • 134
3
votes
1 answer

What does MySQl explain Extra "Using where" really mean?

According to the MySQL documentation, Using where means: A WHERE clause is used to restrict which rows to match against the next table or send to the client. As I understand, it means if your sql statement has a where condition, a 'Using where' will…
Java Beta
  • 43
  • 4
3
votes
2 answers

Why aren't indexes used in the first case but work in the other?

I'd like to verify that my assumptions are right. I have two tables, which only differ in index order. They look like this: CREATE TABLE `ipcountry` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ipFROM` INT(10) UNSIGNED ZEROFILL NOT NULL…
Jiri
  • 115
  • 8
3
votes
1 answer

Hive explain plan where to see full table scan?

How can I see from hive EXPLAIN is there a full table scan? For example, is there a full scan? The table size is 993 rows. The query is explain select latitude,longitude FROM CRIMES WHERE geohash='dp3twhjuyutr' I have secondary index on geohash…
Markiza
  • 444
  • 1
  • 5
  • 18
3
votes
1 answer

Python 3's shortest quine. I don't get it

_='_=%r;print (_%%_) ';print (_%_) (Edit: I have recieved your input and fixed the code, thanks for the correction.) This is the shortest quine you can write in Python (I'm told). A quine being code that returns itself. Can someone explain this…
Endvisible
  • 79
  • 6
1 2 3
…
39 40