Thanks for the info Adam.

I was looking for ways to optimize existing SQL. I guess I started looking in the wrong place :)

Vlad

----- Original Message ----
From: Adam <s3057043@...>
To: firebird-support@yahoogroups.com
Sent: Thursday, January 25, 2007 12:03:20 AM
Subject: [firebird-support] Re: 2 Very simple index questions













--- In firebird-support@ yahoogroups. com, Vlad Orlovsky

<vlad.orlovsky@ ...> wrote:

>

> Hi All,

>

> I have 2 very basic questions about indexes.

>

> 1) Do I need to create a separate index for a column in a table that

is a foreign key?



No. An ascending index is automatically created on any constraint,

including primary keys, unique constraints and foreign keys so this is

not necessary (unless you require a descending index on the foreign

key field for some other reason).

>

> Ex:

> ROLES table

> ROLE_ID INTEGER -- PK

> ROLE_NAME VARCHAR(50)

>

> USERS table

> USER_ID INTEGER -- PK

> ROLE_ID INTEGER -- FK ?????

> USER_NAME VARCHAR(50)

> ....

>

> 2) Which SELECT is faster(using above tables):

>

> SELECT * FROM USERS WHERE USER_ID = 1 AND ROLE_ID =10

> -- OR

> SELECT * FROM USERS WHERE ROLE_ID =10 AND USER_ID = 1

>

They are both the same speed. The optimiser is smart enough to not

care which way you write it. Although the index is available on both

fields, it will prefer the primary key. Firebird is also able to

combine both indices if required, but probably wont bother in this case.



Adam














<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->









____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

[Non-text portions of this message have been removed]