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
20450
December 03, 2004 02:52AM
7142
December 05, 2004 03:03AM
Re: subquery vs. join?
5957
December 05, 2004 07:52PM
6166
January 20, 2005 03:48AM
5310
January 20, 2005 06:52AM
4677
January 21, 2005 11:43PM
4872
January 21, 2005 11:38PM
3891
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.