Index for Transactions ?

[helo worl]

1. Agent
select wk_profile from agent where agentid='bla bla'

What should be index for agent?


2. AgentProfile
SELECT rate1 FROM agentprofile WHERE profile='Sub Query' And hour2>='Bla Bla'

What should be index for agentprofile?

3. Agentcost
SELECT rate FROM agentcost WHERE Agentid='Bla' and rateid ='sub query' and c_code=SUBSTR(cdr.destno, 1,agentcost.c_Code_len) ORDER BY agentcost.c_code_len DESC LIMIT 1

What should be index for Agentcost ?


4. Cdr
select compid,destno,blegzone,duration,(sub querys) from cdr where date='bla' and duration>'bla';

what should be index for cdr ?

//this takes 30 minutes

mysql> explain select compid,destno,blegzone,duration,
-> (
-> SELECT rate FROM agentcost WHERE Agentid='b01' and rateid =
-> ( SELECT rate1 FROM agentprofile WHERE profile=(SELECT wk_profile from agent where agentid='b01') And hour2>=left(cdr.Time,2) LIMIT 1) And
-> c_Code = SUBSTR(cdr.destno, 1,agentcost.c_Code_len)
-> ORDER BY agentcost.c_code_len DESC LIMIT 1
-> ) As Cost,date,time
-> from cdr where date='03/11/2005' and duration>0;
+----+--------------------+--------------+-------+---------------------------------------+-----------+---------+------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------+-------+---------------------------------------+-----------+---------+------------+-------+-----------------------------+
| 1 | PRIMARY | cdr | ref | Date,b | Date | 100 | const | 16172 | Using where |
| 2 | DEPENDENT SUBQUERY | agentcost | ref | AGENTID,AGENTID_2,AGENTID_3,AGENTID_4 | AGENTID_3 | 23 | const,func | 1256 | Using where; Using filesort |
| 3 | DEPENDENT SUBQUERY | agentprofile | ALL | C | NULL | NULL | NULL | 3 | Using where |
| 4 | SUBQUERY | agent | const | AGENTID,A | AGENTID | 3 | | 1 | |
+----+--------------------+--------------+-------+---------------------------------------+-----------+---------+------------+-------+-----------------------------+
4 rows in set (0.22 sec)

mysql>



Edited 1 time(s). Last edit at 11/04/2005 03:06AM by helo worl.