David A. Lee
|
| Posted: 07/23/2001, 5:01 PM |
|
I was struggling with some AWFUL performance using CC and MySql.
What seemed like a straight-forward lookup with 3 "Field Joins" in CC
was taking about 2-15 seconds. I diagnosed the SQL that CC was generating,
and found that mysql didnt optimize it very well.
I am using 2 tables like this : (simplified for discussion)
table url_topic {
int t_id ; // topic id primary key
int u_id ; // url ID
....
} ;
table url {
int u_id ; // primary key url ID
char[] u_url;
char[] u_title;
char[] u_desc;
...
};
I used a GRID form to display 'url_topic' (its linked to a Tree form ...)
for all topics where t_id=topic. Then I added several "u_id" fields,
and named them "Title" , "Description" , "URL" etc ...
In CC I set them up as the appropreate join ( through u_id to table url )
There are only 2 tables involved ... but (for probably obvious reasons)
the SQL generated by CC used 3 table aliases for 'url' like this
select t.t_id as t_t_id, u1.u_id as u1_u_id , u1.u_title as u1_u_title,
u2.u_id as u2_u_id , u2.u_desc as u2_u_desc , u3.u_id as u3_u_id ,
u3.u_url as u3_u_url from url_topic t, url as u1 , url as u2 , url
as u3 ...
where ( t.u_id = u1.u_id ) and ( t.u_id = u2.u_id ) and (t.u_id =
u3.u_id) ... blah
Quite ugly, but totally functional and 'correct'.
Problem is that MySQL did not optimize this very well (it didnt realize that
u1,u2,u3 were the same table!) so queries took forever.
After some playing around ... I discoverd that if I rewrote the SQL as a
2 table join instead a 4 table join ... (replace u2->u1, u3->u1 )
I got a 10 fold increase in speed !!!!
So ... this note is both a gripe and a compliment.
Gripe) CC should try to optimize the output joins using as few table aliases
as possible.
Compliment) Thank GOD for CUSTOM SQL !!! I simply put in my hand-optimized
SQL, removed the joins in CC ... and voila ! problem solved ! very very
little
work (after I figured out what to do that is ...)
--------------------------------------------------
David A. Lee
Dal Enterprises Inc.
dave@calldei.com
http://www.calldei.com
|
|
|
 |
|
