TED]>
To:
Sent: Tuesday, June 20, 2006 3:08 AM
Subject: Query question: select * from table where id in (1,2,3) order
by date uses FILESORT
Hi all,
this is a problem I'm running into:
A table has like 400,000 rows, with a primary key index id. I use this
query:
SELECT * FROM tab
he query to see what mysql is going to
do.
- Original Message -
From: "Peter Van Dijck" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, June 20, 2006 3:08 AM
Subject: Query question: select * from table where id in (1,2,3) order by date
uses FILESORT
Hi all,
this is a problem
Hi all,
this is a problem I'm running into:
A table has like 400,000 rows, with a primary key index id. I use this query:
SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date
DESC LIMIT 0, 40
The problem is, it uses the index id and then a FILESORT.
Questions:
1) Am I correct i
How about
INSERT INTO original_table
SELECT MAX(f1), f2, f3 FROM new_table GROUP BY f2, f3;
Michael
Stephen E. Bacher wrote:
I had a similar problem, but my criteria for selecting the
value of "f1" was different; it's a date field and I wanted
only the rows with the most recent date value in th
I had a similar problem, but my criteria for selecting the
value of "f1" was different; it's a date field and I wanted
only the rows with the most recent date value in that field,
so only the "latest" of otherwise identical entries got inserted.
I ended up doing something like this:
create tempor
Disregard by last message it's a repeat. THANKS for the help!
On Mon, 16 Aug 2004 14:32:27 -0400, "Michael Stassen"
<[EMAIL PROTECTED]> said:
> Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target
> table, leave it out of the SELECT, and let it auto-generate IDs.
> Something
On Mon, 16 Aug 2004 13:57:13 -0400, "Michael Stassen"
<[EMAIL PROTECTED]> said:
> You were perfectly clear. We understand that you only want to test f2
> and f3 for uniqueness. The question is, which of the possible values
> of f1 do you want to get. Do you see? For a particular unique f2, f3
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target
table, leave it out of the SELECT, and let it auto-generate IDs. Something
like this:
INSERT INTO original_table (f2, f3)
SELECT DISTINCT f2, f3 FROM new_table;
I did that in the same order as your original message, t
On Mon, 16 Aug 2004 13:57:13 -0400, "Michael Stassen"
<[EMAIL PROTECTED]> said:
> You were perfectly clear. We understand that you only want to test f2
> and f3 for uniqueness. The question is, which of the possible values
> of f1 do you want to get. Do you see? For a particular unique f2, f3
You were perfectly clear. We understand that you only want to test f2 and
f3 for uniqueness. The question is, which of the possible values of f1 do
you want to get. Do you see? For a particular unique f2, f3 combination,
there may be multiple f1 values. How should we choose which one to put
On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said:
> Let me see if I can explain it a little betterIf you need to move all
> 3 columns to the new table but you only want *1* row where f2 and f3 have
> a unique combination of values, how do you want to choose *which* value
> of
> f1
Let me see if I can explain it a little betterIf you need to move all
3 columns to the new table but you only want *1* row where f2 and f3 have
a unique combination of values, how do you want to choose *which* value of
f1 to move over with that combination? Do you want the minimum value, the
On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said:
> It all depends on which values of f1 you want to ignore.
>
> f1 f2 f3
> - - --
> val1-1 val2 val3
> val1-2 val2 val3
> val1-3 val2 val3
>
> Which value of f1 would you want in your new table? Which one
It all depends on which values of f1 you want to ignore.
f1 f2 f3
- - --
val1-1 val2 val3
val1-2 val2 val3
val1-3 val2 val3
Which value of f1 would you want in your new table? Which ones to ignore?
Are there other columns (beyond these 3) to move as well?
Yours,
say I'm selecting distinct (non-duplicate) rows for insertion,
insert into original_table select distinct * from new_table
these tables have 3 fields/row. Per the above code all 3 fields are
evaluated by "distict *" .
But my question is: I want to ignore field1, therefore I only want to
test
rent_date
>AND lcopen.id=lcreceive.id GROUP BY lcopen.bank;
>
>
> Like this?
> -Original Message-
> From: Terence Ng [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 03, 2003 7:09 PM
> To: mysql
> Subject: RE: Question - SELECT
>
>
> Tha
How do I correct this SQL code:
2 tables there,
lcopen: id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
SELECT
lcopen.bank,
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date <
current_date AS receive,
#
# the abo
d=lcreceive.id GROUP BY lcopen.bank;
>
>
> I think this will work better.
>
> Dave
> -Original Message-
> From: Terence Ng [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 03, 2003 9:52 AM
> To: mysql
> Subject: Question - SELECT
>
>
> How do I
How do I correct this SQL code:
2 tables there, and I want to add one conditon to item
"receive"
lcopen : id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
SELECT
lcopen.bank,
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive
How do I correct this SQL code:
I want to add one conditon to item
"receive"
lcopen : id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
SELECT
lcopen.bank,
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date <
current_
How do I correct this SQL code:
2 tables there, and I want to add one conditon to item
"receive"
lcopen : id, bank, unit_price_us, order_cbm
lcreceive: id, amount_us, due_date
SELECT
lcopen.bank,
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive
How do I correct this SQL code:
SELECT
lcopen.bank,
SUM(lcopen.unit_price_us*lcopen.order_cbm) * 7.8 AS
open,
#
SUM(lcreceive.amount_us) where lcreceive.due_date <
current_date AS receive,
#
# the above statement is not correct for the
# additional condition
#
FROM lcopen, lcreceive
AND lcopen.id
ct: not so simple sql-question?: SELECT iSession FROM O_Sessions
ORDER BY iSession DESC GROUP BY iUser
thank's for your replies, but that did not help :-(
this is the setting (also look below for example table):
O_Sessions contains multilpe recordsets for iUser (let' say iUser=42).
I want t
Have you tried something like this,
SELECT iSession FROM O_Sessions GROUP BY iUser ORDER BY iSessions DESC;
Mike
- Original Message -
From: "Robo" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 16, 2002 3:39 PM
Subject: ??? Simple sql-question
thank's for your replies, but that did not help :-(
this is the setting (also look below for example table):
O_Sessions contains multilpe recordsets for iUser (let' say iUser=42).
I want the last RECORDSET of this iUser=42, not the first.
With GROUP BY, all the various recordsets with iUser=42 ar
Message-
From: Robo [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 16, 2002 12:39 PM
To: [EMAIL PROTECTED]
Subject: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP
BY iUser
I want the latest (highest) iSession to be selected:
SELECT iSession FROM O_Sessions GROUP BY iUser
On 16 Jul 2002, at 21:39, Robo wrote:
> I want the latest (highest) iSession to be selected:
>
> SELECT iSession FROM O_Sessions GROUP BY iUser
>
> Because of GROUP BY, allways the first(!) recordset for iUser is
> selected. But i want the last recordset to be selected :-(
I'm not sure wha
I want the latest (highest) iSession to be selected:
SELECT iSession FROM O_Sessions GROUP BY iUser
Because of GROUP BY, allways the first(!) recordset for iUser is selected.
But i want the last recordset to be selected :-(
How can this be done ?
(mySQL)
(iSession ist the primary key => lat
28 matches
Mail list logo
