Basic Index advice needed please?
-
I am trying to increase the performance on a couple of tables on my database but I'm not sure which columns i should put indexes on.
I have a table with 3 columns:
mainMenuID | mainMenuTitle | mainMenuIndex
The mainMenuID is the Primary Key so it is indexed automatically right? In my SQL statements I ALWAYS select * and order by mainMenuIndex. Given this fact, should I put an index on mainMenuIndex because this is what I order by? What about the fact that there are only ever about 10 rows? Would this make the indexing pointless?
My other table which has thousands of rows is as follows:
eventID | eventName | eventDescription | eventDate | eventTime
eventID is the Primary Key so is again Indexed automatically but his time my query is 'SELECT * FROM calendarTable WHERE eventDate = '01/01/2005' ORDER BY eventTime ASC'
Should I put an Index on the eventTime column (because it is order by column) or the eventDate column (because it is the WHERE column) or perhaps both?
What are the general rules regarding which columns to index etc? Are these rules database specific or would the same rule apply to MySQL?
Thanks in advance for any help you can offer,
Chris Gilbert
-
A couple of ideas :
For the small table. I'd put the index even if you don't expect the table to be big... you never know, in 10 years, what that db is gonna look like. Actually I'd make that index unique for mainMenuIndex, mainMenuTitle. This would assure integrity of the data and a usuable index for the order by.
For the big table :
Combine eventdate and eventime into a smalldatetime column (I assume here that you have no events over 60 years old and nothing past 2040 planned and that you don't need precision to a ms
).Make that new column the first column of the new clustered index key, the second column being the eventid.
Add a new unique non clustered index on eventid.
Then look how fast this runs :
SELECT EventDate, EventId, EventName, EvenDescription FROM dbo.calendarTable WHERE eventDate >= '01/01/2005' and EventDate < '02/01/2005' ORDER BY eventDate
A few last pointers.
1 - Don't use select * from. It's a worst pratice to so and it's gonna byte you one day or another.
2 - Use the owner when referring to any object (dbo.tablename). This will give better performance, won't break ownership chaining, save you from a lot of almost impossible to track bugs and is just the right thing to do
.3 - Stop using suffixes like SomeNameTable. We know it's a table, you're selecting data from it. Just use a simple prefixe on other objects to mark the difference. It'll save you a lot of typing and a lot of redundant information.
Ex of prefixes :
fn : functions
vw : views
USP : stored procs
-
Remi, just a quick question about your 3rd point... I've always thought it a good idea to prefix things like tables (tblMyTable) to make it clear, maybe 3 years down the line, that this is a table - a bit like commenting your own code. So is it just tables that you would suggest not prefixing? Could you give some guidelines on this please?
I did see a discussion on prefixing columns with the datatype (eg intSSN) but the argument AGAINST that was that the datatypes might change over time and then the prefix would hinder the readability of the code
Sharing knowledge saves valuable time!
Simon Martin -
Hmm..
Simon Martin, a quick question: When you convert a table 'into' a partitioned view and you have to stick with the old name for not breaking applications, how would you react (3 years from now) when you are selecting from a view called SomethingTable?
-
A couple of other things to consider...
Columns frequently used in WHERE clauses are good index candidates.
Investigate your indexing solution versus your queries using Execution Plan. Be sure that you are getting (Clustered) Index Seeks where possible and avoid Bookmark Lookups. If you encounter Bookmarks consider adding covering indexes.
You don't mention what index will be clustered. By default the Primary Key will be but is that the one you want? Clustering orders the data physically on the logical sort the key represents. If you retrieve ranges of data based on some column value, consider using your clustered index there to improve those retrievals.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
I was referring to the suffixe table. As for my convention I take for granted that I select data from tables. If not, then it's from vwsomething or fnSomethng, where I can easily track the object anyways. Simple, and it's only 2 characters to type.
-
Hey G Bryant McClellan thanks for the pointers.
You say in reference to Clustered PK's 'By default the Primary Key will be but is that the one you want? Clustering orders the data physically on the logical sort the key represents.'. I'm not 100% sure that i understand what your saying?
I have a number of queries where I ALWAYS select everything and Order By ASC on a Column other than the PK. For example SELECT * FROM mainMenuTable ORDER BY mainMenuIndex. In this table my Clustered PK is mainMenuID which as you can see isn't actually used in the query (or any query for that matter). Are you saying that if I added mainMenuIndex as the Clustered Index on this table, my data would automatically be sorted ASC (or decending depending which I chose) therefore meaning that I could just write SELECT * FROM mainMenuTable and get the same results as the query including the Order By?
Thanks in advance,
Chris Gilbert.
-
The short version is : the only way to garantee an order in a select is to use the order by clause, otherwise the data will be sent as it is retrieved from the drive.
However, the real answer here is, if you don't need the column, don't keep it... and try to make the clustered index as usable as possible.
-
..and even if you table has a clustered index on the column you want ordered. Don't depend on it. Doing a join might re-sort the table in some other, more efficient way. And if you have an ORDER BY on a column that has a clustered index, you do not loose out on anything. SQL Server does not sort the data an extra time.
And last but not least. If you rely on the clustered index to do your sorting, what happends when someone clusters another column/columns for some other query that might gain performance? Will that break your application or what would happen?
-
Exactly why I used big red fonts on "THE ONLY WAY"
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply