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
20411
December 03, 2004 02:52AM
7119
December 05, 2004 03:03AM
Re: subquery vs. join?
5932
December 05, 2004 07:52PM
6143
January 20, 2005 03:48AM
5283
January 20, 2005 06:52AM
4656
January 21, 2005 11:43PM
4857
January 21, 2005 11:38PM
3877
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.