List:General Discussion« Previous MessageNext Message »
From:Dave Date:May 21 1999 12:10am
Subject:Index optimization
View as plain text  
Hi all,

I am attempting to optimize my indexing so as to provide for the fastest
queries possible. I have read and re-read and re-read the section Explain
Select in the documentation and somehow it is still not making sense...

I have the following tables/indexes:


ar_ledger:
    entry_id int(10) unsigned auto-inc,
    cust_id int(10) unsigned,
    entry_date date,
    amount double(8,2),
    type tinyint(3) unsigned,
    inv_id int(10) unsigned,
    description char(100),
    ...
    ...
The fields indexed in ar_ledger are:
    entry_id <-> cust_id as Primary
    date
    type
    amount
    inv_id
--------------------------------------------------------------------------
customers:
    cust_id int(10) unsigned auto-inc,
    first varchar(30),
    last varchar(30),
    address varchar(30),
    city varchar(30),
    state varchar(2)
    dayphone varchar(12),
    evephone varchar(12),

    ...
    ...
The fields indexed in customers are:
    cust_id as Primary
    first
    last
    dayphone
    evephone
--------------------------------------------------------------------------
passwd:
    pass_id int(10) unsigned auto-inc,
    pw_name varchar(5),
    pw_passwd varchar(13),
    pw_gecos varchar(30),
    cust_id int(10) unsigned,
    first varchar(30),
    last varchar(30),
    dayphone varchar(12),
    evephone varchar(12),
    ...
    ...

The fields indexed in passwd are:
    pass_id <-> pw_name <-> cust_id as Primary
    first
    last
    dayphone
    evephone



Now, while writing this email I think I noticed a problem with the Primary
index in the passwd table, combining fields of different type into an index
could not be all that good huh?


Anyway, I have tried select using:

select
    c.cust_id,c.first,c.last,sum(a.amount) as balance,p.pw_name
from
    ar_ledger a
    left join lists.customers c using (cust_id)
    left join system.passwd p using (cust_id)
where
    c.first like '%gra%' || c.last like '%gra%' || p.pw_name like '%gra%'

group by c.cust_id


which provides me the wrong balances anyway...but I don't care about that
yet...


I am getting a eq_ref join on ar_ledger but a only a ref join on passwd...

following is the explain:

+-------+--------+---------------+---------+---------+-----------+------+---
---------+
| table | type   | possible_keys | key     | key_len | ref       | rows |
Extra      |
+-------+--------+---------------+---------+---------+-----------+------+---
---------+
| a     | ALL    | NULL          | NULL    |    NULL | NULL      | 1820 |
|
| c     | eq_ref | PRIMARY       | PRIMARY |       4 | a.cust_id |    1 |
|
| p     | ref    | cust_id       | cust_id |       4 | c.cust_id |   23 |
where used |
+-------+--------+---------------+---------+---------+-----------+------+---
---------+


Now I don't know if this is completely optimized or not, according to the
docs on explain it doesn't appear so. I am wondering if anyone might have
ideas on why?


The data I am looking for (if someone has a better idea) is:

All records from ar_ledger with a matching c.cust_id (amount field summed)
All records from passwd with a matching c.cust_id



Any help is greatly appreciated..

-Dave
California Internet Connection









Thread
Index optimizationDave21 May
  • Re: Index optimizationChristian Mack21 May