some 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-corre
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 are broken with regards to index
usage?
I think I'm discovering that sub-selects in MySQL
th 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: Subse
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 doesn't
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 Link.parentID
embre 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)
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 |
++-+--
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 t
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)
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 statement
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 count(SUB.S
* 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
head.).
Thanks in any event for your time and interest.
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 2:49 AM
To: [EMAIL PROTECTED]
Cc: Petre Agenbag
Subject: Re: help to structure query without using subselects
* Petre Agenbag
[...]
* 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
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 thi
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
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
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 .
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 wh
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 r
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
k
2, 2003 11:46 PM
Subject: Re: subselects
>
> 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
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 jo
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) will
;>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"
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&
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 &quo
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 the
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:
ht
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
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_feature
001 11:44
> 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
(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 that the
Are temp tables the best way to handle a 'SUBSELECT'?
I can't see moving large amounts of data through pipes/sockets into an
PHP interpeter and it's array mechanism, sifting through that to get
what I need. It would be better to do that all in the memory of the
server with the MySQL daemon on it,
35 matches
Mail list logo
