Really slow query with dependent subquery

[Ivo H]

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.