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

How can I optimize this slow query with multiple joins and sub queries

I have to optimise a query that someone else wrote, and I am struggling. Its taking sometimes in excess of 12 seconds to complete! I have an SQL fiddle here, however theres no data in there yet. I think the amount of data is partially the cause of…
Horse
  • 3,023
  • 5
  • 38
  • 65
3
votes
1 answer

Almost equal queries give different execution times

I have these two queries: SELECT SQL_NO_CACHE DISTINCT(type) FROM actions LIMIT 0, 30; and SELECT SQL_NO_CACHE type FROM actions GROUP BY type LIMIT 0, 30; If I don't use the LIMIT clause, the execution times are equal. On the other hand, in my…
marcosh
  • 8,780
  • 5
  • 44
  • 74
3
votes
1 answer

What is a "bitmap index"?

I have a PostgreSQL query that is taking longer than I'd like. I'm looking at the output of EXPLAIN ANALYZE and it mentions a Bitmap Index Scan. I've been searching the 'net and reading for about 10 minutes, but I cannot figure out: Is a Bitmap…
Phrogz
  • 296,393
  • 112
  • 651
  • 745
3
votes
2 answers

Why MongoDB cannot use a compound index that is much similar(not exact) to the query?

Consider the below Mongo index strategy and the query, Index: db.collec.ensureIndex({a:1,b:1,c:1}); Query: db.collec.find({"a":"valueA"},{"_id":0,"a":1,"c":1}).sort({"c":-1}).limit(150) The explain on the above query returns: /* 0 */ { …
vivek_jonam
  • 3,237
  • 8
  • 32
  • 44
3
votes
1 answer

MongoDb geoIntersects doesn't use IndexOnly

I have a collection called search2 with about 20000 documents like this: { "loc": { "type": "Polygon", "coordinates": [ [ [ 43.78526674007639, 11.14739998758569 …
MarcoBiagi
  • 163
  • 7
3
votes
5 answers

Why does the same exact query produce 2 different MySQL explain results?

I have a simple SELECT * From tv Where Client = 'ABCD' query and when I do an EXPLAIN EXTENDED, it gives me two different results. When executing the query, one of them take a few milliseconds, while the other takes about 3 seconds. Why would it…
Xaisoft
  • 45,655
  • 87
  • 279
  • 432
3
votes
1 answer

MySQL EXPLAIN rows amount much higher on simple WHERE condition

I've been working on optimisations for a while now, but I really can't crack this one. The query: SELECT `field` FROM `table` WHERE `column_id` = 12341 The index: ALTER TABLE `table` ADD INDEX `column_id` (`column_id`); Now the explain of the…
Lennaert van Dijke
  • 138
  • 5
3
votes
1 answer

MySQL examining too many rows when WHERE clause has more than one IN

I'm currently using MySQL 5.6.10. My actual query is more complicated, but here is a simple way to reproduce the problem. I know the query below is useless (select id from x where id in (select id from x...)), but it proves my point. I created this…
Bumptious Q Bangwhistle
  • 4,689
  • 2
  • 34
  • 43
3
votes
1 answer

understanding perf of mysql query using explain extended

I am trying to understand performance of an SQL query using MySQL. With only indexes on the PK, the query failed to complete in over 10mins. I have added indexes on all the columns used in the where clauses (timestamp, hostname, path, type) and the…
tom
  • 303
  • 2
  • 13
3
votes
1 answer

Why so different query plans?

I have table reviews_article (1508 rows). I don't understand, why two queries with only different offset have thus different query plans. Can you explain me, please? CREATE TABLE "reviews_article" ( "id" serial NOT NULL PRIMARY KEY, …
Vladislav
  • 1,318
  • 16
  • 15
3
votes
4 answers

Slow MySQL Query

I have a query in MySQL (used in a stored procedure) which searches by name and another field. When I use different combinations of these search parameters, I get quick results (between 1 and 2s) but with some particular values, I get a query which…
Timothy Mifsud
3
votes
2 answers

Mysql query optimisation, EXPLAIN and slow execution

Having some real issues with a few queries, this one inparticular. Info below. tgmp_games, about 20k rows CREATE TABLE IF NOT EXISTS `tgmp_games` ( `g_id` int(8) NOT NULL AUTO_INCREMENT, `site_id` int(6) NOT NULL, `g_name` varchar(255) NOT…
Horse
  • 3,023
  • 5
  • 38
  • 65
2
votes
3 answers

Is it possible to modify data with an SQL statement that starts with `EXPLAIN EXTENDED ...`?

I read about MySQL EXPLAIN safety in the StackOverflow question about it, and one of the later commenters (@tye) said: people figuring out how to make updates via "explain" It's a question that I've been thinking about today -- is there any way to…
Remi
  • 33
  • 4
2
votes
0 answers

Postgres query runs 1000X slower when combined with UNION ALL than when queries run separate

I have two queries that when run separately, each take 1-10 seconds to execute. When I combine their results with UNION ALL, the execution time jumps up to 4500 seconds (over 1 hour)! I have run the long query with EXPLAIN ANALYZE and it appears to…
Scott C.
  • 21
  • 2
2
votes
2 answers

PostgreSQL Parallel Query Only Works When ANALYZE EXPLAIN. without ANALYZE EXPLAIN, Single Process works

My PostgreSQL Version 13. and below is parallel related parameters. SELECT name, setting FROM pg_Settings WHERE name LIKE '%parallel%' name |setting| --------------------------------+-------+ enable_parallel_append …
seankim
  • 83
  • 6
1 2 3
…
39 40