indexes on a large table

15 pts.
Tags:
Index Performance
SQL 2005
SQL 2005 Database
SQL Database
SQL Database Indexing
hello, i got a 5milion rows on a single table, my database is sql2005 and my select is : SELECT top * FROM tbl1 WHERE ip_from <= 3515134258 and ip_to >= 3515134258 the 3515134258 is on a sample but the format is similar: SELECT top 1 * FROM tbl1 WHERE ip_from <= [value] and ip_to >= value what is the best indexing method that is to be used here... ? the select is slow and takes from 6seconds to 70 seconds and i tried several indexes methods, the server is 4 cpu's, 16gb of ram and it is working very good except this time... thanks, benny

Answer Wiki

Thanks. We'll let you know when a new response is added.

Could you please tell us what have you tried so far ?
Have you seen the execution plan to check if your index was in fact being used ?

An index on (ip_from,ip_to) will for sure help, but I guess you already tried this.

———————–

Your best index option will be to create a clustered index on the ip_from and ip_to fields.

If your primary key is clustered (which it is by default) you will need to change the primary key to a nonclustered index first then you can create a new clustered index.

If you want to use a nonclustered index then create the index on the same fields ip_from and ip_to, and add any other columns which you need returned as included columns in the index. Then only return these columns. It is best to not return return any columns which you do not need.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bbbttt
    i tried 1-an index on both from and to (single) 2-clustered index on from and regular index on to 3-clustered index on to and regular index on from 4-regular index on from and regular index on to nothing helps (:..
    15 pointsBadges:
    report
  • Denny Cherry
    Creating two indexes isn't going to help you any as SQL will only use a single index per table. See by notes above.
    66,070 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following