Re: About EXPLAIN: Extra column information

2014-05-29 Thread Claudio Nanni
Hi, "Can anybody explain me the difference between "using index", "using index condition" and "using where" in the EXTRA column information of EXPLAIN query?" using index: the columns selected are part of an index that is used to return the results, ther

About EXPLAIN: Extra column information

2014-05-29 Thread Alfonso Maestro Lira
Can anybody explain me the difference between "using index", "using index condition" and "using where" in the EXTRA column information of EXPLAIN query? I have read the official doc but i am confused. Thank you.

Re: explain shows type = ALL for indexed column

2010-12-22 Thread 杨涛涛
.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html > > > > If you have further questions after doing that, show the table > structures, the query, and the explain output. > > > > -- > Aaron Turner > http://synfin.net/ Twitter: @synfinatic > http://tcprepla

Re: explain shows type = ALL for indexed column

2010-12-07 Thread Aaron Turner
g-subqueries.html > > If you have further questions after doing that, show the table structures, > the query, and the explain output. > -- Aaron Turner http://synfin.net/         Twitter: @synfinatic http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Windows

RE: explain shows type = ALL for indexed column

2010-12-07 Thread Gavin Towey
Mysql often handles subqueries poorly. It's best to rewrite that as a JOIN instead: http://dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html If you have further questions after doing that, show the table structures, the query, and the explain output. -Original Message-

explain shows type = ALL for indexed column

2010-12-07 Thread Aaron Turner
Basically, I'm doing a: select FROM Database1.table1 WHERE indexed_field IN (Select field from Database2.table2, ); It's taking about 40sec to execute where table1 (InnoDB) only has about 33k records and my subselect is returning about 600 records. Explain shows that it's d

Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables

2010-02-25 Thread Baron Schwartz
Hello, On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang wrote: > I have the following query. Note that the nested query has no > dependencies on the outer one, yet mysql reports it as dependent. Do an EXPLAIN EXTENDED followed by SHOW WARNINGS. You will see the "optimization" tha

Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables

2010-02-25 Thread Perrin Harkins
On Thu, Feb 25, 2010 at 2:48 AM, Dan Nelson wrote: > IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely > efficient); Yes, I meant to say IN/NOT IN subqueries, not value lists. > it's subqueries in general that are killers. Subqueries in the FROM clause (aka derived tables)

Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables

2010-02-25 Thread Johan De Meersman
On Thu, Feb 25, 2010 at 8:48 AM, Dan Nelson wrote: > IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely > efficient); it's subqueries in general that are killers. > If the dependent subquery is nothing but index lookups, it's still blazingly fast, though :) I just optimized

Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables

2010-02-24 Thread Dan Nelson
In the last episode (Feb 24), Perrin Harkins said: > On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang wrote: > > Any ideas on how to optimize this by convincing mysql to see the > > independence use a const join? > > http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/

Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables

2010-02-24 Thread Perrin Harkins
On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang wrote: > Any ideas on > how to optimize this by convincing mysql to see the independence use a > const join? http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ You need to rewrite as a join or use a FROM subquery. You

EXPLAIN says DEPENDENT SUBQUERY despite no free variables

2010-02-24 Thread Yang Zhang
), whereas I know that the subquery yields only 50 tuples, so a const join would've made more sense. Any ideas on how to optimize this by convincing mysql to see the independence use a const join? (This is in mysql 5.4.3 beta.) Thanks in advance. mysql> explain select thread_id,

MySQL University session on February 4: Optimizing Queries with EXPLAIN

2010-02-02 Thread Stefan Hinz
MySQL University: Optimizing Queries with EXPLAIN http://forge.mysql.com/wiki/Optimizing_Queries_with_Explain This Thursday (February 4th, 14:00 UTC), Morgan Tocker will talk about Optimizing Queries with Explain. Morgan was a technical instructor at MySQL and works for Percona today. For MySQL

RE: different type column and keys for EXPLAIN

