Indexes question....

  • Hi,

    If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?

    But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!

    Is it worth having both indexes?

    The queries can have any column on the search criteria since the App UI allows it.

    Thanks,

    Pedro



    If you need to work better, try working less...

  • PiMané (11/27/2012)


    Hi,

    If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?

    Yes.

    But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!

    Is it worth having both indexes?

    Maybe.

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Check the below link

    http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI406

  • In a first approach is it "wise" to just create one of the indexes (Col1, Col2, Col3) and not both (Col1, Col3, Col2) and after analyzing the workload for a few days/weeks see what SQL Server engine suggests?

    Start from a simple approach and not the "big picture" since we can be creating indexes that won't be used?

    If we have a 4 columns searchable table we can have loads of indexes or just create 4 (start with one different column and add the others by any order)...

    Thanks,

    Pedro



    If you need to work better, try working less...

  • Just one more thing...

    Is Col1, Col2, Col3 equal to having Col1, Col2 ?

    Some tables have indexes like this an the wider indexes probably make the other obsolete..

    Thanks,

    Pedro



    If you need to work better, try working less...

  • GilaMonster (11/27/2012)


    PiMané (11/27/2012)


    Hi,

    If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?

    Yes.

    But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!

    Is it worth having both indexes?

    Maybe.

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Hi,

    After reading your blog about indexes http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/ I made a query to get "duplicate" indexes considering that the first two columns are important and the other can be "joined", for example an index col1, col2, col3 and col1, col2, col5 can be "joined" in a col1, col2, col3, col5... I'm creating wider indexes and removing small ones.

    I'm still not considering the include columns but joining two "duplicate" indexes include columns is the next step..

    DECLARE @FirstColumnsKeys INT = 2

    ;WITH IndexInfo AS (

    SELECT

    SCHEMA_NAME(t.schema_id) SchemaName,

    t.name TableName,

    i.name IndexName,

    (SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id <= @FirstColumnsKeys ORDER BY ic.index_column_id FOR XML PATH('')) FirstKeyColumns,

    (SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id > @FirstColumnsKeys ORDER BY ic.column_id FOR XML PATH('')) OtherKeyColumns,

    (SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 1 ORDER BY ic.column_id FOR XML PATH('')) IncludeColumns

    FROM sys.indexes i

    INNER JOIN sys.tables t ON i.object_id = t.object_id AND t.is_ms_shipped = 0

    WHERE i.type != 0)

    SELECT i1.* FROM

    IndexInfo i1 INNER JOIN IndexInfo i2 ON i1.SchemaName = i2.SchemaName AND i1.TableName = i2.TableName AND i1.IndexName <> i2.IndexName AND i1.FirstKeyColumns = i2.FirstKeyColumns

    ORDER BY i1.SchemaName, i1.TableName, i1.FirstKeyColumns, i1.OtherKeyColumns, i1.IndexName

    Still working on the query but this is as it is so far...

    Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes? If I have a FK on col2, col3 and col4 can an index with col2, col3, col5, col4 be considered "good" for FK validation?

    Thanks,

    Pedro



    If you need to work better, try working less...

  • Pedantic nitpick. The ; is a statement terminator. It does not begin statements. ;WITH is an abomination that I wish would go away.

    PiMané (11/28/2012)


    DECLARE @FirstColumnsKeys INT = 2;

    WITH IndexInfo AS (

    Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes?

    Maybe. It depends how unique the combinations are (and that goes in general, not for foreign keys).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/28/2012)


    Pedantic nitpick. The ; is a statement terminator. It does not begin statements. ;WITH is an abomination that I wish would go away.

    Agree... sorry 🙂

    Maybe. It depends how unique the combinations are (and that goes in general, not for foreign keys).

    Do the indexes on FKs have to have the exact same columns by the same order to have impact?

    Thanks,

    Pedro



    If you need to work better, try working less...

  • About FKs and indexes...

    If a FK has Col1, Col2, Col3 by this order is a Col1, Col2 index any good?

    It's probably better than nothing but should a Col1, Col2, Col3 index exist or a Col1, Col2, Col3, Col4, .... A Col1, Col2, Col4, Col3 probably is the same as just Col1, Col2 for a FK validation...

    Thanks,

    Pedro



    If you need to work better, try working less...

  • PiMané (11/28/2012)


    About FKs and indexes...

    If a FK has Col1, Col2, Col3 by this order is a Col1, Col2 index any good?

    yes but always (not there, where col3 is involve)

    PiMané (11/28/2012)


    About FKs and indexes...

    It's probably better than nothing but should a Col1, Col2, Col3 index exist or a Col1, Col2, Col3, Col4, .... A Col1, Col2, Col4, Col3 probably is the same as just Col1, Col2 for a FK validation...

    no .. i will suggest you to read article referred/written by Gail

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi,

    I read it...

    A wide index with Col1, Col2, Col4, Col3 with a FK Col1, Col2, Col3 seems the same as an index Col1, Col2 since the "big" one will make a seek on Col1, Col2 and then a scan on Col4 for Col3 since it has no way of seeking Col3 directly.

    So the "big" index and the Col1, Col2 seem the same... aside the small index needs less reads to get the same data from Col1, Col2...

    Also about FKs... I have a table with 10 FKs... by creating indexes for all FKs will be adding 10 indexes... Is that a good thing to do? One wide index is better than lots thinner indexes but FKs should have indexes and most of then are thinner (1 or 2 columns tops) ... It's a bit contradictory...

    Thanks,

    Pedro



    If you need to work better, try working less...

  • Hi,

    Regarding this question I read some articles that consider partial duplicate indexes those indexes with the same first column and "reversible" indexes those indexes with the same columns but with reverse order.

    In cases where the application can filter by any table column, say the table has 30 or 40 columns, the only way to determine the best index(es) is by monitoring for some time the app usage and then determine the best one(s)...

    But in any case the FKs indexes should always be created ... but should they all have the exact columns and order?

    For example:

    Table1: T1_1, T1_2, T1_3, T1_4

    Table2: T2_1, T2_2, T2_3

    Table3: T3_1, T3_2, T3_3, T3_4, T3_5

    Table2 references Table1 => T2_1 => T1_1 and T2_2 => T1_3

    Table3 references Table1 => T3_1 => T1_1 and T3_2 => T1_2

    In this example should we create 4 indexes (2 for T1 and 1 for T2 and T3) or is 3 enough (1 for T1 with T1_1, T1_2, T1_3) ? Or assuming T1 has less than 1.000 rows should and index on T1_1 be enough?

    Thanks,

    Pedro



    If you need to work better, try working less...

  • PiMané (11/28/2012)Also about FKs... I have a table with 10 FKs... by creating indexes for all FKs will be adding 10 indexes... Is that a good thing to do? One wide index is better than lots thinner indexes but FKs should have indexes and most of then are thinner (1 or 2 columns tops) ... It's a bit contradictory...

    I asked this cause we have a table with 30 FKs with just one column... 30 narrow indexes...

    They won't have impact on updates (not much at least) cause they are on data that isn't changed a lot once it's inserted (payment method, dispatch method, ...).

    So it's probably better to have them so the check validations are fast (seeks instead of scans), right?

    Thanks,

    Pedro



    If you need to work better, try working less...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply