Hi Mike,
Thanks for you help!
Best,
Jia
mos wrote:
Jia,
The code you sent seems to be able to get the job done.
You could try something simpler by executing 2 sql statements instead
of using one. Something like:
create table rmpdata1
select ri.*, mv.* from RItime as ri left join
Thanks for your reply, Mike.
Yes, 13419851 rows were added to rmpdata1. However, 10 minutes seem to
be too long. I run the same join by using SQL procedure in a statistical
software called SAS on a similar machine. It only takes 1 minute and 3
seconds.
Yes, it is a 1:1 relationship between
Jia,
Yes, it is a 1:1 relationship between table RItime and MVtime. However, I
don't get your suggestion, I'd recommend joining the two tables into 1
table so you don't have to join them in the first place. Could you
elaborate that?
Sure but first I have to relate it to my own experience.
Hi Mike,
Thanks for your detailed answer. Now, I understand what you mean. And,
yes, I agree with you that keeping all data in one table works better
for a bunch of 1:1 relationship tables. Actually, this is what I was
trying to do with that query.
Since you mention They all had a 1:1
Jia,
The code you sent seems to be able to get the job done.
You could try something simpler by executing 2 sql statements instead of
using one. Something like:
create table rmpdata1
select ri.*, mv.* from RItime as ri left join MVtime as mv on
(ri.code=mv.code and ri.ndate=mv.ndate));
Hi there,
One simple query took more than 10 minutes. Here is how relevant rows
in the slow query log looks like:
# Time: 090905 10:49:57
# u...@host: root[root] @ localhost []
# Query_time: 649 Lock_time: 0 Rows_sent: 0 Rows_examined: 26758561
use world;
create table rmpdata1 select
How many rows were added to rmpdata1 table? If it is 13.4 million rows then
it is going to take several minutes to join this many rows from the 2 tables.
Is there a 1:1 relationship between the two tables or a 1:Many? If there is
a 1:1 then I'd recommend joining the two tables into 1 table so
