Presentation Topic Help Needed!!!

[jonathan_phang]

Hi all,

I was wondering if you could all help me on something (as you all seem to be a talented bunch!!). I've got to a final round interview for a graduate Analyst Programmer and have to several presentations. I'm fine on most of them, but one technical one is to talk about the following:

In Database technology, what is an index and what are the trade-offs of having an index?

I've done some research with books etc and at oracletuning.com so have got some info, but I'd be greatfull if I could get any extra points that I've missed.

I need it more on the 'trade offs' part, but any tips/links would be very helpful!!!

Come on Hexus Community,

Help one of your own get a job

JonnyP

[Iain]

The main trade-off with having an index is having one on a table on which you will be doing a lot of INSERT statements.

Indexes are used to make searching a table for data lot faster. However, in order to keep the index up to date it is modified with every INSERT. This means that INSERT queries take slightly longer to run as they are also modifying an index. If you imagine the HEXUS reviews table this isn't such a big deal as there are a small number of INSERTS a week as the reviewers add new content but there are thousands of SELECTS a day as people read the content. Hence, an index is performance booster.

On the other hand, if you imagine a table that traps advert impressions then having a complex index slows things down as every ad that requires a SELECT to display it will also require an INSERT to update the table that tells you how many ads have been served. This can slow the site down.

It is not uncommon if you are running a large batch process to add new content to a table with a large index to remove the index first. That way the batch process just loads all the data in and then the index is built at the end. This is faster than loading each row and forcing the database to update the index. To give you an idea, at work we were loading a table with around 4 billion records for assets. With the indexes left on throughout the load it would have taken around 8 days longer to perform the process

hope that helps

[DaBeeeenster]

^^^ What he said

There are different types of indexes that are available, too, depending on what the DBMS is. If you REALLY want to impress in the interview, then you can take a DB, say Oracle, find out what the different types of indexes are and how they would be best used in different situations.

Another thing you might want to investigate is bulk inserts/transactional inserts. This method wraps up a number of inserts into a transaction, and applies them all at once. This can provide you with significant speed improvements over the standard "add record, index, add record, index".

A common error to make is to index pretty much everything (I've seen it done). This can have a disasterous affect on performance. It's important to target which fields to index, and for that you need some sort of query analyzer. There is a really good one in MS SQL Server, and there is a fairly basic one for MySQL. These can be VERY useful tools when DB tuning.

The number one rule to keep in mind when tuning DB's is that the one thing that will REALLY kill your performance is any form of disks access. Memory/Cache = Fast, Hard Disks - Slow. Anywhere you see a query analyzer come out with "full table scan" on a large table is a perfect example for an index.

Finally, (tho this point is sort of redundant now, as Disk Space is so cheap), indexes will take up more disk space than the tables by themselves, so you have a trade off there. So, for example, if you had an embedded device that had a DBMS running on it (say a Palm Pilot, with solid state storage), indexes might be a bad idea.

[jonathan_phang]

Az, Beeenster,

thaks a lot for the advice there! i'd got some of the points made, but not others, so thats great. I've been researching about the different types (functional, inverted, bitmapped etc) and will try to drop in a few pointers about each.

Again, really appreciated (keep em coming tho!!)

JP

[DaBeeeenster]

What type of firm is it? Sofware house/Consultancy/Banking?

[jonathan_phang]

Its for the 'Business Applications' Team of News International ltd. Its the UK subsiduary of News Corp, Rupert Murdochs company that has holdings of BskyB, Fox etc.

The UK part handles production of the sun, news of the world, the times amongst others. The job I'm going for mainly deals with advertising (production of online booking, etc planning) using unix, VB, oracle, PL/SQL.

Got to the final 3 applicants so I'm busy preparing some presentations. Bit nervous, but fingers crossed, I'll do ok!

JP