1. I have a table called companies which has a foriegn key called countryid. countryid references the primary key of table countries.
table countries has the following fields:
countryid - primary key auto increment
countryname
The table Companies is regulary searched on countryid. Would you therefore recomend indexing this field given the frequent searches it requires or would you simply redesign the query and do an inner join on the countries table:
e.g a search based on a non indexed countryid field
Code:
Select * from Companies where countryid = 1;
or
Code:
Select * from companies
inner join countries as c on
c.countryid = companies.countryid
Where
c.countryid = 1;
Since the second example passes the condtion to the countries table and searches on the primary key would this be more faster than searching on a non indexed field as in the first example or would you simply index the countryid in the companies table and use the first example? My minds boggeling!
2. I have another scenario aswell.
The companies table has the following fields:
companyid - primary key auto increment
userid - foreign key references primary key from table users
Again this table is frequently searched by users by using their userid to list all companies assigned to them. Would you therefore recommend indexing the userid or redesign the query using an inner join.
e.g a search based on a non indexed userid field:
Code:
Select * from companies where userid = 1;
or
Code:
Select * from companies
inner join users as u on
u.userid = companies.userid
Where
u.userid = 1;
Alternativeley would you make the userid in the companies table into a compound primary key with the companyid. In which case the first example would be doing a search on an indexed field in any case?
dBforums
>
Indexing a foriegn key.
04-30-07, 11:12
Linear Mode
