MySQL Forums
Forum List  »  Optimizer & Parser

Really slow query with dependent subquery
Posted by: Ivo H
Date: April 07, 2010 04:12AM

Hello guys,
I could really appreciate some help here.

Let's assume that i have a table "visits" which contains "id", "user_id", "contact_id" and "date". "id" field is auto_increment, and "contact_id" is the id of the contact being visited.

What I want is to see the id AND the "serial number" of every visit. What I mean is that if i have visited contact with id 1 - 3 times, and contact with id 2 - 2 times I what the following result:
1, 1 (contact_id 1, first visit)
1, 2 (contact_id 1, second visit)
1, 3 (contact_id 1, third visit)
2, 1 (contact_id 2, first visit)
2, 2 (and so on..)

To accomplish this I have the following query, which determines the "serial number" by counting the number of visits till now by the date field.

SELECT v.id, (SELECT COUNT(*) FROM visits WHERE user_id = v.user_id AND contact_id = v.contact_id AND date <= v.date) as contact_visit_num from visits v;

This works great if the number of visits is not much but so far I have 5128 visits and the explain query shows:
+----+-------------+-------+--------+---------------+---------+---------+----------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------+----------+-------------+
| 1 | PRIMARY | v | range | PRIMARY | PRIMARY | 3 | NULL | 5128 | |
| 2 | DEPENDENT SUBQUERY | visits | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 5128 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+----------------+----------+-------------+

Which is ridiculous. I can't even wait to see the results. Is there a way to optimize the dependent subquery and minimize the rows within it or I don't know.. anything?

THanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Really slow query with dependent subquery
3650
April 07, 2010 04:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.