List:General Discussion« Previous MessageNext Message »
From:Andrew Pattison Date:August 19 2004 4:24pm
Subject:Re: 1 day 28 min insert
View as plain text  
I'm guessing that you have indexes on the 321st_stat table? If this is the 
case, try dropping them before you do the insert, then rebuilding them. 
MySQL is known to be slow at doing bulk inserts on indexed tables. Also, 
updates are much faster than inserts since with inserts there are much more 
disk IOs required.

Cheers

Andrew.
----- Original Message ----- 
From: "matt ryan" <matt_lists@stripped>
To: <mysql@stripped>
Sent: Thursday, August 19, 2004 5:06 PM
Subject: 1 day 28 min insert


>I think oracle parallel query is calling me
>
> 110,832,565 stat records
>
> 77,269,086 on weekly update, I get small daily files, but daily sql's dont 
> work very well, and miss records, in this case it missed 563 records.
>
> mysql> update stat_in set ctasc='321ST';
> Query OK, 77269086 rows affected (24 min 17.60 sec)
> Rows matched: 77269086  Changed: 77269086  Warnings: 0
>
> mysql> insert ignore into 321st_stat select * from stat_in;
> Query OK, 563 rows affected (1 day 28 min 35.95 sec)
> Records: 77269086  Duplicates: 77268523  Warnings: 0
>
> I just cant deal with speeds this slow, an insert onto a table with a 
> primary key that tosses out almost all records shouldnt take this long to 
> do
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
>
> 


Thread
1 day 28 min insertmatt ryan19 Aug
  • Re: 1 day 28 min insertAndrew Pattison19 Aug
    • Re: 1 day 28 min insertmatt ryan19 Aug
      • Re: 1 day 28 min insertSGreen19 Aug
    • Re: 1 day 28 min insertSGreen19 Aug
  • Re: 1 day 28 min insertDan Nelson19 Aug
    • Re: 1 day 28 min insertmatt ryan19 Aug
      • Re: 1 day 28 min insertDan Nelson19 Aug
        • RE: 1 day 28 min insertBoyd E. Hemphill19 Aug
          • Re: 1 day 28 min insertmatt ryan19 Aug
            • Re: 1 day 28 min insertRemigiusz SokoĊ‚owski20 Aug
              • RE: 1 day 28 min insertDonny Simonton20 Aug
  • Re: 1 day 28 min insertRhino19 Aug
  • Re: 1 day 28 min insertMikhail Entaltsev20 Aug
    • Re: 1 day 28 min insertmatt ryan27 Aug
  • Re: 1 day 28 min insertMikhail Entaltsev27 Aug
    • Re: 1 day 28 min insertmatt ryan27 Aug
      • Re: 1 day 28 min insertSGreen27 Aug
        • Re: 1 day 28 min insertMikhail Entaltsev27 Aug
          • Re: 1 day 28 min insertSGreen27 Aug
            • Re: 1 day 28 min insertMikhail Entaltsev27 Aug
          • Re: 1 day 28 min insertmatt ryan1 Sep
  • Re: 1 day 28 min insertMikhail Entaltsev27 Aug