List:General Discussion« Previous MessageNext Message »
From:Mikhail Entaltsev Date:August 27 2004 9:05pm
Subject:Re: 1 day 28 min insert
View as plain text  
> If that were a valid SQL statement, I would agree with you. What you wrote
**only** functions through
> a MySQL-specific SQL extension that permits non-aggregated, non-grouped
columns to exist in the SELECT clause.

He is working with MySQL and that query is working in MySQL. So everything
should be fine. :)
But I agree that it is MySQL-specific query.

> I do not understand why you believe that a GROUP BY test will prove any
sort of performance gain
> for his original INSERT problem.  It took just over 24 hours to do an
INSERT IGNORE
> to add just about 1000 rows to a 77 million row table.
> What metric of that performance will your GROUP BY test help to prove?

Yes, it is unclear from my previous email. Let's look at that query:

insert into 321st_stat
select * from stat_in group by dic,niin,fr_ric,don,suf,dte_txn,sta;

Why it could be slow? Either select part is slow, either insert or both.
If select part is slow then adding KEY
(`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta`) on stat_in table
should help.
If select part is fast then IMHO the problem is in number of keys and
PACK_KEYS for 321st_stat table.
Right?

Best regards,
Mikhail.


----- Original Message ----- 
From: SGreen@stripped
To: Mikhail Entaltsev
Cc: matt ryan ; mysql@stripped
Sent: Friday, August 27, 2004 10:40 PM
Subject: Re: 1 day 28 min insert

If that were a valid SQL statement, I would agree with you. What you wrote
**only** functions through a MySQL-specific SQL extension that permits
non-aggregated, non-grouped columns to exist in the SELECT clause.

I do not understand why you believe that a GROUP BY test will prove any sort
of performance gain for his original INSERT problem.  It took just over 24
hours to do an INSERT IGNORE to add just about 1000 rows to a 77 million row
table. What metric of that performance will your GROUP BY test help to
prove?

Respecfully puzzled,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



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