2009-12-15 Thread Manish Ranjan
new index would work. -Original Message- From: Sergey Petrunya [mailto:pser...@askmonty.org] Sent: Monday, December 14, 2009 5:58 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN Manish, On Mon, Dec 14, 2009 at 05:33:43PM +0530, Man

Re: different type column and keys for EXPLAIN

2009-12-14 Thread Sergey Petrunya
checking for lastname 'clark'. > Rest everything is same with these two queries. However, the explain output > shows "ref" for the first query and uses only one key for the first query > whereas second query uses "index_merge" and both keys. > > ... >

Re: different type column and keys for EXPLAIN

2009-12-14 Thread Johan De Meersman
Behalf Of Johan De > Meersman > Sent: Monday, December 14, 2009 8:33 PM > To: Manish Ranjan > Cc: mysql@lists.mysql.com > Subject: Re: different type column and keys for EXPLAIN > > > > I don't think there's an actual problem as such, the optimiser is just > maki

RE: different type column and keys for EXPLAIN

2009-12-14 Thread Manish Ranjan
Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN Heh. Try running "analyze table", so the index stats are correct. If that doesn't help, you may have stumbled upon an optimizer glitch, or maybe there's something happening that I'm not se

RE: different type column and keys for EXPLAIN

2009-12-14 Thread Manish Ranjan
9 8:33 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN I don't think there's an actual problem as such, the optimiser is just making a decision to merge the lastname and firstname indices for the second query. At a guess, I'd say

Re: different type column and keys for EXPLAIN

2009-12-14 Thread Johan De Meersman
ISAM. > > > > Please refer to the below two statements. First query is checking for > lastname 'clarke' where as second query is checking for lastname 'clark'. > Rest everything is same with these two queries. However, the explain output > shows "ref&q

different type column and keys for EXPLAIN

2009-12-14 Thread Manish Ranjan
I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. Please refer to the below two statements. First query is checking for lastname 'clarke' where as second query is checking for lastname 'clark'. Rest everything is same with these two queries. However, the expla

Re: UNIX_TIMESTAMP - Can anyone explain this behavior?

2009-04-21 Thread Martijn Engler
Hi Keith, I'm not sure, but this might be DST that's in your way. Have you looked into that? Have a nice day, - Martijn On Mon, Apr 20, 2009 at 18:34, Keith Hughitt wrote: > Hi all, > > Does anyone know what is going on here: > > //Query: > > select UNIX_TIMESTAMP(TIMESTAMP('2003-01-01 00:00:0

UNIX_TIMESTAMP - Can anyone explain this behavior?

2009-04-20 Thread Keith Hughitt
Hi all, Does anyone know what is going on here: //Query: select UNIX_TIMESTAMP(TIMESTAMP('2003-01-01 00:00:00')) as first, UNIX_TIMESTAMP(TIMESTAMP('2003-10-05 00:00:00')) as second, UNIX_TIMESTAMP(TIMESTAMP('2004-01-01 00:00:00')) as third; ++++ | first

Optimizing query question, EXPLAIN SELECT ...

2008-11-25 Thread Thomas Thomas
Hi, I am pretty new in optimizing tables with index and may need some help. This is my query: EXPLAIN SELECT timestamp FROM Meting_INT_COPY WHERE blockid = '200811252000' ORDER BY timestamp DESC LIMIT 1 If I have an index(blockid), EXPLAIN will return the following informat

Re: Impossible WHERE in explain

2008-09-02 Thread Krishna Chandra Prajapati
tended recipient. > Sender does not necessarily endorse content contained within this > transmission. > > > > Date: Tue, 2 Sep 2008 02:05:00 +0530 > > From: [EMAIL PROTECTED] > > To: [EMAIL PROTECTED] > > Subject: Re: Impossible WHERE in explain > > C

RE: Impossible WHERE in explain

