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
