Hi Krishna, The index combination ur using is not correct, please check the cardinality of the joining columns in the where condition and create the index also, you can use an "BETWEEN" clause instead of "date_format(ucp.payment_date,'%Y-%m-%d') >= '2001-12-12' and date_format(ucp.payment_date,'%Y-%m-%d') <= '2007-12-12' "
How many rows are there in c.operator_id with value=1. regards anandkl On 8/14/07, Baron Schwartz <[EMAIL PROTECTED]> wrote: > > Hi, > > MySQL is seeking into the operator_id index on crm_sales_contact and > finding one or more matching rows where c.operator_id = 1. > > For each row it finds, it uses the value of contact_id to do a unique > index lookup into crm_sales_contact's primary key. > > It then does a unique index lookup into crm_contacts primary key to find > a value matching the resultant_id from crm_sales_contact. > > It applies a WHERE clause to satisfy the date_format() WHERE arguments. > > Finally, it builds a temporary table of the results, sorts them by > user_id, and groups them together. > > A couple of notes on this query: if it were possible for MySQL to use an > index on payment_date, you have defeated it by wrapping that column in a > date_format() function. If the type of payment_date is DATE or > DATETIME, you can just compare it to a string literal, like this: > > WHERE payment_date >= '2001-01-12' > > That can use an index. Next, if you just want DISTINCT user_ids, MySQL > may find a better query plan with the DISTINCT keyword instead of GROUP > BY. I'm not sure about this -- maybe someone else could answer it for > sure. > > If you're unfamiliar with reading EXPLAIN output, try > mysql-visual-explain (http://mysqltoolkit.sourceforge.net/). This query > isn't too hard to read, but more complex queries are tough to understand. > > Baron > > krishna chandra prajapati wrote: > > 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 > >>> > >>> > >>> > > >
