[MySQL - PHP] Deleting many rows in a large table
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9

Thread: [MySQL - PHP] Deleting many rows in a large table

Hybrid View

  1. #1
    Join Date
    Jul 2011
    Location
    .NET 3.5
    Posts
    40

    [MySQL - PHP] Deleting many rows in a large table

    As the title suggest, I am using MySQL as my database and I would like to delete many rows (let's say 100k) in a large table (a few millions records). Users will select a date from when to when and choose to delete the records. The PHP script will then execute the query.

    I found 2 ways to do it, one is to use a script to delete like 1000 or 10000 rows per second or so. I am using PHP for the scripting part.
    Another is by renaming the table, then create a new table. After that, I delete the rows in the old table and then I transfer the records from the new table to old table.

    But those 2 ways are still going to take a very long time. Is there any other solutions or ways that can be faster without using too much of the CPU?
    If it can be done within 10 seconds, then that would be the best.

    Any help would be much appreciated.
    Last edited by tiestoycc; November 27th, 2012 at 09:09 PM.

  2. #2
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: [MySQL - PHP] Deleting many rows in a large table

    Fastest way is to put the dates in the SQL query..
    Code:
     Delete from [table] where [daterow] between [date1] and [date2]
    and call it once...

    something else to assist in the speed is to create an index on the Daterow, this will assist the Query to locate all relevant records..
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  3. #3
    Join Date
    Jul 2011
    Location
    .NET 3.5
    Posts
    40

    Re: [MySQL - PHP] Deleting many rows in a large table

    Yea... I have a timestamp in the table.
    And the column is indexed too.
    However, it is still taking a long time to delete the records.

  4. #4
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: [MySQL - PHP] Deleting many rows in a large table

    well how long does it take??

    I know SQL server much better than MySQL, so the following might not exactly work, but look them up..

    TableLock vs Rowlock :locking the table(rather than row) will prevent MySQL from trying to update the indexing after every row delete. (index's will be updated after all deletes are completed)

    Foreign Key's : If the table has foreign keys, this could also slow down the delete ,as it also has to locate the Matching records in other tables. (Either ensure the Keys are indexed or remove the foreign keys before the delete and rebuild after the delete.)

    Linked Tables : Are there tables that are dependent on this one.. A delete from this table will cause a (possibly slower non indexed assisted) delete in any dependent tables.

    right now cant think of any others but there are a few more...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  5. #5
    Join Date
    Jul 2011
    Location
    .NET 3.5
    Posts
    40

    Re: [MySQL - PHP] Deleting many rows in a large table

    To delete like 100k records from 1million records will take more than 5minutes.

    Maybe I can look up on TableLock.

    I don't have any foreign keys or linked tables. This table is independent.

    There couldn't be any simpler way other than "DELETE FROM table_name WHERE timeinlong BETWEEN 'fromdate' AND 'todate'".

    Still trying to come up with a better way.

    Thanks! let me know if you can think of others.

  6. #6
    PeejAvery's Avatar
    PeejAvery is offline Super Moderator Power Poster
    Join Date
    May 2002
    Posts
    10,877

    Re: [MySQL - PHP] Deleting many rows in a large table

    Quote Originally Posted by tiestoycc View Post
    To delete like 100k records from 1million records will take more than 5minutes.
    That is absolutely ridiculous. Even for an old server with very few resources it should take seconds on a non-indexed table and nanoseconds on an indexed table.

    What is your table structure? Any large blobs in these?
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  7. #7
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: [MySQL - PHP] Deleting many rows in a large table

    There is lots of talk on some sites that Updates and inserts are faster than deletes in many SQL database systems..

    try this code (as is for now, because it can be destructive to Data) and see how long this takes to run.. If it's shorter i'll show you how to change it to work in place of a delete..
    Code:
    select *  into #tmptable from [table]
     WHERE not([daterow] BETWEEN '[date1]' AND '[date2]')
    truncate table #tmptable
    select *  into #tmptable from [table]
    Just one thing... Does the table have a Auto numbering field (Identity Specification = yes)??
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  8. #8
    Join Date
    Jul 2011
    Location
    .NET 3.5
    Posts
    40

    Re: [MySQL - PHP] Deleting many rows in a large table

    Quote Originally Posted by GremlinSA View Post
    There is lots of talk on some sites that Updates and inserts are faster than deletes in many SQL database systems..

    try this code (as is for now, because it can be destructive to Data) and see how long this takes to run.. If it's shorter i'll show you how to change it to work in place of a delete..
    Code:
    select *  into #tmptable from [table]
     WHERE not([daterow] BETWEEN '[date1]' AND '[date2]')
    truncate table #tmptable
    select *  into #tmptable from [table]
    Just one thing... Does the table have a Auto numbering field (Identity Specification = yes)??
    Yea, Updates, Inserts and Selects are a lot faster than deletes. I can select hundreds of thousands of records in less than a second. But when it comes to deleting, it takes too long.

    No, there is no auto-increment field.

  9. #9
    Join Date
    Jul 2011
    Location
    .NET 3.5
    Posts
    40

    Re: [MySQL - PHP] Deleting many rows in a large table

    This is what I did:

    mysql> delete from user_table where username='john';
    Query OK, 203601 rows affected (6 min 6.88 sec)

    and the username column is indexed as well.

    We have 63 columns in the table with 5 indexes. No, there are no large blobs in the table.

Tags for this Thread

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center