Sql-server – How the indexes are managed

indexoraclesql server

I am trying to understand how the indexes are managed for below common data types:
1- Numeric (Integer, Decimals)
2- String (Varchar, Char)
3- DateTime

I have few questions:

1- How the indexes are stored for different data types? For example if have Numeric data like (100, 101, 100, 900, 700) & string data like (AAA,BBB,CCC,AAA,ABC) how the numericcolumn & stringcolumn index will store this data on disk?

2- Is there any different between the retrieval of a Numeric & String indexes? For example if i try the SELECT statements on numericIndexed column & on sringIndexes columns how they are retrieved?

3- Is the indexes are stored different by SQL Server & Oracle? OR they use the same logic?
Regards.

Best Answer

For SQL Server, an index entry is stored pretty much the same way as a normal record except for the row header. Also, index and record entries are never on the same page

Now, anything I add to this would be copy/paste of articles by cleverer folk than me, so I'll refer you to Paul Randal: Inside the Storage Engine: Anatomy of a record

Also, all indexes in SQL Server are B-Tree. Tables with clustered indexes in SQL Server are the same as Oracle Index Organised tables

As for a SELECT, you have to consider if an index is "covering" or not usually. This means the entire SELECT can be satisfied from the index only. Otherwise it may not be used or generate key lookups. For more, see

Related Solutions

Sql-server – Will an index that is a partial key of the begining of another index perform any better than the longer index

There really isn't even any guarantee that the optimizer will use the index in the first place. The difference of only one column between the two indexes is (for the most part) trivial. But if it did, any performance gain you would see (if any) would be trivial.

The reason for this is in how the B-Tree index is implemented by SQL Server. Both indexes are equally capable of satisfying the predicate (WHERE clause) and therefore locating only the rows that meet the search parameters. The only difference between the two would be how many pages in the index SQL Server would have to read in order to locate the required rows. And how many pages will ultimately be determined by the size of the index rows (Storage size in bytes of (TransactionDate, ClientID) vs. the alternative, with only the size of the State column being the difference between the two.

Just as an example, if a single index row was 60 bytes, SQL Server would only have to read 2 pages in order to locate a row up until there were around 2.5 million rows in the index. Then it would only require 3 pages to be read until the table reached more than 300 million rows.

So, what does that mean? Whether it is looking through an index of 2 or all 3 columns, SQL Server is still only looking through the same number of pages to locate the rows, unless the State column is so large as to create a significant difference in the size of a single row, thus causing the index pages to fill considerably faster. Any noticeable performance difference would only be caused if SQL Server had to read through more levels of the index to satisfy the query (3 levels vs. 4 levels, etc) and a single column of text, especially if State is a 2 char state abbreviation, simply won't be enough to make a significant difference.

If you really want to boost performance of the query, and the table is quite large, you might be better off exploring table partitioning, or if not on Enterprise Edition perhaps a partitioned view, all depending on whether there are certain ranges that are primarily searched and others that are searched much less often.

Mysql – InnoDB vs MyISAM with many indexes

There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.

Based on this statement, let's play with some numbers. Lets's say there are 500 writes a day and 20,000 reads a day. That computes out to the following

97.56% reads per day
2.44% writes per day
40 reads / write

As much as I love InnoDB, I would have to choose MyISAM in this case for several reasons

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run of the the following to physically shrink the InnoDB table mydb.mytable:
- OPTIMIZE TABLE mydb.mytable;
- ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)