a b c d of Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

a b c d of Indexes

Author

Topic

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-01-23 : 07:17:00

Hi All

we'll be trying hands on the following query which has five columns
containing data of 30,00,000 Row(s)

select id,a,b,c,d
from tb1
where a='axz' or b='axz'
and
c='axz' or d='axz'

LENGTH of columns a b c d is char(3) and id is of char(15)

Which index is useful on which col
only for the read purpose

Can u give some detail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-23 : 08:25:55

for a start when mixing 'and' + 'or' in your where statement.....remember to put brackets "()" in the appropriate locations....

a='axz' or b='axz'
and
c='axz' or d='axz'

will NOT give you the same answer as
(a='axz' or b='axz')
and
(c='axz' or d='axz')

re the indices....not sure which col would give you the best return for the cost of having it in place....i think a seperate index on each column would be the best advice, but i'm open to contradiction here....but i certainly can't see a 'combined 4 column index' being of any use to you....

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-01-24 : 02:06:38

quote:

for a start when mixing 'and' + 'or' in your where statement.....remember to put brackets "()" in the appropriate locations....

The query is

Where ((((a='AXZ') or (b='AXZ'))
and ((c='BFX') or (d='BFX')))
or (((c='AXZ') or (d='AXZ'))
and ((a='BFX') or (b='BFX')) ))

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2003-01-24 : 08:32:12

I think when you use OR, you automatically cause the optimizer not to use an index, and it is forced to do a table scan. Possibly try to write different select statements with a UNION ALL statement. If you did it this way, then a clustered index on a,b,c,d will be good with a non-clustered index on id.

***************************************
Death must absolutely come to enemies of the code!

Edited by - royv on 01/24/2003 08:32:42

Edited by - royv on 01/24/2003 08:35:32

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-24 : 09:59:31

Here's something strange (2000 sp 2): when I try running these two queries the one with the ORDER BY is faster than the one without -- it doesn't seem to work out that the parts of the UNION are already in the right order for a Merge (which will always? be faster than a hash union).


DROP TABLE tb1
GO

CREATE TABLE tb1 (
  i int NOT NULL PRIMARY KEY,
  a tinyint NOT NULL,
  b tinyint NOT NULL,
  c tinyint NOT NULL,
  d tinyint NOT NULL
)
GO

INSERT INTO tb1
SELECT n, n%13, n%17, n%19, n%23
FROM Numbers
WHERE n < 100000

GO

CREATE INDEX tb1ac on tb1 (a,c,b,d)
CREATE INDEX tb1ad on tb1 (a,d,b,c)
CREATE INDEX tb1bc on tb1 (b,c,a,d)
CREATE INDEX tb1bd on tb1 (b,d,a,c)

GO


SELECT * FROM tb1 WHERE (a=0 AND c=1)
UNION SELECT * FROM tb1 WHERE (b=0 AND c=1)
UNION SELECT * FROM tb1 WHERE (a=0 AND d=1)
UNION SELECT * FROM tb1 WHERE (b=0 AND d=1)
UNION SELECT * FROM tb1 WHERE (c=0 AND a=1)
UNION SELECT * FROM tb1 WHERE (d=0 AND a=1)
UNION SELECT * FROM tb1 WHERE (c=0 AND b=1)
UNION SELECT * FROM tb1 WHERE (d=0 AND b=1)
ORDER BY a,b,c,d,i

SELECT * FROM tb1 WHERE (a=0 AND c=1)
UNION SELECT * FROM tb1 WHERE (b=0 AND c=1)
UNION SELECT * FROM tb1 WHERE (a=0 AND d=1)
UNION SELECT * FROM tb1 WHERE (b=0 AND d=1)
UNION SELECT * FROM tb1 WHERE (c=0 AND a=1)
UNION SELECT * FROM tb1 WHERE (d=0 AND a=1)
UNION SELECT * FROM tb1 WHERE (c=0 AND b=1)
UNION SELECT * FROM tb1 WHERE (d=0 AND b=1)

Edited by - Arnold Fribble on 01/24/2003 10:00:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources