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

MYSQL query EXPLAIN SELECT

EXPLAIN SELECT results for some tables has: type possible_keys key Extra table1 ref fl fl Using where table2 ref PRIMARY,variant variant Using where; Using index If would like to…
Ken Tang
  • 519
  • 3
  • 7
  • 20
0
votes
1 answer

Performace Lag in Mysql query

This mysql query takes 5 minutes 20 seconds to execute SELECT PROVIDER, COUNT(DISTINCT(NAME)) FROM Test WHERE NAME NOT IN (SELECT NAME FROM Test WHERE OPERATION = 'SIGN_IN' and Test.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE() -…
RBK
  • 77
  • 2
  • 8
0
votes
1 answer

indexed query, but still searching every row

I have the following mysql query select points_for_user from items where user_id = '38415'; explain on the query returns this id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE …
Scott Foster
  • 465
  • 3
  • 9
  • 17
0
votes
1 answer

joining table in mysql not using index properly?

I have four tables that I am trying to join and output the result to a new table. My code looks like this: create table tbl select a.dte, a.permno, (ret - rf) f0_xs_ret, (xs_ret - (betav*xs_mkt)) f0_resid, mkt_cap last_year_mkt_cap, betav…
Alex
  • 19,533
  • 37
  • 126
  • 195
0
votes
2 answers

Optimizing MySQL Queries: Is it always possible to optimize a query so that it doesn't use "ALL"

According to the MySQL documentation regarding Optimizing Queries With Explain: * ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and…
Runcible
  • 7,006
  • 12
  • 42
  • 62
0
votes
1 answer

Improving performance of a [mysql query with 'in clause']

I have a query that retrieves data from two tables. table1 has an int field, table1_id which is the primary key. table2 has two fields; table1_id, an int field (which references table1.table1_id) and field1, a bit field. There are three indexes on…
Tola Odejayi
  • 3,019
  • 9
  • 31
  • 46
0
votes
1 answer

Optimise query using filesort and temporary

Can anyone help me to optimise this query. It's taking a while to run: The su_pref table has almost 900,000 rows SELECT p.mykey, p.merchant_name, m.merchant_url, p.name, p.description, p.image_url, …
Amara
  • 213
  • 1
  • 5
  • 13
0
votes
1 answer

mysql using temporary table with subqueries, but not group by and order by

I have the following mysql query which is taking about 3 minutes to run. It does have 2 sub queries, but the tables have very few rows. When doing an explain, it looks like the "using temporary" might be the culprit. Apparently, it looks like the…
Phil Bartholo
  • 33
  • 4
-1
votes
1 answer

Multiple MATCH-AGAINST clauses results in no FULLTEXT index being used

I have the following two tables: CREATE TABLE `lname` ( `lnameid` binary(16) NOT NULL, `lid` binary(16) NOT NULL, `name` varchar(200) NOT NULL, `namerank` int(11) DEFAULT NULL, `score` float DEFAULT NULL, PRIMARY KEY (`lnameid`), KEY…
andynewman
  • 51
  • 5
-1
votes
2 answers

With same table structure there is different behavior of query plan on different environments

Case Inputs Mysql Version on both Prod and test: 5.7 Query: SELECT t1.id t1.accountId, t2.col1 FROM table1 t1 INNER JOIN table2 t2 ON (t2.Id = t1.id) WHERE 1 AND MOD(t1.id, 20) = 0 AND t1.col1 = 0 AND t1.col2 <=…
Rdba
  • 3
  • 3
-1
votes
1 answer

Numpy.ndarray' object has no attribute 'head'

i have a problem with my explainable model,the following happens: I define muy label enconding The last column, common name is a categorical value. Label Encode it to numerical values. label_encoder = LabelEncoder() x= merged_data_df.iloc[:, 1:14] …
jupiter
  • 1
  • 1
  • 2
-1
votes
1 answer

How can I optimize DB query?

My db query is taking 5 seconds instead of <1 second earlier and the culprit query is : explain extended select A,B,C from table flow where (status in ('X' , 'Y' , 'Z')) and priority=1 and created<=now() order by id asc limit…
user124
  • 423
  • 2
  • 7
  • 26
-1
votes
1 answer

Maps and sets please explain why this code has the output

Maps and sets please explain why this code has the output I am especially confused about what m[x] ++ and the out put 3:0 my understanding was that the set is (0,1,2,2,4,4) and m. insert(key,value) would record the map (0,2,4,4,8,8) which would make…
Bob Gax
  • 11
  • 2
-1
votes
2 answers

Optimize Mysql query with two joins with explain

I have a somewhat complex (to me) query where I am joining three tables. I have been steadily trying to optize it, reading how to improve things by looking at the EXPLAIN output. One of the tables person_deliveries is growing by one to two million…
syntax
  • 23
  • 6
-1
votes
1 answer

How to read costs in Postgres explain statements?

Here's an example explain from postgres: Aggregate (cost=55881.29..55881.30 rows=1 width=64) -> Nested Loop (cost=1509.25..55881.28 rows=1 width=32) -> Nested Loop (cost=1508.82..55880.82 rows=1 width=23) -> Nested…
Antonis Karvelas
  • 105
  • 1
  • 7
1 2 3
…
39 40