Useful Video suggested by SachAccess

[SachAccess]

Hi,
I was not able to find appropriate thread for YouTube videos hence posting here. Though I remember reading a thread about YouTube videos, cannot remember at the moment.

Please see above video if you get time. Was bit tired from working and wanted to watch something hence browsed and watched this video.
I am at the beginner level so was not able to understand most of the stuff, however, the author says, indexing can impact performance in negative way. Would like to know views from the experts here.

 

[gemma-the-husky]

It's hard to see why indexing would have negative results.

You don't want to index fields unnecessarily, as there is a machine time cost involved in maintaining indexes, but other than that, I can't see the issue. Does he mention why?

 

[Minty]

I have seen a table( a big one mind you >2,000,000 rows) with a three-field unique primary key index, that was painful to operate on.
I have to regularly replicate a copy of it into another database, and linking to it and performing a unique insert of say 25000 rows took ages, 10-15 minutes at least.

It was simpler and quicker to truncate the entire table and simply duplicate the entire thing than run the insert. The whole process took about 10 % of the original time.

 

[CJ_London]

Edit - response to this question originally posted to the Off Topic thread
Sorry, don't have time to trawl through 45 minutes of video. If you want a specific comment, please provide the time in the video where the author makes this assertion.

Certainly indexing can slow the bulk insert and update of records since the indexes need to be updated as well. But manual entry of data will not be noticeably affected. There are ways to overcome this which may or may not have been mentioned by the author.

However that small time cost of inserting/updating records is more than outweighed by the time saved when retrieving data.

see this link on why indexing is important

Why indexing is important for good performance​

 

[CJ_London]

a three-field unique primary key index

Personally I avoid multi field PK's, although I accept the possible need for multi key indexes to prevent duplication - however there is an alternative..

Create a hex field based on the three fields and index with no duplicates - only one index required. A few years ago I was involved in developing an app which imported around 1m rows every month to a sql server BE. There were around1 5 columns and it was important that no duplicates could be imported. To have a multi field index just wasn't practical. The hex field was based on all 15 fields and was around 40 characters in length and importing 1m rows took about a minute.