List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 21 1999 6:02pm
Subject:Re: Index optimization
View as plain text  
Dave wrote:
> 
> 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

Hi Dave

Does your PRIMARY KEY on system.passwd have this ordering?
PRIMARY KEY (cust_id,pw_name,pass_id)

If not, this is your problem.

Tschau
Christian

Thread
Index optimizationDave21 May
  • Re: Index optimizationChristian Mack21 May