Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: insert huge # of rows

    hi
    newbie here. Whenever I try to insert huge number of rows (say 25000 records) into a table my transaction timesout. I use this query

    insert into gTable select A,B, C, D, E,F,G,sum(V) ,((Floor(H/10000))*10000) as ttime, min(V) , max(V), sum(V)/6 from preTable where (A <=25000 and A>0) group by B,C,D,E,F,G,H

    Few columns in this table(innodb type) are indexed and few have FK constraints. This query takes anything between 10 min to 20 min to complete. Removing FKs does speed up insert time but stillam running into similar issues. any help?

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Quote Originally Posted by TimKaine
    hi
    newbie here. Whenever I try to insert huge number of rows (say 25000 records) into a table my transaction timesout. I use this query

    insert into gTable select A,B, C, D, E,F,G,sum(V) ,((Floor(H/10000))*10000) as ttime, min(V) , max(V), sum(V)/6 from preTable where (A <=25000 and A>0) group by B,C,D,E,F,G,H

    Few columns in this table(innodb type) are indexed and few have FK constraints. This query takes anything between 10 min to 20 min to complete. Removing FKs does speed up insert time but stillam running into similar issues. any help?
    What takes the most time here? Did you time the SELECT to see how much time it took to execute? Did you try an EXPLAIN on your select to verify if your query wouldn't benefit from an index on column A?

  3. #3
    Join Date
    Aug 2004
    Posts
    2

    insert huge # of rows

    --"What takes the most time here? Did you time the SELECT to see how much time it took to execute? Did you try an EXPLAIN on your select to verify if your query wouldn't benefit from an index on column A?"

    The select part of the query does not take time. It is the inserts ..and it slows down as the table grows. Here is the result of the explain query.

    *************************** 1. row *************************
    table: preTable
    type: range
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 8
    ref: NULL
    rows: 50408
    Extra: Using where; Using temporary; Using filesort

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •