SEO

#1 (**permalink**) 03-07-2008, 02:29 PM

hello group

there is table1 with 5 fields: A,B...E definied as varchar(250), without
indexes and autonumber fields
i would like to create table2 with unique combination of fields
the simplest way is (imho) query:

create table table2
select A,B,C,D,E from table1
group by A,B,C,D,E;

but the query is not effective and takes a lot of time.
have you got any ideas how to speed up the process?

#2 (**permalink**) 03-07-2008, 02:29 PM

On Fri, 07 Mar 2008 11:51:46 +0100, aaooo54 <roobik@poczta.onet.pl> wrote:

> hello group

>
> there is table1 with 5 fields: A,B...E definied as varchar(250), without
> indexes and autonumber fields
> i would like to create table2 with unique combination of fields
> the simplest way is (imho) query:
>
> create table table2
> select A,B,C,D,E from table1
> group by A,B,C,D,E;
>
> but the query is not effective and takes a lot of time.
> have you got any ideas how to speed up the process?

For a simple 'run once' scenario, I'd probably do this:
CREATE TABLE table2
....
ADD UNIQUE(A,B,C,D,E)

INSERT IGNORE INTO table2 (A,B,C,D,E) SELECT A,B,C,D,E FROM table1;

... and possibly drop the UNIQUE afterward

For a repeatable scenario, I think creating any other tables them
temporary ones is most likey a flaw in design somewhere.
--
Rik Wasmus

#3 (**permalink**) 03-07-2008, 02:29 PM

> For a simple 'run once' scenario, I'd probably do this:
there is cleaver, i will check this way

> For a repeatable scenario, I think creating any other tables them
> temporary ones is most likey a flaw in design somewhere.
i am not sure what you have on your mind, can you explain your comment?

#4 (**permalink**) 03-07-2008, 02:29 PM

On Fri, 07 Mar 2008 12:28:56 +0100, aaooo54 <roobik@poczta.onet.pl> wrote:

>> For a simple 'run once' scenario, I'd probably do this:
> there is cleaver, i will check this way

>
>> For a repeatable scenario, I think creating any other tables them
>> temporary ones is most likey a flaw in design somewhere.
> i am not sure what you have on your mind, can you explain your comment?

If you plan to do this more often in the same database, IMHO the table
should allready exist after initial creation, and not be recreated again
and again.
--
Rik Wasmus

#5 (**permalink**) 03-07-2008, 02:29 PM

> If you plan to do this more often in the same database, IMHO the table
> should allready exist after initial creation, and not be recreated again
> and again.
I see. I will remember that but this time there is "run once" script.
thank you Rik.
R

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

performance issue - group by, multiple fields