Re: MySQL 5 is 25% slower then 4.1

[Chenzhou Cui]

The file is in fix column width format. If it is in CSV format, I don't 
need a program to read it.
The first few lines are as following:
-------------------------------------------

++++usnob_read: no zone specified, read from South Pole
#USNOB (whole)
#USNO-B1.0   Tycho-2        RA  (J2000) Dec    sRA sDE  Epoch   pmRA   
pmDE P spA spD Fit N MsY| Bmag1 C Surv. cl <-xi-><-eta>| Rmag1 C Surv. 
cl <-xi-><-eta>| Bmag2 C Surv. cl <-xi-><-eta>| Rmag2 C Surv. cl 
<-xi-><-eta>|  Imag C Surv. cl <-xi-><-eta>| ;     r(")
0000-0000001              001.087156-89.918600  65   0 1986.9     -2     
-6 8   4   0 0 0 3 ..Y|  ---  - --    --             | 14.00 1 5-001  9 
+00.04-00.01| 14.43 1 4-001  3 -00.04+00.00| 13.68 1 6-001  0 
-00.02+00.00|  ---  - --    --             |
0000-0000002              001.330223-89.933234   5 192 1986.9     +2     
+0 6   0  12 0 1 3 ...|  ---  - --    --             | 17.26 1 5-001 10 
+00.00+00.14| 17.43 1 4-001  8 -00.01-00.10| 17.20 1 6-001  0 
-00.01-00.06|  ---  - --    --             |
0000-0000003              003.255817-89.964103   2  80 1986.9     +8    
+18 8   0   5 0 0 3 ...|  ---  - --    --             | 19.22 1 5-001  8 
+00.00+00.06| 21.20 1 4-001  2 -00.01-00.04| 19.66 1 6-001  0 
-00.01-00.03|  ---  - --    --             |
0000-0000004              003.646575-89.906014 204 224 1986.9     +0     
+0 0   0   0 3 3 3 ...|  ---  - --    --             | 19.50 1 5-001  3 
+00.17-00.29| 19.86 1 4-001  8 +00.10+00.01| 18.60 1 6-001  0 
-00.29+00.26|  ---  - --    --             |
0000-0000005              006.311025-89.952395 114  45 1986.9     +0     
+0 0   0   0 1 0 3 ...|  ---  - --    --             | 18.10 1 5-001  9 
-00.11-00.05| 18.61 1 4-001  8 -00.06-00.01| 18.23 1 6-001  0 
+00.16+00.05|  ---  - --    --             |


Gary Richardson wrote:

>Also, if the file looks anything like a CSV file, I recommend using
>LOAD DATA INFILE
>
>http://dev.mysql.com/doc/refman/4.1/en/load-data.html
>
>You'll probably load that data in half to a quarter of the time.
>
>On 11/30/05, Daniel Kasak <dkasak@stripped> wrote:
>  
>
>>Chenzhou Cui wrote:
>>
>>    
>>
>>>I didn't use multiple insert statements and "LOAD DATA INFILE", but
>>>only "insert into" statement. The Java program reads one line from the
>>>source file, and then execute an "insert" statement.
>>>      
>>>
>>I can't comment on the speed of 5.0.x vs 4.1.x, but I can suggest that
>>you optimize this 'insert' process.
>>
>>Instead of issuing a new insert command per record, how about using
>>placeholders and binding values? I don't know any Java, but from Perl
>>you can do this. Actually, I'm not certain that MySQL supports this yet
>>or not - you'd have to check your server version and drivers.
>>
>>Or you could do something like collect a series of records - say 100
>>records at a time, and issue an insert query that includes all of them, eg:
>>
>>insert into SomeTable ( field_1, field_2, field_3 ) values
>>( 3, 56, 45 ),
>>( 45, 3456, 345 ),
>>( 345, 76, 345 ),
>>( 345, 45, 546 )
>>
>>This is a lot faster than issuing separate inserts per record. You don't
>>want to include *too* many records at once - there's a maximum packet
>>size or something like that that you can't exceed.
>>
>>--
>>Daniel Kasak
>>IT Developer
>>NUS Consulting Group
>>Level 5, 77 Pacific Highway
>>North Sydney, NSW, Australia 2060
>>T: ([phone] / F: ([phone]
>>email: dkasak@stripped
>>website: http://www.nusconsulting.com.au
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>    
>>
>>
>  
>

-- 
============================================================
Chenzhou Cui  (China-VO Project)   
National Astronomical Observatory | Tel: [phone]
Chinese Academy of Sciences       | FAX: [phone]
Datun Road 20A, Chaoyang District | Email: ccz@stripped
Beijing 100012, China             | WWW: www.lamost.org/~cb
============================================================