Index question

Question

Index question

Admingod

SSCertifiable

Points: 5997
More actions
October 28, 2020 at 9:21 pm

#3802253

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?

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 2

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.

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

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Admingod SSCertifiable Points: 5997 More actions · Answer 4

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 5

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.

_____________
Code for TallyGenerator

Admingod SSCertifiable Points: 5997 More actions · Answer 6

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

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 7

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.