Yeah. The subquery sometimes creates temporary tables, so the performance is
worse than join. Just leaving it.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com
2010/12/8 Aaron Tur
Thanks Gavin. Rewriting the query to not use the subselect solved the problem!
On Tue, Dec 7, 2010 at 11:33 AM, Gavin Towey wrote:
> 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
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-
From: A
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" that mysqld applies to
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)
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
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/
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
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 indexes or it calculate
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
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
> >
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
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 also explain why
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
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
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
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?
David Sparks <[EMAIL PROTECTED]> wrote:
> I have a query that is taking days to complete (not good). If I change
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
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| Extra |
+---
"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
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 index if I remember correctly. Mike
- Original Message -
From: "Gabriel PREDA" <[EMAIL PROTECTED]>
To:
Sent: February 17,
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
> Subject: EXPLAIN: Select table
Hi Chris,
> Hmm
>
> The question is, does MySQL's optimiser do enough planing to result in a
> tree of any non-trivial interest?
I don't know - apparently, you think it doesn't?
> I love MySQL as much as the next geek with a significant other that
> loves dolphins, but I'm not sure that MySQ
Hmm
The question is, does MySQL's optimiser do enough planing to result in a
tree of any non-trivial interest?
I love MySQL as much as the next geek with a significant other that
loves dolphins, but I'm not sure that MySQL 4.0 would provide a lot of
data for funky tree-drawing (MS SQL tool
On Fri, Sep 19, 2003 at 10:20:34PM -0700, Hsiu-Hui Tseng wrote:
> These 2 are the queries
>
> explain select * from user where user_id = 123;
> -> where user_id is the primary key of user table with 2178576 rows.
Is there a user with user_id 123?
--
Jeremy D. Zawodny | Perl, Web, MySQL, L
513726 rows.
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Friday, September 19, 2003 3:13 PM
To: Hsiu-Hui Tseng
Cc: [EMAIL PROTECTED]
Subject: Re: explain
On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote:
> Hello,
>
> When I do a explain on
Hi,
- Original Message -
From: "Jeremy Zawodny"
Cc: <[EMAIL PROTECTED]>
Sent: Friday, September 19, 2003 5:12 PM
Subject: Re: explain
> On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote:
> > Hello,
> >
> > When I do a ex
On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote:
> Hello,
>
> When I do a explain on a query, I got the following:
> +-+
> | Comment |
> +-
No that says that it can figure out the where and prob wont use a key.
The like is probably the cause.
If it was going to use the primary key then the FIELD Key_used
(something like that) would say PRIMARY.
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http:
Robert,
Monday, November 04, 2002, 12:39:34 AM, you wrote:
RGp> I've tried an EXPLAIN of the SELECT below.
RGp> Please can anyone tell me why it's
RGp> "Impossible WHERE noticed after reading const tables"
RGp> ?
RGp> I've droped and created the table - but the error/message occurs.
RGp> When I
Ken Menzel writes:
> Hi Guys,
>I would like to request an additional feature with EXPLAIN.
> EXPLAIN does not run the query guesses on how the query would be run.
> This is not always accurate. The slow-log however logs the actual
> information the query was run with (like explain only the tru
At 04:09 PM 2/18/02, Chris Boget wrote:
> > I couldn't find anything like this in my MySQL book or in the on line help.
>
>Could this be what you are looking for?
>
>http://www.mysql.com/doc/E/X/EXPLAIN.html
>
>Not sure exactly what you need to know...
>
>Chris
Sorry if I wasn't clear.
I've read
> I couldn't find anything like this in my MySQL book or in the on line help.
Could this be what you are looking for?
http://www.mysql.com/doc/E/X/EXPLAIN.html
Not sure exactly what you need to know...
Chris
-
Before posting
At 15:51 -0800 2/14/02, Brian Warn wrote:
>When I run the code below, information about the first column in each
>table in my list is excluded. Any ideas why?
Why are you reading a row into @row_ary?
I bet you're thinking that row will correspond to the row of column
headers that you'd see wer
On 14 Feb 2002, at 15:51, Brian Warn wrote:
> When I run the code below, information about the first column in each
> table in my list is excluded. Any ideas why?
[snip]
> @row_ary = $sth->fetchrow_array;
You've read that information into @row_ary, but then you never do
anything with it
In the last episode (Jul 20), Ravi Raman said:
> ok, first actual question to the list, hope someone can help:
> the following query -
>
> select pn.number, c.ID, c.cost, c.days_valid, c.description from
> pinnumbers pn, cardtype c where pn.cardtypeID = c.ID and pn.tmaster = 0
> order by c.ID, pn.
Hi
Two points:
- Any multi column index a,b,c also indexes a and a,b
- In using an index, efficiency is important, and if you have two indexes a,b,c and
a,d,b , when you hit data
from columns a+b+d, the database engine has to work out if accessing the data from
a+b is more efficient
than
Dimitar Boiadjiev wrote:
>
> Hi all,
> I've been working on optimizing my query but no success. Concerning the
> type of query shown by "explain select", about join type "ALL" in the
> manual is written: "This is normally not good if the table is the first
> table not marked const, and usually ve
38 matches
Mail list logo
