Post reply

query not using index properly

  • March 20, 2007 at 2:40 am

    #369823

    Hi,

    i hav a query " select * from members where email='' and password='' " it returns some 100,000 rows.. it takes a long time to return data n its not using indexes too... how can i modify this query to use my index properly..? hav a non-clustered index for email and password...

  • March 20, 2007 at 2:59 am

    #695858

    100,000 records for same email and password?

    Something else is wrong besides long run-time! You should revise your business rules first.

    N 56°04'39.16"
    E 12°55'05.25"

  • March 20, 2007 at 5:23 am

    #695881

    Hi Peter,

    It is not like dat.. i need to get the rows which doesnt hav any email contact..

    And email column is not unique..

  • March 20, 2007 at 5:42 am

    #695883

    How do you store an email "not there"?

    Empty string? NULL?

    You already have an index on email column...

     

    N 56°04'39.16"
    E 12°55'05.25"

  • March 20, 2007 at 5:45 am

    #695884

    ya just empty string..

    it is givin the results correctly but only issue is the performance..

    i hav index in email column..

  • March 20, 2007 at 9:11 am

    #695973

    100,000 records is just going to take a long time to return especially when you add x number of columns, latency, network traffic, etc. It's also probably going to ignore indexes and use table scans, depending on how big the whole table is and whether or not the range you're selecting is from a clustered index.

    That's before you get into, just how selective is the index you're using since 100,000 rows consist of ''. You have to have upwards of 98% selectivity for an index to make a difference.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 21, 2007 at 10:27 am

    #696208

    Did you check your execution plan ?

    What is the reason that optimizer does not use indexes ?

    Is empty string is majority of records ?

    Have statistics been updated recently ?

    Did you check sys.dm_db_missing_index_details ? It gives you recommendations on what columns to build indexes.

    Are you using sorting ?

    You can also try hint FASTFIRSTROWS, it actually selects proper indexes for huge result sets for wide tables and when sorting is used.

     

Viewing 7 posts - 1 through 6 (of 6 total)

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