z7Xkie19

1/*
2********************************
3* lmb_idb_u2/db/lmb_idb_u2.sql *
4********************************
5*/
6
7/*
8use this script to create the new innodb database
9*/
10
11use lmb_idb_u2;
12
13delimeter ;
14
15drop table if exists handles;
16
17create table handles
18(
19 feature_id mediumint unsigned not null, 
20 tag_id smallint unsigned not null,
21 checkout_ts datetime not null,
22 user_id mediumint unsigned default null,
23 checkin_ts datetime default null,
24 loadinfo_id smallint unsigned default null,
25 process_id mediumint unsigned default null,
26 request_ts datetime default null,
27 host_id smallint unsigned default null,
28 tokens smallint unsigned default null,
29 account_id smallint unsigned default null,
30 origin tinyint default null,
31 accounting_location_id smallint unsigned default 0,
32 primary key (feature_id, tag_id, checkout_ts)
33)
34engine=InnoDB 
35default charset=latin1 collate latin1_swedish_ci;   
36	
37
38	
39drop table if exists tags;
40
41create table tags
42(
43tag_id smallint unsigned not null primary key,
44name varchar(255) not null
45)
46engine=InnoDB 
47default charset=latin1 collate latin1_swedish_ci;   
48
49
50
51/*
52***************************************
53* lmb_idb_u2/db/lmb_idb_u2_export.sql *
54***************************************
55*/
56
57
58/*
59export data from your old myisam database using this script - remember
60order is important !
61*/	
62
63use old_myisam_database;
64
65delimiter ;
66 
67select 
68 feature_id,
69 tag_id,
70 checkout_ts,
71 user_id,
72 checkin_ts,
73 loadinfo_id,
74 process_id,
75 request_ts,
76 host_id,
77 tokens,
78 account_id,
79 origin,
80 accounting_location_id 
81into 
82  outfile 'd:\\f00\\My Dev\\PHP\\lmb_idb_u2\\export\\handles.dat' 
83fields terminated by '|' optionally enclosed by '"'
84lines terminated by '\r\n'
85from
86 handles
87order by
88 feature_id,
89 tag_id,
90 checkout_ts,
91 user_id;
92 
93  
94select distinct
95 tag_id,
96 name
97into 
98  outfile 'd:\\f00\\My Dev\\PHP\\lmb_idb_u2\\export\\tags.dat' 
99fields terminated by '|' optionally enclosed by '"'
100lines terminated by '\r\n'
101from
102 tags
103order by
104 tag_id;
105
106
107/*
108***************************************
109* lmb_idb_u2/db/lmb_idb_u2_import.sql *
110***************************************
111*/
112
113
114/*
115i just did a 2 million row import in 33 seconds so i am expecting your 
11685 million handles.dat import to be around the 30 min mark - depending 
117on your HD performance and my.cfg / my.ini innodb config
118*/	
119
120use lmb_idb_u2;
121
122delimiter ;	
123
124truncate table handles;
125
126set autocommit = 0;
127
128load data infile  'd:\\f00\\My Dev\\PHP\\lmb_idb_u2\\import\\handles.dat' 
129into table handles
130fields terminated by '|' optionally enclosed by '"'
131lines terminated by '\r\n'
132(
133 feature_id,
134 tag_id,
135 checkout_ts,
136 user_id,
137 checkin_ts,
138 loadinfo_id,
139 process_id,
140 request_ts,
141 host_id,
142 tokens,
143 account_id,
144 origin,
145 accounting_location_id 
146)
147set 
148checkout_ts = from_unixtime(checkout_ts),
149checkin_ts = from_unixtime(checkin_ts),
150request_ts = from_unixtime(request_ts);
151
152commit;
153
154
155truncate table tags;
156
157set autocommit = 0;
158
159load data infile  'd:\\f00\\My Dev\\PHP\\lmb_idb_u2\\import\\tags.dat' 
160into table tags
161fields terminated by '|' optionally enclosed by '"'
162lines terminated by '\r\n'
163(
164 tag_id,
165 name
166)
167set 
168name = nullif(name,'');
169
170
171commit;