2

Im new to using MySQL.

Im trying to run an inner join query, between a database of 80,000 (this is table B) records against a 40GB data set with approx 600million records (this is table A)

Is Mysql suitable for running this sort of query? Whay sort of time should I expect it to take?

This is the code I ied is below. However it failed as my dbs connection failed at 60000 secs.

set net_read_timeout = 36000;


INSERT
INTO    C
SELECT A.id, A.link_id, link_ref, network, 
date_1, time_per, 
veh_cls, data_source, N, av_jt 
from A
inner join B
on A.link_id = B.link_id;

Im starting to look into ways to cutting down the 40GB table size to a temp table, to try and make the query more manageabe. But I keep getting

Error Code: 1206. The total number of locks exceeds the lock table size 646.953 sec

Am I on the right track? cheers!

my code for splitting the database is:

LOCK TABLES TFM_830_car WRITE, tfm READ;
INSERT
INTO    D
SELECT A.id, A.link_id, A.time_per,  A.av_jt 
from A
where A.time_per = 34 and A.veh_cls = 1;
UNLOCK TABLES;

Perhaps my table indices are in correct all I have is a simple primary key

CREATE Table A
(
id int unsigned Not Null auto_increment,
link_id varchar(255) not Null,
link_ref int not Null,
network int not Null,
date_1 varchar(255) not Null,
#date_2 time default Null,
time_per int not null,
veh_cls int not null,
data_source int not null,
N int not null,
av_jt int not null,
sum_squ_jt int not null,


Primary Key (id)
);


Drop table if exists B;
CREATE Table B
(
id int unsigned Not Null auto_increment,
TOID varchar(255) not Null,
link_id varchar(255) not Null,
ABnode varchar(255) not Null,

#date_2 time not Null,

Primary Key (id)

);

In terms of the schema, it is just these two two tables (A and B) loaded underneath a database

DBatt
  • 21
  • 2

2 Answers2

0

I believe that answer has already been given in this post: The total number of locks exceeds the lock table size

ie. use a table lock to avoid InnoDB default row by row lock mode

Community
  • 1
  • 1
Clément Prévost
  • 8,000
  • 2
  • 36
  • 51
0

thanks foryour help.

Indexing seems to have solved the problem. I managed to reduce the query time from 700secs to aprox 0.2secs per record by indexing on:

A.link_id

i.e. from 

from A
inner join B
on A.link_id = B.link_id;

found this really usefull post. v helpfull for a newbe like myself

http://hackmysql.com/case4

code used to index was:

CREATE INDEX linkid_index ON A(link_id);
DBatt
  • 21
  • 2