2008-09-01 Thread Martin Gainty
other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Tue, 2 Sep 2008 02:05:00 +0530> From: [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> Subject: Re: Impossible WHERE in explain> CC: mysql@lists.mysql.com> > I have

Re: Impossible WHERE in explain

2008-09-01 Thread Jim Lyons
I just tried this. I got the same results *until* I inserted a record that met the criteria. SInce the values are for a primary key, EXPLAIN could see there was no such entry and so reported that. If you actually execute the command, you would not see an error, just no rows returned. EXPLAIN

Re: Impossible WHERE in explain

2008-09-01 Thread Krishna Chandra Prajapati
hat works. > > Brent Baisley > > > On Sep 1, 2008, at 3:59 PM, Krishna Chandra Prajapati wrote: > > Hi, >> >> In the query below explain gives 'Impossible WHERE noticed after'. what >> does >> this mean. >> >> CREATE TABLE `user_c

Re: Impossible WHERE in explain

2008-09-01 Thread Brent Baisley
It might be because you you are comparing user_id to a string, when the field type is a decimal. Drop the quotes around the user_id search value and see if that works. Brent Baisley On Sep 1, 2008, at 3:59 PM, Krishna Chandra Prajapati wrote: Hi, In the query below explain gives

Impossible WHERE in explain

2008-09-01 Thread Krishna Chandra Prajapati
Hi, In the query below explain gives 'Impossible WHERE noticed after'. what does this mean. CREATE TABLE `user_cookie` ( `user_id` decimal(22,0) NOT NULL default '0', `param` varchar(128) NOT NULL default '', `value` varchar(128) default NULL,

Re: log-queries-not-using-indexes and "Using index" in the Extra col of Explain

2008-07-29 Thread MySQLForum MySQLForum
Isn't the choice in the the "type" col of Explain a "full index scan" rather than a full table scan ?My understanding was that the "ALL" stands for "full table scan ". Thanks, Mariella On Tue, Jul 29, 2008 at 11:03 AM, Mary Bahrami <[EMAIL PRO

RE: log-queries-not-using-indexes and "Using index" in the Extra col of Explain

2008-07-29 Thread Mary Bahrami
From: MySQLForum MySQLForum [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2008 6:35 AM To: mysql@lists.mysql.com Subject: log-queries-not-using-indexes and "Using index" in the Extra col of Explain Hi All, I have been using mysql 5.1.26-rc on Linux and configured to log queries that d

log-queries-not-using-indexes and "Using index" in the Extra col of Explain

2008-07-29 Thread MySQLForum MySQLForum
. Does the log-queries-not-using-indexes include also "index scans" ? E.G. EXPLAIN SELECT DISTINCT field1 FROM table1; ++-+-+---+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key

Re: Corruption? Performance issue + strange 'explain'

2008-01-22 Thread Dobromir Velev
7;Impossible WHERE noticed after reading const tables'? > > > > http://dev.mysql.com/doc/refman/4.1/en/explain.html > > MySQL has read all const (and system) tables and notice that the WHERE > > clause is always false. > > > > ie - no rows match that query

Re: Corruption? Performance issue + strange 'explain'

2008-01-21 Thread Daniel Kasak
s always false. > > ie - no rows match that query and so there's nothing to 'explain'. There must be a problem then. In this particular example, there were 3773 records returned by this select. > I'd suspect that the time is spent trying to check or clean up the > for

Re: Corruption? Performance issue + strange 'explain'

2008-01-21 Thread Chris
E CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=111073 DEFAULT CHARSET=latin1 If I do an 'explain' on a *select* with the above values, I get: mysql> explain select * fr

Corruption? Performance issue + strange 'explain'

2008-01-21 Thread Daniel Kasak
UTO_INCREMENT=111073 DEFAULT CHARSET=latin1 If I do an 'explain' on a *select* with the above values, I get: mysql> explain select * from TelecomAccountPosting where LocID = 19014 and InvDate = '20080115'; ++-+---+--+---+--+-

Re: explain

2007-06-06 Thread Baron Schwartz
Hola Ricardo, Ricardo Conrado Serafim wrote: Hi group, Someone can answer me if when I execute the "explain" in a select statement, the select is executed to get the parameters or this information came from other place? In other words, the explain covers the table and ind

explain

2007-06-06 Thread Ricardo Conrado Serafim
Hi group, Someone can answer me if when I execute the "explain" in a select statement, the select is executed to get the parameters or this information came from other place? In other words, the explain covers the table and indexes or it calculate based on numbers of rows, etc.

Could someone explain

2007-04-20 Thread Mikhail Berman
Dear List, We are running: mysql> status -- mysql Ver 14.12 Distrib 5.0.27, for unknown-freebsd6.0 (i386) using readline 5.0 Could someone explain the meaning or give us brief explanation of the following entries in err file on MySQL ser

Re: Need help on EXPLAIN in rating queries

2006-08-01 Thread Aleksandar Bradaric
Hi, > I am trying to JOIN 2 tables TBL1 and TBL2 on TBL1.fld_id > = TBL2.fld_id . And finally I filter out the results that > i need in the where clause using > > where TBL1.fld_col = 100; > > Running an EXPLAIN shows that it is an impossible where > condition. This may be

Need help on EXPLAIN in rating queries

2006-08-01 Thread Ratheesh K J
Helo all, I need explanation on EXPLAIN here. I am trying to JOIN 2 tables TBL1 and TBL2 on TBL1.fld_id = TBL2.fld_id . And finally I filter out the results that i need in the where clause using where TBL1.fld_col = 100; Running an EXPLAIN shows that it is an impossible where condition. This

Re: number of rows in EXPLAIN for unpacked vs packed tables

2006-07-01 Thread C.R.Vegelin
AIL PROTECTED]> To: "C.R.Vegelin" <[EMAIL PROTECTED]> Cc: Sent: Friday, June 30, 2006 4:36 PM Subject: Re: number of rows in EXPLAIN for unpacked vs packed tables Cor - Those numbers are an estimate, not a hard number, of how many rows MySQL thinks it will have to exmaine

Re: number of rows in EXPLAIN for unpacked vs packed tables

2006-06-30 Thread Dan Buettner
e has possibly been inserted, updated, and deleted from, causing the key information to be less accurate. You could try running a CHECK TABLE EXTENDED on the old one, which should update all the key info, and then checking your EXPLAIN results again. Be aware it could take a while for a large table and

number of rows in EXPLAIN for unpacked vs packed tables

2006-06-30 Thread C.R.Vegelin
Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT

Explain explanation (was: More LEFT JOIN newbie fun!)

2006-05-11 Thread Chris Sansom
= r.id and ta.ida = tga.ida) to my query, and that both got the same results. Having added two pairs and tried both versions, I find the second one is fractionally (but I mean /really/ fractionally) faster, as reported by phpMyAdmin. However, when I do an Explain on both versions, the first shows

Re: Unclear about key_len in EXPLAIN output

2006-02-03 Thread James Harvard
As well, if the key that EXPLAIN uses is multi-part, you would know >from the length of the fields. You have to use some deductive >reasoning, but I don't think it's really murky*shrug* If you have a key combined from different integer column types and text columns then it

Re: Unclear about key_len in EXPLAIN output

2006-02-03 Thread sheeri kritzer
The manual is large and vast, I couldn't find the text you quoted on the page I sent. What page did you find that text on? As well, if the key that EXPLAIN uses is multi-part, you would know from the length of the fields. You have to use some deductive reasoning, but I don't think i

Re: Unclear about key_len in EXPLAIN output

2006-02-02 Thread James Harvard
'That other statement' that I quoted is actually just part of the paragraph from the manual that you quoted! My point was that it doesn't say what units the key length is given in or explain _how_ to determine the number of parts used. However the more I think about it the mor

Re: Unclear about key_len in EXPLAIN output

2006-02-02 Thread sheeri kritzer
according to: http://dev.mysql.com/doc/refman/5.0/en/explain.html "The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actu

Unclear about key_len in EXPLAIN output

2006-02-01 Thread James Harvard
According to the manual "the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses." However, it doesn't specify quite how one can determine that. It _looks_ like the number of bytes in the key (or key part) that is used - is that correct? TIA, Ja

Re: unexpected EXPLAIN result with subselect

2005-09-14 Thread Michael Stassen
Matthias Pigulla wrote: Hi all, can someone explain me why this query gets executed the way it is? I simplified it as much as possible and think it's a conceptual/logical thing, so I'll omit - at least for now - the table definitions and sample data for brevity. -- "Superquery&

Re: unexpected EXPLAIN result with subselect

2005-09-14 Thread Gleb Paharenko
Hello. See: http://bugs.mysql.com/bug.php?id=12106 "Matthias Pigulla" <[EMAIL PROTECTED]> wrote: > Hi all, > > can someone explain me why this query gets executed the way it is? I > simplified it as much as possible and think it's a conceptual/lo

Re: unexpected EXPLAIN result with subselect

2005-09-14 Thread Felix Geerinckx
On 14/09/2005, "Matthias Pigulla" wrote: > However - why do we need the dependent subquery at all? The subquery > can be executed on its own, as it does not depend on any information > of the "superquery". This is a subquery optimizer bug. See http://bugs.mysql.com/bug.php?id=10309 -- felix -

unexpected EXPLAIN result with subselect

2005-09-14 Thread Matthias Pigulla
Hi all, can someone explain me why this query gets executed the way it is? I simplified it as much as possible and think it's a conceptual/logical thing, so I'll omit - at least for now - the table definitions and sample data for brevity. -- "Superquery" EXPLAIN SELECT

Re: explain not explaining long running query?

2005-08-09 Thread Gleb Paharenko
Hello. > State: Sending data MySQL server shouldn't spend several days in state of sending one row (your query should return only one row :) to the client. Server doesn't work properly and steps like upgrade or switching to the official binaries might be helpful. > > The query h

Re: explain not explaining long running query?

2005-08-08 Thread SGreen
David Sparks <[EMAIL PROTECTED]> wrote on 08/08/2005 12:20:41 PM: > Hi all! > > Gleb Paharenko wrote: > > Hello. > > > > > > > >>I have a query that is taking days to complete (not good). If I change > > > > > > Really, not good. What does SHOW PROCESSLIST report about the thread of > >

Re: explain not explaining long running query?

2005-08-08 Thread David Sparks
Hi all! Gleb Paharenko wrote: > Hello. > > > >>I have a query that is taking days to complete (not good). If I change > > > Really, not good. What does SHOW PROCESSLIST report about the thread of > this query? The query has been running for ~5 days now: Id: 27977 User: root Ho

Re: explain not explaining long running query?

2005-08-05 Thread Jason Pyeron
On Fri, 5 Aug 2005, Joerg Bruehe wrote: Hi Shawn, all! Still, I would consider to replace X > A AND X < B by X BETWEEN (A+1) AND (B-1) for the reasons I gave in my original post (provided we talk about integral values). the field is a DOUBLE, so you can't could this a

Re: explain not explaining long running query?

2005-08-05 Thread Joerg Bruehe
Hi Shawn, all! [EMAIL PROTECTED] wrote: Joerg Bruehe <[EMAIL PROTECTED]> wrote on 08/05/2005 02:08:35 PM: [[...]] Have you considered using BETWEEN? [[...]] Actually, no, he cannot substitute BETWEEN as BETWEEN is _inclusive_ of the boundary conditions (a closed interval). In this case he

Re: explain not explaining long running query?

2005-08-05 Thread SGreen
Joerg Bruehe <[EMAIL PROTECTED]> wrote on 08/05/2005 02:08:35 PM: > Hi David, all! > > David Sparks wrote: > > I have a query that is taking days to complete (not good). If I change > > the query so that it selects less rows it runs fast. > > I fear I have no decisive hint for this (sorry), bu

Re: explain not explaining long running query?

2005-08-05 Thread Joerg Bruehe
Hi David, all! David Sparks wrote: I have a query that is taking days to complete (not good). If I change the query so that it selects less rows it runs fast. I fear I have no decisive hint for this (sorry), but still ... [[...]] mysql> select count(*) from msgs where message_id > 112

Re: explain not explaining long running query?

2005-08-05 Thread Gleb Paharenko
If I change > the query so that it selects less rows it runs fast. > > I ran an explain on both queries and it didn't give any hints as to why > the one query is taking days to run. In fact explain knows how many > rows each query will examine. > > P

explain not explaining long running query?

2005-08-04 Thread David Sparks
I have a query that is taking days to complete (not good). If I change the query so that it selects less rows it runs fast. I ran an explain on both queries and it didn't give any hints as to why the one query is taking days to run. In fact explain knows how many rows each query will ex

Re: 'Single quotation mark' makes different results for 'explain select'

2005-06-08 Thread Dan Nelson
In the last episode (Jun 08), Ying Lu said: > By the way, I am using 4.0.18-log on i686.linux2.6.10. > >I did the following two explain select ... According to whether I put > >the single quotation mark or not, I will get totally different results: > > > >1. Wi

'Single quotation mark' makes different results for 'explain select'

2005-06-08 Thread Ying Lu
Greetings, I did the following two explain select ... According to whether I put the single quotation mark or not, I will get totally different results: 1. Without single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE *T1.STUDID = 099 AND T1.Prog_link =

Re: 'Single quotation mark' makes different results for 'explain select'

2005-06-08 Thread Ying Lu
By the way, I am using 4.0.18-log on i686.linux2.6.10. Greetings, I did the following two explain select ... According to whether I put the single quotation mark or not, I will get totally different results: 1. Without single quotation mark: explain SELECT * FROM test T1 force index

RE: Can someone explain???? strange query!! THANKS!!

2005-04-29 Thread nngau
Ahh okay the column was cut off or something \G shows it! Thanks!! -Original Message- From: Rich Lafferty [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 10:28 AM To: mysql@lists.mysql.com Subject: Re: Can someone explain strange query!! On Fri, Apr 29, 2005 at 10:46:16AM

Re: Can someone explain???? strange query!!

2005-04-29 Thread Rich Lafferty
On Fri, Apr 29, 2005 at 10:46:16AM -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I did two query, can someone explain what happened? Not with what you included: > | customer_num | title | first_name | middle_name | last_name | email | > dayphone | evenp

RE: Can someone explain???? strange query!!

2005-04-29 Thread Jay Blanchard
[snip] I did two query, can someone explain what happened? First query: mysql> select * from customer_billing where first_name="shad"; mysql> select first_name, customer_num from customer_billing where first_name="shad"; Now these query is the same except f

Can someone explain???? strange query!!

2005-04-29 Thread nngau
I did two query, can someone explain what happened? First query: mysql> select * from customer_billing where first_name=&q

explain says: no index is used - however it exists....

2005-04-12 Thread Balazs Laszlo
Hi! I have a table with the following fields: varchar(200) title, varchar(200) link, text content I made an index on the table, named "common", with type fulltext, which contains the title, link, content fields. When i execute the explain on the following select: (SELECT * FR

Re: Explain and indexes

2005-03-09 Thread Jigal van Hemert
From: "Terry Spencer" > An index exists on all three columns referred to, in addition to a > combination of del and signoff. > > The indexes are listed as possible keys, but none used by the query; key = > null. Can anyone suggest why? How can I optimise this? How many records are there in the tab

Explain and indexes

2005-03-09 Thread Terry Spencer
Im trying to speed up a query. select project_id from timesheet ts where ts.del is null and signoff = 'A' The output of explain is detailed below. ++-+---+--+-+--+ | id | select_type | table | type | possible_keys

Re: Simple explain

2005-03-03 Thread SGreen
"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 03/03/2005 02:38:54 PM: > Hello, > > Does this mean a key is not being used? > > mysql> explain select vl_ts from view_log where vl_uid='bb'; > +--+--+---+--+---

Simple explain

2005-03-03 Thread Jonathan Mangin
Hello, Does this mean a key is not being used? mysql> explain select vl_ts from view_log where vl_uid='bb'; +--+--+---+--+-+--+--++ | table| type | possible_keys | key | key_len | ref |

Re: EXPLAIN does not explain the WHERE clause

2005-03-01 Thread gerald_clark
Christopher Malton wrote: When I use the statement: EXPLAIN SELECT * FROM workunits WHERE Sent>0 It returns +---+--+---++-++-++ | table | type | possible_keys | key| key_len | ref| rows| Ex

EXPLAIN does not explain the WHERE clause

2005-03-01 Thread Christopher Malton
When I use the statement:   EXPLAIN SELECT * FROM workunits WHERE Sent>0   It returns   +---+--+---++-++-++ | table | type | possible_keys | key    | key_len | ref    | rows    | Ex

Re: EXPLAIN: Select tables optimized away

2005-02-17 Thread Gabriel PREDA
"O'K Web Design" <[EMAIL PROTECTED]> Subject: Re: EXPLAIN: Select tables optimized away > Hi > > Counts are extremely fast and since you have no WHERE statement, it > takes the count value straight from the internals and does not look at the > tables or an inde

Re: EXPLAIN: Select tables optimized away

2005-02-17 Thread O'K Web Design
ent: February 17, 2005 6:15 AM Subject: EXPLAIN: Select tables optimized away > MySQL 4.1.10 > > What does "Select tables optimized away" mean ? > > mysql> explain SELECT COUNT(*) AS total FROM members_twining_main; > | 1 | SIMPLE | NULL | NULL | NULL

RE: EXPLAIN: Select tables optimized away

2005-02-17 Thread Andy Eastham
Gabriel, I think it means that this count can be done from an index, so there's no need to access the actual table at all. Andy > -Original Message- > From: Gabriel PREDA [mailto:[EMAIL PROTECTED] > Sent: 17 February 2005 11:16 > To: mysql@lists.mysql.com > Subj

EXPLAIN: Select tables optimized away

2005-02-17 Thread Gabriel PREDA
MySQL 4.1.10 What does "Select tables optimized away" mean ? mysql> explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- MySQL General Mai

Re: Can someone please explain this?

2004-10-07 Thread Peter Brawley
>From 5.0.1, I get ... e c b 1 1 1 2 2 2 3 3 3 NULLNULL4 PB - Original Message - From: Ian Sales To: [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 4:21 AM Subject: Can someone please explain this? During s

Re: Can someone please explain this?

2004-10-07 Thread Ian Sales
Michael Stassen wrote: It's a bug: Depending on which bug report you look at, this result is either because mysql treats this as a nested join, or because mysql does not support nested joins. Frankly, I find t

Re: Can someone please explain this?

2004-10-07 Thread Michael Stassen
|2 |2 | |3 |3 |3 | |4 | NULL | NULL | +--+--+--+ 4 rows in set (0.13 sec) Michael Ian Sales wrote: During some training on SQL recently, the following occurred. No one can explain it. The training used five tables A, B, C , D and E, each with a single int column

Can someone please explain this?

2004-10-07 Thread Ian Sales
During some training on SQL recently, the following occurred. No one can explain it. The training used five tables A, B, C , D and E, each with a single int column. Table A has column a with values 1, 2, 3, 4, 5 Table B has column b with values 1, 2, 3, 4 Table C has column c with values 1, 2

Re: please explain why this query isn't optimized

2004-09-07 Thread Egor Egorov
Oops, haven't noticed that these are InnoDB tables. The behavior of optimization and even explain are much different for InnoDB tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensit

Re: please explain why this query isn't optimized

2004-09-07 Thread Egor Egorov
David T-G <[EMAIL PROTECTED]> wrote: > % Well, actually, there are 2.878 Meg rows, or 2878k. > > FYI, you're both right. Americans write numbers as x,xxx,xxx.xx while > Europeans typically write them as x.xxx.xxx,xx (dot as thousands > separator and comma as decimal separator). But programmers

Re: please explain why this query isn't optimized

2004-09-06 Thread David T-G
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bill, et al -- ...and then Bill Easton said... % % Well, actually, there are 2.878 Meg rows, or 2878k. FYI, you're both right. Americans write numbers as x,xxx,xxx.xx while Europeans typically write them as x.xxx.xxx,xx (dot as thousands separator

Re: please explain why this query isn't optimized

2004-09-06 Thread Bill Easton
ECT MAX(changed) as maximum FROM archived_stats having maximum > 0; +-+ | maximum | +-+ | 8 | +-+ 1 row in set (0.00 sec) mysql> explain SELECT MAX(changed) FR

Re: please explain why this query isn't optimized

2004-09-06 Thread Egor Egorov
Dave Dyer <[EMAIL PROTECTED]> wrote: > Before I post it as a bug, perhaps someone can explain why > this query is not optimized to use the index (it has to examine all 287k rows). > > mysql> explain SELECT MAX(changed) FROM archived_stats where changed>0; &

RE: please explain why this query isn't optimized

2004-09-04 Thread Donny Simonton
n Nelson > Cc: Donny Simonton; [EMAIL PROTECTED] > Subject: Re: please explain why this query isn't optimized > > > > > >> Getting the same answer, from a simpler query, in infinitely > >> less time, just seems wrong to me. > > > >Ma

Re: please explain why this query isn't optimized

2004-09-04 Thread Dave Dyer
> >> Getting the same answer, from a simpler query, in infinitely >> less time, just seems wrong to me. > >Makes perfect sense. Simpler queries *are* easier to optimize, you >know :) Makes perfect sense. Thanks, I think the relevant points have been covered. -- MySQL General Mailing List Fo

Re: please explain why this query isn't optimized

2004-09-03 Thread Dan Nelson
In the last episode (Sep 03), Dave Dyer said: > At 05:39 PM 9/3/2004, Donny Simonton wrote: > >It would help if you would say how many entries do you have for > >changed =0 and how many are greater than 0. > > > >Since changed is a timestamp you should never get an entry of 0. So > >the query of c

Fwd: please explain why this query isn't optimized

2004-09-03 Thread Eric Bergen
MySQL is using the index in both of those. The first query where changed < 7890; should be very fast since explain says it only has to find one row. The second query, after mysql does a range scan on the index has to still examine 11551351 rows to find a max value. The last query SELECT

RE: please explain why this query isn't optimized

2004-09-03 Thread Dave Dyer
would get me the first entry > xxx. mysql> explain SELECT MAX(changed) FROM archived_stats where changed < 7890; ++---+---+-+-+--+--+--+ | table | type | possible_key

Re: please explain why this query isn't optimized

2004-09-03 Thread Eric Bergen
> > > -Original Message- > > From: Dave Dyer [mailto:[EMAIL PROTECTED] > > Sent: Friday, September 03, 2004 7:04 PM > > To: [EMAIL PROTECTED] > > Subject: please explain why this query isn't optimized > > > > > > Before I post it a

RE: please explain why this query isn't optimized

2004-09-03 Thread Donny Simonton
essage- > From: Dave Dyer [mailto:[EMAIL PROTECTED] > Sent: Friday, September 03, 2004 7:04 PM > To: [EMAIL PROTECTED] > Subject: please explain why this query isn't optimized > > > Before I post it as a bug, perhaps someone can explain why > this query is not op

please explain why this query isn't optimized

2004-09-03 Thread Dave Dyer
Before I post it as a bug, perhaps someone can explain why this query is not optimized to use the index (it has to examine all 287k rows). mysql> explain SELECT MAX(changed) FROM archived_stats where change

explain strange result using keys

2004-06-08 Thread Alberto Mucignat
Working around optimizing some queries, I tried to get a better key improvement. This is what I get: mysql> explain select ID,reply,nome,email,dataora,titolo,testo,status,thread from board where articolo=50966 AND status<2 and (thread<102741 and thread>101548) ORDE

Re: Used EXPLAIN, have indexes - query still surprisingly slow

2004-05-26 Thread Robert J Taylor
Andy Henshaw wrote: I have the following query that takes anywhere from 1 to 3 seconds to run. I would expect it to run in less than 1/2 a second (and I really need it to do so). I've added the appropriate indices and tried EXPLAIN and ANALYZE; but, I'm not getting anywhere.

  1   2   >