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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
