Indexing question

[takaoki]

Hi,

When choosing which tables to index (by a DATE field in this case), is it better to select tables based on NUMBER of rows - OR - based on the total SIZE of the table? In this case, I'm not indexing all the tables, just ones most affected by size.

This is more a practical question as opposed a question of index "principles".

Thank you

 

[hmckillop]

Firstly, indexing is a method for accessing data with as few I/O operations as possible, so wherever you plan to access data you should have an appropriate index.

In some cases, due to many underlying sql questions, a full table can be scanned because the table has so little data in it, the optimiser thinks it better just to load the whole table to memory. Alternatively the resultset will be a relatively large proportion of the table again it will return the whole data set ( i am not 100% certain on the percentages)

So in answer to your question, I would index any/all of the tables which you plan to access regardless of the amount of data


"I'm living so far beyond my income that we may almost be said to be living apart

 

[takaoki]

Right. I know this That's why I was saying this is not a question of principles.

I just want to know if it's better to index based on # rows vs table size. In this case, I'm just indexing one DATETIME field.

This is a very specific thing I'm working on...

 

[r937]

This is a very specific thing I'm working on...

shouldn't take you long, then, to test it both with and without an index, and solve your practical problem that way




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon