Hi all,

I need some help on indexing a table around 10 million rows.
Basically, the table contains

Table: Account
unique_number
account_type (There are about 10 account types)
account_number
account_name
SSN
account_amount

This table is used both for query and reports.

At first, I create an index on account_type, account_number and
account_amount. It works great except when I try to combine this table
with the address table, and I want to select the address for everyone
with a specific type. Since the query has account_type, it used this
index which made the query very slow.

I then drop and create a new index just on account_number and
account_amount, but why is the query slow when I do the following?

select unique_number
from account
where account_type = 'a'
and account_number like '999'
and account_amount = 100

What is the best way to tackle and create the appropriate index.
Should I just make the index account_number, acocunt_type,
account_amount?

Thanks for the help!

It would help if you posted the actual DDL of the Account Table and the Adress Table, and describe the values in each column and how the tables are used in the applciation.

Well, Account table has all th information regarding the account. The address table has the address information for each unique number.

I think I should take a step back and ask the most basic question.

I know it's not reasonable to create an index on the account_type by itself since there is only 10 account_type, but on the other hand account type is used in many other aggregate functions where I would be account_type and sum(account_amount).

In this basic query

select unique_number
from account
where account_type = 'a'
and account_number like '999'
and account_amount = 100


Will there a difference if I create an index on account_type, account_number, account_amount vs. account_number, account_type, account_amount?

I am not sure what you mean by:

account_number like '999'

do you mean:

account_number like '999%'
account_number like ‘%999%’
account_number = '999'
or something else?

Check out the syntax for ‘like’ in the SQL Reference manual.

An index on account_number by itself is probably OK, but an index on account_number and account_amount together might be beneficial if account number (or the 3 digits of account number that are included in the ‘like’ predicate) has many duplicate values.

But if account_amount is updated frequently, including it in the index will hurt update performance since the index row will probably need to be moved each time account_amount is changed.

Your explanation of the table DDL and the application is really not sufficient to give a complete answer.