Hi, The details are as follows mysql> explain select ucp.user_id as RESULTANT_ID from user_course_payment ucp,crm_sales_contact sc, crm_contacts c where c.contact_id=sc.contact_idand c.product_id = sc.product_id and sc.product_id=1 and sc.resultant_id = ucp.user_id and sc.resultant_id !='' and c.operator_id=1 and date_format( ucp.payment_date,'%Y-%m-%d') >= '2001-12-12' and date_format( ucp.payment_date,'%Y-%m-%d') <= '2007-12-12' GROUP BY ucp.user_id; +----+-------------+-------+--------+--------------------------------+-------------+---------+---------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+--------------------------------+-------------+---------+---------------------+------+----------------------------------------------+ | 1 | SIMPLE | c | ref | PRIMARY,operator_id,product_id | operator_id | 4 | const | 2 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | sc | eq_ref | PRIMARY | PRIMARY | 4 | dip.c.contact_id | 1 | Using where | | 1 | SIMPLE | ucp | eq_ref | PRIMARY,user_course_pay_comp1 | PRIMARY | 10 | dip.sc.resultant_id | 1 | Using where | +----+-------------+-------+--------+--------------------------------+-------------+---------+---------------------+------+----------------------------------------------+ 3 rows in set (0.00 sec)
Regards, Krishna On 8/14/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: > > Hi Krishna, > Its the bottom-up approach in all db's which follow SQL standards. > Can u please show us the explain plan of the below query. > > regards > anandkl > > > On 8/14/07, krishna chandra prajapati <[EMAIL PROTECTED]> wrote: > > > > Hi all, > > > > i have to optimize complex join queries. i am not able to understand how > > the complex queries gets executed. Whether it follow top-down or bottom-up > > approach. There is millions of data. > > > > The queries is: > > > > select ucp.user_id as RESULTANT_ID from user_course_payment > > ucp,crm_sales_contact sc, crm_contacts c where > > c.contact_id=sc.contact_id > > and c.product_id = sc.product_id and sc.product_id=1 and > > sc.resultant_id =ucp.user_id and sc.resultant_id !='' and > > c.operator_id=1 and date_format(ucp.payment_date,'%Y-%m-%d') >= > > '2001-12-12' and date_format(ucp.payment_date,'%Y-%m-%d') <= > > '2007-12-12' GROUP BY > > ucp.user_id; > > > > Please help me. > > > > -Regards, > > Krishna > > > > > > >
