Efficiency Considerations

[Emperor]

Hi guys,

I wrote code that will bulk insert records (table rows), I thought it would be nice to check if there was an existing record, a matching URL really, before doing the insert.

I'm wondering, if I have (say) 100,000 rows in my table will it take much longer to check them than if I had (say) 500 rows ? Does MySQL internally optimize my tables for fast look-ups ?

If I'm bulk inserting 100 records and the table has 100,000 in it already, and if it does a simple iteration for a match, that could take a long time.

What do you think ?

[Kid Charming]

If you do have a matching url, do you want to replace the other field values without creating a new row, or skip that entry completely?

[Emperor]

Hi Kid,

I would want to skip it entirely. What is the best (most efficient) way to do that ?

Thank you.

[Kid Charming]

The most efficient would probably be to set a unique index on your url field and use the IGNORE keyword in your INSERT query:


INSERT IGNORE
INTO
table
...


If you try then to insert a row with a duplicate url value, that row will not be inserted, but the statement will continue processing, so the rest will.