Index question

  • We have some indexes we have changed over the last few years to address performance problems. Currently the system is in MySql. But we are planning to do the migration to SQL Server. Not any issues in MySQL. I am thinking of making the changes in SQL Server. Let me know what you think?

  • I am not sure what the question is.  Are you wondering if I think SQL Server will work better or if SQL Server will need the same indexes as MySQL or...?

    My opinion about switching database engines is it depends a LOT.  There are budgetary constraints, potential application changes, new skill sets as administration of SQL Server is different than MySQL, etc.  It is similar and the syntax is similar, but you may need to rewrite some (or all) stored procedures, functions, etc.

    Focusing strictly on indexes, SQL Server and MySQL use different database engines so the index strategy for MySQL may not be the right choice for SQL Server.  Having not worked with MySQL, I am not certain how their indexes work, but with SQL Server, each additional index will have a negative impact on an INSERT and may help or hurt SELECT, UPDATE, and DELETE performance.  There is also a limit to the number of indexes you can have on an object (table or view).  I expect that MySQL is handling indexes in a similar manner to SQL Server.

    Knowing more what your question is though about indexes and SQL Server, we may be able to offer more help.

  • Admingod wrote:

    We have some indexes we have changed over the last few years to address performance problems. Currently the system is in MySql. But we are planning to do the migration to SQL Server. Not any issues in MySQL. I am thinking of making the changes in SQL Server. Let me know what you think?

    Can you be a wee bit more specific?  What changes are you thinking of?  You just have to know that there's going to be quite a few changes due to the migration because you're not only changing the engine, you're changing the basic paradigm and the SQL Language dialect and, yeah, that will involve your indexes, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Total agreement with Jeff & Brian.

    I will say though, to a degree, and there are hundreds of caveats to this, what makes a good index in one RDBMS, roughly translates to what makes a good index in a second RDBMS. Nothing, anywhere, is strictly one to one. However, a highly selective column is just as attractive to the optimizer in PostgreSQL as it is in SQL Server.

    Testing is going to be your bestest buddy.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks. The  indexes are supplied by vendor for application. For instance the index supplied:

    CREATE NONCLUSTERED INDEX INDEX_NAME ON dbo.TABLE(Column char(5))

    Table has around approx 2 million rows over a million column in the index has a single value and the top 3 values overall cover 3/4 of all records. This leads the index being tilting and either not selected at all.

    In this case we evaluated the search arguments and added a couple of additional columns that were used in most of the queries to alleviate the problem.

    CREATE NONCLUSTERED INDEX INDEX_NAME ON dbo.TABLE(Column1 char(5), column2 char(12), column3 char(2))

    These issues with the data selectivity are caused by our choices in configuration not the application, so keeping these indexes in SQL Server. Do you see any issues with this? I agree testing is what needs to be done but just trying to learn if there are any caveats?

  • Not gonna help.

    if statistics suggest that the 1st column in the index has low selectivity, it's very unlikely that optimiser will choose to use that index.

  • It is working well in MySQL. But you think this will not help in SQL Server?

  • I'm not that familiar with MySQL, so I can't say how it works. However, I can talk to SQL Server. The key here is that first column. There are two parts to statistics, the overall selectivity of the key or keys, and the histogram. The selectivity is defined by 1/unique values of the first column, and then a regression against the unique values of each subsequent column in the index. The histogram is a distribution of the data of the first column, and only the first column. Selectivity is a huge factor in determining the usefulness of the index. However, in most cases, the row estimates come from the histogram. If you've got a very non-selective, skewed, data set in the first column, it will affect the behavior of the index. Going for selectivity only in an index may be useful, but, you still need to deal with that first column. Also, for the index to the most useful, especially in the case of a compound key, your WHERE clause (or JOIN, others, depending) needs to reference the columns in that key. Otherwise, you're making the index selective, but the optimizer can tell that the index isn't going to help with the query.

    I hope that helps a little.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Admingod wrote:

    Table has around approx 2 million rows over a million column in the index has a single value and the top 3 values overall cover 3/4 of all records. This leads the index being tilting and either not selected at all.

    This is a possible good case for some well designed filtered indexes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

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