I've got a couple of questions if it might be better to not use indexing.

BACKGROUND: My records possess a timestamp attribute, and also the records is going to be placed so as of the timestamps (i.e., placed chronologically).

QUESTIONS:

  1. Basically Avoid using indexing could it be typical for that database to place the records within the order that they are placed?

  2. If response to #1 is absolutely, after i perform a "Choose .. WHERE timestamp > X" type query will the database be efficient in internet marketing, or does it need to go through each and every record because it is not indexed? I'd assume if there have been no index, the database wouldn't "know" the records were placed in sorted order and may not, therefore, take advantage of sorted property from the database.

I suppose a clustered index might be best for these kinds of records &lifier their card inserts.

Please tell me what everyone think.

Thanks, jbu

In my opinion, yes, the database will place stuff within the chronological order, particularly if you never remove anything. However, it's not guaranteed, and it is a very bad idea to try and depend on behavior that's not guaranteed.

Also, the query planner is not likely to know this fact, so any query you need to do with no index may cause a complete table scan. Whether that's reduced than an indexed query will be based a great deal on which kind of data you've, and what number of it comes down following the "X" inside your query.

it is dependent around the database you're using, obviously!

generally, for those who have lots of card inserts to complete, it's most likely easier to disable the indices, perform the card inserts, then recreate the indices

while using timestamp because the clustered index (i.e. an order where the rows are saved) is only going to matter in case your most typical queries have been in time order (instead of retrieve-this-row) and when you will find no duplicate timestamps

If you will find no removes in the table, you are able to think that the database only will add new blocks towards the finish on the table. However, there is no guarantee whether individuals blocks on disk are generally contiguous, as well as evolving correctly (i.e. the table can very well be fragmented with time).

Any Choose from the table without any indexes can lead to a table scan. Indexes are the way you "tell" the database about such things as "timestamps have been in climbing order".

A clustered index will work for telling the database you need to keep your rows in index order inside the table. However, it's typically (based on your implementation) only valuable on reasonably static data, as that's the only method the DB will be sure that the rows on the table truly are in index order, because it does that by repairing the table.

What database?

1)
A table without indexes is known as a heap. A heap will keep records within the order these were placed. As lengthy while you don't place from multiple threads, you'll have the ability to predict an order the database will keep records in. As others have stated, this may presume you do not do deletions by which situation your DBMS may fill the empty pages with new rows.

2)
Without indexes, the DBMS will need to perform a complete table scan (which runs in linear time with regards to the amount of records). For records in which you place the records with growing timestamps, a clustered index could be good. As lengthy while you don't place old timestamps therefore the DBMS needs to arrange the rows physically because of the clustered index.

A clustered index may be the order the records exist around the disk. There'll always be one, no matter whether you specify one or otherwise, as there has to be a purchase around the disk.

It is perfectly normal for that primary answer to be also the clustered index, but this do not need to be.

If you're doing batch card inserts, you'll probably have mulitple records placed with similar timestamp. Clearly this can't then be considered a primary key.

To be able to perform a query like "Choose .. WHERE timestamp > X" a catalog around the 'timestamp' area will enhance the performance of this query, whether it's clustered or otherwise.

If the index around the 'timestamp' area ought to be clustered and whether additionally, you will need other indexes will rely on all of the queries you will have to perform around the data.