creating indexes - memory vs speed

[datadriven]

My table strategy involves a main table for registered users.

This table as it works out has a primary index (auto increment) and approximately 6 foreign key columns. All linked column values require three-way and four-way joins to print their values.

How can I balance the "memory required for indexed columns vs. process speed"? (Designing with a combination of single and composite indexes, assuming this can be done.)

Is creating an index really worth it if there will be many inserts?
What about if not many updates or deletes but many selects?

Can do these to test, but where do I get actual time figures from?:

SELECT /*+ FULL (table)*/ col1, col2 FROM table: //full scan
SELECT /*+ INDEX (table indexname)*/ col1, col2 FROM table: //index

[r937]

not really sure i understand what you're doing

"All linked column values require three-way and four-way joins to print their values." -- this is a byproduct of using foreign keys instead of redundantly storing the values (e.g. names), and eliminating redundancy is usually much preferred to eliminating joins

the "memory required for indexed columns vs. process speed" balance should be left up to the database engine -- trust me, it handles input/output disk caching a lot better than we can

"What about if not many updates or deletes but many selects?" -- then in general you want more indexes

[asterix]

Is creating an index really worth it if there will be many inserts?
What about if not many updates or deletes but many selects?

Can do these to test, but where do I get actual time figures from?:

Take a look at this article about indexing in MS SQL, it discusses many of the things you ask. (I wrote it).

The thing to remember is that there is no in vitra best solution, you miust continually reasses your application's performance with the current indexing strategy to get maximum benefit.

[r937]

nice article

what would be the additional benefit of declaring a clustered index on a GUID column, as opposed to a non-clustered index

and isn't it redundant to declare an index on the primary key? it gets one anyway, by virtue of being declared the primary key

by the way, i think datadriven's database is oracle

see http://www.billmagee.co.uk/oracle/sq...70_single.html

[asterix]

Thanks,

Well, I would question the use of GUIDs in general. The only time you really need them is when you are doing replication or federated servers, where you need to ensure that two instances of the same database do not create the same PKs. If you take the standard sql-weenie view, GUIDs are artificial and therefore have no rightfiul place in a relational database anyway...

But let's suppose you do have a table field which is of the GUID datatype. That is 16 bytes, just as large as for ntext. That's a lot of data to put in an index. Say you have a table with 100 million rows, that's a lot of megabytes just for the clustered index. And since any other index you put on the table is going to be including the clustered index key too, that's a lot of IO and storage space.

You generally use clustered indexes for locating ranges, using between or < >. None of these operators makes any sense in connection with GUIDs, you can't meaningfully group on a GUID. Then again they are also used for equality lookups, so if your app does a lot of "select f1 from t1 where pk = 'guidxxxxxxxxxxxxxxxx'" then it is probably worth while after all....

And yes, if you specify a field as being a PK, and the table does not already have a clustered index, SQL Server will create a unique clustered index for you.

[r937]

None of these operators makes any sense in connection with GUIDs.

that's what i thought, but i'm not a dba so i tend not to be interested in performance minutiae

presumably if you're looking up single rows based on a GUID, a non-clustering index would be just as good

i just had momentary visions of GUIDs being inserted into different disk pages in random sequence and i couldn't imagine why this would be a good idea

perhaps it has something to do with no "hot spots" like you get with incremental ids

but like i said, i usually don't care about such stuff, because, you see, i am an sql weenie