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
6
votes
1 answer

Why is Postgres EXPLAIN ANALYZE execution_time different than when I run the actual query?

I'm using a database client to test. Using EXPLAIN ANALYZE: Hash Join (cost=5.02..287015.54 rows=3400485 width=33) (actual time=0.023..1725.842 rows=3327845 loops=1) Hash Cond: ((fact_orders.financial_status)::text =…
john
  • 33,520
  • 12
  • 45
  • 62
6
votes
1 answer

Postgresql huge performance difference when using IN vs NOT IN

I have 2 tables, "transaksi" and "buku". "transaksi" has around ~250k rows, and buku has around ~170k rows. Both tables have column called "k999a", and both tables use no indexes. Now I check these 2 statements. Statement 1: explain select k999a…
aaron
  • 257
  • 6
  • 15
6
votes
2 answers

what is the equivalent of EXPLAIN form SQLite in SQL Server?

I used an SQLite database and run an EXPLAIN statement before executing the actual query to verify if there was any attempt to write on the database. Now, we have migrated to SQL Server and I need to know if a query tries to write on the database or…
Gabriel Diaconescu
  • 1,769
  • 3
  • 21
  • 31
6
votes
1 answer

How to force mysql UPDATE query to use index? How to enable mysql engine to automatically use the index instead of forcing it?

Below is the update query/query plan that is not using the compound index that was created recently. The explain shows that its not using the compound index named radacctupdate which i think will make the update query faster. There are other…
sherpaurgen
  • 3,028
  • 6
  • 32
  • 45
6
votes
1 answer

Fix Using where; Using temporary; Using filesort

I have two simple tables: CREATE TABLE cat_urls ( Id int(11) NOT NULL AUTO_INCREMENT, SIL_Id int(11) NOT NULL, SiteId int(11) NOT NULL, AsCatId int(11) DEFAULT NULL, Href varchar(2048) NOT NULL, ReferrerHref varchar(2048) NOT NULL…
Pavel
  • 61
  • 1
  • 2
6
votes
4 answers

MySql refuses to use index

I'm new to query optimizations so I accept I don't understand everything yet but I do not understand why even this simple query isn't optimized as expected. My…
Tin
  • 699
  • 1
  • 6
  • 19
6
votes
4 answers

How do you force a query in MySQL to use an index?

I am trying to improve the performance of a hammered wordpress DB by adding indexes to queries that appear in the slow query log. In MS SQL you can use query hints to force a query to use an index but it is usually quite easy to get a query to use…
Monkey Magix
  • 163
  • 2
  • 3
  • 10
6
votes
2 answers

Filtering before Join or after Join in SQL

I have two queries to get the same data, they more or less have the same execution time. SELECT name AS prog_name,d.chap_name,d.vid_name,d.idvideo FROM program JOIN (SELECT name AS chap_name,chapter.idprogram, c.vid_name,c.idvideo FROM chapter…
Jibran K
  • 885
  • 7
  • 20
5
votes
2 answers

How do I know what's behind the `auto_key0` index in MySQL?

Saying I have this query: EXPLAIN SELECT * FROM ( SELECT "A" as a, i.n FROM (SELECT 1 AS n) AS i UNION ALL SELECT "B" as a, i.n FROM (SELECT 1 AS n) AS i) AS t WHERE a = "B"; MySQL says id select_type table partitions type …
Xenos
  • 3,351
  • 2
  • 27
  • 50
5
votes
1 answer

sort describe table foo output by field alphabetically

I have a sizeable table that I'm actively working on and modifying columns of. It'd be handy if I could sort a describe table by Field name alphabetically to quickly find the definition for a column. The docs for mysql describe nor explain mention…
user3791372
  • 4,445
  • 6
  • 44
  • 78
5
votes
1 answer

pymongo aggregate don't allow explain option

I succesfully run: result = my_col.aggregate(my_pipeline, allowDiskUse=True) Now when I try: result = my_col.aggregate(my_pipeline, allowDiskUse=True, explain=True) it fails saying: pymongo.errors.ConfigurationError: The explain option is not…
Christian Arnault
  • 101
  • 1
  • 8
5
votes
1 answer

Understanding the result of EXPLAIN in MySQL

I have two separated queries which have identical outputs. Now I'm trying to understand which one is better? Query1: | id | select_type | table | type | possible_keys | key | key_len | ref | rows | …
Shafizadeh
  • 9,960
  • 12
  • 52
  • 89
5
votes
1 answer

Explain - inserts only one row

I'm trying to manually save optimizer plan for further analysis, like this: do $$ declare tmp text; begin explain select * from public.some_table where 1=2 into tmp; insert into public.plans(plan) values (tmp); end; $$ But when I select it…
JustMe
  • 2,329
  • 3
  • 23
  • 43
5
votes
1 answer

Why is MySQL showing index_merge on this query?

I have what seems like a fairly simple table structure, however MySQL is defaulting to a less than optimal index_merge on a simple query. Here's the table structure: CREATE TABLE IF NOT EXISTS `event_log` ( `event_id` int(11) NOT NULL…
a coder
  • 7,530
  • 20
  • 84
  • 131
4
votes
3 answers

MySQL explain output

Does anybody know the difference between Using Index and Using where; Using index in mysql's explain output (in Extra)? Reproduction: CREATE TABLE `tmp_t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL DEFAULT '0', `b`…
Zaar Hai
  • 9,152
  • 8
  • 37
  • 45
1 2
3
…
39 40