tuning and debugging.
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 24, 2007 2:35 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Need confirmation: Subselects are broken with regards to
index usage?
Hi Robert,
The way non-correlated
I think I'm discovering that sub-selects in MySQL are broken. Is that
true? It seems like you cannot have a sub-select without doing a table
scan -- even for a constant IN expression -- this because it gets
re-written as an EXISTS that executes for each row.
Is that true? Forcing an index
ON link.parentID=path.decendantId
WHERE (path.ancestorId = 1)
LIMIT 0,100;
Anyone have any ideas why this is the case?
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 24, 2007 1:11 PM
To: mysql@lists.mysql.com
Subject: Need confirmation: Subselects
?
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 24, 2007 1:11 PM
To: mysql@lists.mysql.com
Subject: Need confirmation: Subselects are broken with regards to index
usage?
I think I'm discovering that sub-selects in MySQL are broken. Is that
true
Are there any hard and fast rules for this? If someone has already
compiled a list I'd love to see it.
For example:
* When a subselect will eliminate duplicates a join might introduce.
Change:
SELECT DISTINCT Acl.*
FROM Acl
JOIN Link ON Link.childID = Acl.ID
JOIN Paths ON
to quote the manual:
Currently, you cannot delete from a table and select from the same table
in a subquery.
mysql select * from paths where id=(select max(ppathref) from paths);
++-++--+
| id | typeref | name | ppathref |
2005 19:53
To: mysql@lists.mysql.com
Subject: delete, where, and subselects...
to quote the manual:
Currently, you cannot delete from a table and select from the same table
in a subquery.
mysql select * from paths where id=(select max(ppathref) from paths
Hello everyone,
I have a problem with a few sql-queries because I have written the questions
with subselect statements and then will they not work on older MySQL DB:s. Can
any one help me to say haw to solve it without subselect!
Today looks the questions like this:
SELECT (SELECT
Please help, this must be easy for experinced MySQL users but for me as a
newbie isn't it. I have tried to find the answer on the things below in older
MySQL manuals but without any success...
I have a problem with a few sql-queries because I have written the questions
with
subselect
Joppe,
I have a problem with a few sql-queries because I have written the
questions
with subselect statements and then will they not work on older MySQL
DB:s.
Can any one help me to say haw to solve it without subselect!
Today looks the questions like this:
SELECT
(SELECT count(SUB.S_ID)
Joppe,
Another question is how do I do when I want to compare two tables,
normally is it no problem to get a list with rows that exist i both
tables, but how do I do if I want to to have the diffrence listed
instead, the rows that only appears in one of the tables?
SELECT *
FROM tblA
LEFT JOIN
subselects
* Petre Agenbag
[...]
This works:
select distinct name, max(id) as mid from table group by name
but it only returns the name and the highest id for such a name.
if I try this:
select distinct name, max(id) as mid, anything_else from table group by
name
it returns the FIRST
* Petre Agenbag
The distinct clause will return only one occurrance of the
implicated field.
distinctrow would do the same, but for an entire row. what I am
looking for
is something like distinctset(field1,field2,field7) , is this what the
MAX-CONCAT trick attempts to do?
Not exactly, but
Hi List
Can anyone help me to do the following without the need of subselects:
I have a table that contains rows where mostly, the only common field is
the name. The rest of the data fields, even though they are related to
the name, differs from row to row ( they are things like the address
etc
* Petre Agenbag
[...]
This works:
select distinct name, max(id) as mid from table group by name
but it only returns the name and the highest id for such a name.
if I try this:
select distinct name, max(id) as mid, anything_else from table group by
name
it returns the FIRST
Hi List,
My head is not on right today.
I have something like this:
main_table
id namemain_type
1 joe type1
2 johntype2
3 jacktype1
table type1
id_type1id action
1 1 2003-07-01
2 1 2003-08-03
3
Im have a problem / misunderstanding with the 4.1 release. Im having issues
with indexes related to 'IN' vs '=' in subselects.
assume the subselect returns 1 value
select SQL_NO_CACHE user_id from table_a where user_id in (select
SQL_NO_CACHE user_id from table_b where ... blah)
9.5 seconds
On Mon, Jun 09, 2003 at 12:46:32PM -0500, Christopher Knight wrote:
Im have a problem / misunderstanding with the 4.1 release. Im having issues
with indexes related to 'IN' vs '=' in subselects.
assume the subselect returns 1 value
select SQL_NO_CACHE user_id from table_a where user_id
On Thu, Feb 06, 2003 at 09:52:11AM -0500, Brent Baisley wrote:
As long as the foreign keys are index it should be pretty quick. There
really is no way to get around processing every record in table data.
As that table grows, your query will get slower. But a 157K rows isn't
really a whole
As long as the foreign keys are index it should be pretty quick. There
really is no way to get around processing every record in table data.
As that table grows, your query will get slower. But a 157K rows isn't
really a whole lot.
How long does it take? Obviously you would want to run SELECT
Hi folks,
I have a problem that normally I'd want to use subselects on. Here
it goes:
Table t1 has a column data_id. Table t2 has a column data_id. Both
are foreign keys for the autoincrement primary column id of the
table called data.
I wish to delete every row from data which
Hi All
I know that this maybe a repeated question here but I just need to know
the updates.
I am asking about the new feature of subselects which should be implemented
in version 4.1 (thats what i know), when this version is expected to the
light ?
For me this single feature (subselect
How's your application written? i.e. J2EE?
We've managed to put a layer in between the application and MySql that translates
subselects on the fly into inner joins.
This allowed us to quite painlessly move from MS SQL to MySql.
EXISTS can be refactored into an inner join.
NOT EXISTS can
Thanks Greg for the hint but I also wanted to know when is MySQL version 4.1
to be expected to show up in binaries ?
Ahmed
- Original Message -
From: [EMAIL PROTECTED]
To: Ahmed Farouk [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, January 02, 2003 11:46 PM
Subject: Re: subselects
Ahmed,
- Original Message -
From: Ahmed Farouk [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Thursday, January 02, 2003 9:52 PM
Subject: subselects
Hi All
I know that this maybe a repeated question here but I just need to
know
the updates.
I am asking about the new
I need to figure out values in a column of a given table that do not
exist in a a column of another given table without subselects.
For example:
If I have one table called TABLEA with column name id and values
1,2,3,4,5,6,7,
and another table called TABLEB with column name id and values
Hello.
On Wed 2002-08-21 at 16:49:06 -0400, [EMAIL PROTECTED] wrote:
I need to figure out values in a column of a given table that do not
exist in a a column of another given table without subselects.
For example:
If I have one table called TABLEA with column name id and values
of another given table without subselects.
For example:
If I have one table called TABLEA with column name id and values
1,2,3,4,5,6,7,
and another table called TABLEB with column name id and values 3,4,5.
Is there any select statement I can do, which will return records in
TABLEA with id values
On Thu, 20 Jun 2002, Seth Yount wrote:
Hello experts
I'm glad you think so highly of us.
I am trying to decide which would be the most efficient way of
retrieving data, Subselects (nested queries?) or Temporary Tables?
Does MySQL even support these yet?
MySQL supports temporary
Hi,
1. I am reading 'internals' list and I see some words about subselect in 'bk
commit into 4.1 tree' messages...
Seems like we can expect subselect in 4.1 branch !?? :)
2. When 4.0.2 beta will be released?
3. Seems like gcc 3.0 (or 3.1?) becomes an official recommended compliler
for mysql
Alexander,
Monday, June 03, 2002, 9:46:40 AM, you wrote:
AB 1. I am reading 'internals' list and I see some words about subselect in 'bk
AB commit into 4.1 tree' messages...
AB Seems like we can expect subselect in 4.1 branch !?? :)
Yeah, sub-selects is in our TODO for 4.1, look at:
Hi.
On Tue, Oct 02, 2001 at 04:44:08PM +0100, [EMAIL PROTECTED] wrote:
[...]
I would like to use Subselects with mySQL but they are not supported
until 3.24.
Sub-selects are scheduled for 4.1, as far as I know (also see end of
page http://www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.html
(Apologies in advance if this is the wrong place to ask, but this is my
first foray into this list.)
I would like to use Subselects with mySQL but they are not supported
until 3.24.
Can anyone tell me if there is an estimated date for the release of
3.24?
Secondly is it likely
To: [EMAIL PROTECTED]
Subject: Subselects
(Apologies in advance if this is the wrong place to ask, but this is my
first foray into this list.)
I would like to use Subselects with mySQL but they are not supported
until 3.24.
Can anyone tell me if there is an estimated date for the release
34 matches
Mail list logo
