List:MySQL on Win32« Previous MessageNext Message »
From:John Bonnett Date:August 21 2008 3:06am
Subject:RE: How to perform insert and update of two different tables in one command
View as plain text  
On the face of it I think your solution is quite reasonable.

The SELECT DISTINCT in your first query is essentially asking for all
the details to be sorted to find the distinct ones.

In the UPDATE query it needs to do a lot of looking up in the
eventDetails table so it would be good if that table had an index on its
details column, although an index on a long text column may not be all
that efficient.

I can see what you mean by trying to combine the two to speed it up. You
might be able to do something like that using cursors but I think you
would need a unique key on the events table. What you might do is create
a cursor on a query that selects the key and details from the events
table ordered by the details column. While looping through the rows in
the cursor you would insert rows into the eventDetails table as you
found each new details and plug the newly allocated ids back into the
event table using the unique key.

If there is no unique key on events then I guess the way you suggested
is as good as any. My suggestion above might take as long anyway because
there would be a lot of random updating on the events table but your way
would imply lots of look ups in that index on the eventDetails table.

Either way it is likely to take a while with that many records. If it is
a one off job, it is probably not worth hunting for a perfect solution.

John Bonnett

-----Original Message-----
From: Ilavajuthy Palanisamy [mailto:ilavajuthy@stripped] 
Sent: Thursday, 21 August 2008 11:06 AM
To: win32@stripped
Subject: How to perform insert and update of two different tables in one
command

Hi,

I need help in finding a query which will solve my performance issue;
The requirement is we have big events table around 25 million record. It
has a column called details, which is mostly repeating, so now the idea
is to split the table events into two a)events and b)eventDetails. Move
the details column to a separate table and link these two tables based
on the id.
This details will be unique in the eventDetails table.

eventsDetails table id auto increment and details varchar(512).

i can think of two queries for this

1) insert into eventDetails (details) select distinct details from
events;
>>>> this will populate the eventDetails table.
2) update events e, eventDetails ed set e.id=ed.id where
e.details=ed.details; >>> this will link the events and eventDetails
based on  the id.

Is there any way to combine the above queries into one or is there any
efficient way.

Any help will be greatly appreciated.

Thanks,
Ila.
Thread
How to perform insert and update of two different tables in one commandIlavajuthy Palanisamy21 Aug
  • RE: How to perform insert and update of two different tables in one commandJohn Bonnett21 Aug