Remember that if you dont use an index on a colum and you search by that
criteria, there would be a whole table scan :(.
Its recommended that you always index those columns that appear on a search
and/or an order (where column_a='yyy' order by column_b) and those involved
on joins; generally speaking joins are always based on primary keys, so, if
you have already defined the primary keys there is an index associated with
the columns of that primary key (just remember that order matters), so the
join use the index and not the whole table.
Hope this helps.
Carlos
On 8/10/06, Ratheesh K J [EMAIL PROTECTED] wrote:
Hello All,
Need a suggestion for this:
We have tables which have very few number of rows ( less than 600 ). For a
long period of time the number of rows are going to remain almost the same.
1) Is it better to index the columns of such tables?
2) There are Joins on this table and then a search on certain fields of this
table in the where clause. Does index make a difference?
Example: TBL_XXX and TBL_YYY are related through FLD_ACC_ID. This fld is
indxed on both the tables. None of the others are. Would adding indexes help
on such tables ( only 342 rows in both )
DESC TBL_XXX;342 Rows
-
FLD_EMAIL_ID varchar(150) PRI
FLD_ACC_ID int(11) unsigned PRI 0
FLD_PRIMARY_FLAG tinyint(1) YES 0
--
DESC TBL_YYY; 342 Rows
FLD_ACC_ID int(11) unsigned PRI 0
FLD_ACC_NAME varchar(32) YES \N
FLD_MAIN_ZONE tinyint(1) 0
FLD_FOR_ZONE_ID int(10) unsigned YES 0
FLD_STATE tinyint(1) unsigned 0
FLD_DOMAIN_ID tinyint(4) unsigned YES 0
FLD_SCAN_CUST_SERIAL_NUM_FLAG tinyint(1) unsigned YES 0
FLD_SCAN_CUST_NO_SUPPORT_FLAG tinyint(1) unsigned YES 0
FLD_INBOX varchar(64) YES \N
FLD_INBOX_PASSWD varchar(16) YES \N
FLD_INBOX_LOCK_FLAG tinyint(1) YES 0
FLD_INBOX_LOCK_DATE_TIME datetime YES \N
FLD_EMAIL_INBOX_OUTWARD varchar(32) YES \N
FLD_WEBMAIL_INDIVIDUAL_FLAG tinyint(1) unsigned YES 0
FLD_WEBMAIL_INDIVIDUAL_TYPE tinyint(5) unsigned YES 0
FLD_EMAIL_DONT_PROCESS_FLAG tinyint(1) unsigned 0
FLD_ATTACH_MAX_UPLD_SIZE tinyint(2) unsigned YES 0
FLD_ATTACH_MAX_RECV_SIZE tinyint(2) unsigned YES 0
FLD_ADD_BY int(11) 0
FLD_ADD_DATE_TIME datetime -00-00 00:00:00
FLD_UPDATE_BY int(11) YES \N
FLD_UPDATE_DATE_TIME datetime YES \N
FLD_PARENT_ID int(11) unsigned YES 0
FLD_THREAD_ID int(11) unsigned YES 0
FLD_POS tinyint(5) unsigned YES 0
FLD_LEVEL tinyint(5) unsigned YES 0
FLD_OWNER_COMPANY_ID int(10) unsigned 0
FLD_FOR_COMPANY_ID int(10) unsigned YES 0
FLD_NO_DIRECT_SUBMISSION_FLAG tinyint(1) YES 0
Thanks,
Ratheesh Bhat K J
