Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 simple SQL indexing question

views
     
TSnarf03
post Jan 16 2017, 11:11 PM, updated 8y ago

Look at all my stars!!
*******
Senior Member
4,544 posts

Joined: Dec 2004
From: Metro Prima, Kuala Lumpur, Malaysia, Earth, Sol


Lets say i have a big table with million of rows, it has multiple fields, but we just talk about 2 of them, Status and TimeStamp.

Status(bigint) is indexed, but TimeStamp(datetime) is not indexed.

Status can be only -1, 0, and 1.

TimeStamp is the date/time the row created.

Only up to 10 rows in the table(which has millions) the status can be 1, the rest must be 0 or -1.

I only care about those rows that has status of 1.

And if i issue the following command

QUOTE
select * from table where Status=1 and DATEDIFF(Minute, getDate(), TimeStamp)<10


I want to know if the sql server will have to process and compare the whole table of million of rows, or its smart enough to filter those 10 rows from status then only compare ?

I dont have the millions of record now, and thus i need to ask =P
dstl1128
post Jan 17 2017, 08:12 AM

Look at all my stars!!
*******
Senior Member
4,463 posts

Joined: Jan 2003
It is smart enough. The "status=1" already narrow down search pool.

Well you can use EXPLAIN to observe your SELECT statement. It will show you the "index name" that SQL uses for query and the 'approximate' number rows it 'scans'.
alien3d
post Jan 17 2017, 08:44 AM

Look at all my stars!!
*******
Senior Member
3,740 posts

Joined: Mar 2009
QUOTE(narf03 @ Jan 16 2017, 11:11 PM)
Lets say i have a big table with million of rows, it has multiple fields, but we just talk about 2 of them, Status and TimeStamp.

Status(bigint) is indexed, but TimeStamp(datetime) is not indexed.

Status can be only -1, 0, and 1.

TimeStamp is the date/time the row created.

Only up to 10 rows in the table(which has millions) the status can be 1, the rest must be 0 or -1.

I only care about those rows that has status of 1.

And if i issue the following command
I want to know if the sql server will have to process and compare the whole table of million of rows, or its smart enough to filter those 10 rows from status then only compare ?

I dont have the millions of record now, and thus i need to ask =P
*
If scare sub select

Select from (select status 1) where date filter x

scar_face008
post Jan 17 2017, 09:25 AM

opis boi
****
Senior Member
585 posts

Joined: Jan 2007
From: Ranau, Sabah


maybe not relevant but this is what i remember reading last year when i was googling around. note that i am a beginner.
1) if value is fixed (not variable), maybe can use enum instead of bigint on status to minize space usage.
2) if you know maximum only 10 result will be returned, can use limit 10.
badai
post Jan 18 2017, 02:18 PM

Enthusiast
*****
Senior Member
986 posts

Joined: Jan 2003
you already index status, so it already knew which row have 1. then it will search for timestamp in those rows.

if you index timestamp too, it will filter out instead of search.
dstl1128
post Jan 18 2017, 03:01 PM

Look at all my stars!!
*******
Senior Member
4,463 posts

Joined: Jan 2003
Not much of a gain in lookup performance if that timestamp is indexed since there are only max of 10 rows. Whereas adding additional index would slow write performance.


donhue
post Jan 18 2017, 04:22 PM

On my way
****
Senior Member
586 posts

Joined: Jul 2007


because your records are inserted in sequence according to timestamp, and no updates on the timestamp field, I would create a clustered index on the timestamp field.. this will make queries based on the timestamp much more efficient..

not too sure if it's worth indexing status because there's only 3 unique values though.. you can read this for more info

finally, it's better practice to write your query in this form:
CODE
select * from table where Status=1 and TimeStamp > DateAdd(minute,-10,getDate())

this evaluates dateAdd(minute,-1,getDate()) into a constant value and does a comparison, compared to having a function calculate the time difference.


TSnarf03
post Jan 19 2017, 01:21 PM

Look at all my stars!!
*******
Senior Member
4,544 posts

Joined: Dec 2004
From: Metro Prima, Kuala Lumpur, Malaysia, Earth, Sol


ok i just spend some time generating 10 mil rows just for testing, seems like SQL server is smart enough to only perform that date calculation on the few selected status rows.

 

Change to:
| Lo-Fi Version
0.0124sec    0.40    5 queries    GZIP Disabled
Time is now: 28th March 2024 - 11:30 PM