Database Design - Creating Index

Posted on 2005-04-28
Last Modified: 2010-03-19

I want to create some index on a specific table to help boost performance for data retrieval.  The data would be searched on field1 and also on field2:

For example:
1) Where field1 = 1
2) Where field2 > 300
3) Where field1 = 20 and field2 > 400

Also, the two fields would be included in the order by clause (e.g. "order by field1, field2").

Should creating an index on field1 and a separate index on field2 is enough to take care everything stated above?  Or do I also have to create a combined index of (field1, field2) besides creating a separate index for field1 and field2?  What is the best approach for creating index in the above scenario?

Question by:jsmui
    LVL 68

    Assisted Solution

    Separate indexes will likely be better if you use the command "WHERE col2 > value" fairly frequently.  If that search is rare, you should probably combine them as (col1, col2).

    Either way col1 should probably be clustered.
    LVL 28

    Accepted Solution

    I suggest creating an index on field1 by itself and another index on field2, field1 in that order.  So if the query only specified field1, it will use the first index.  If the query specified only field2, then it will use the second index.  And if both fields are specified, the second index will also be used.
    LVL 68

    Expert Comment

    Actually if you cluster field1 you won't explicitly need to add it to the index on field2 -- SQL will automatically add it.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now