MySQL Forums
Forum List  »  Performance

Re: subquery vs. join?
Posted by: daofeng luo
Date: December 05, 2004 07:52PM

I agree. But the problem is: the performance of join is not linear in this query:

select speed_type, count(*) from log, ip_region
where login_ip between start_ip and end_ip
group by speed_type;
+----+-------------+------------------+------+----------------+------+---------+------+-----------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+----------------+------+---------+------+-----------+------------------------------------------------+
| 1 | SIMPLE | ip_region | ALL | NULL | NULL | NULL | NULL | 120142 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | log | ALL | index_login_ip | NULL | NULL | NULL | 4021986877 | Range checked for each record (index map: 0x2) |
+----+-------------+------------------+------+----------------+------+---------+------+-----------+------------------------------------------------+
when the log table is less than 20 million rows, the performance is linear, and takes about 5 minute for 10 million rows. but when the log table become 100 million rows, it takes endless time to finish(I only waited for 15 hours).

It seems that when the log table gets larger, the query processor refuses to use the index_login_ip to conduct the join.

Options: ReplyQuote


Subject
Views
Written By
Posted
20420
December 03, 2004 02:52AM
7127
December 05, 2004 03:03AM
Re: subquery vs. join?
5942
December 05, 2004 07:52PM
6149
January 20, 2005 03:48AM
5294
January 20, 2005 06:52AM
4663
January 21, 2005 11:43PM
4862
January 21, 2005 11:38PM
3880
January 20, 2005 04:41PM


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.