server version: 3.23.47
mysql> create table myhits (
-> libid int unsigned not null,
-> begin bigint unsigned not null,
-> end bigint unsigned not null,
-> index(libid),
-> index(begin),
-> index(end)
-> ) type = heap;
Query OK, 0 rows affected (0.00 sec)
[ insert statement to fill table "myhits" ]
Query OK, 65681 rows affected (1.70 sec)
Records: 65681 Duplicates: 0 Warnings: 0
mysql> explain select h1.libid as libid,
-> max(h1.end + 1) as begin,
-> min(h2.begin - 1) as end
-> from myhits as h1 inner join myhits as h2 on (h1.libid = h2.libid and h2.begin
> h1.end)
-> group by h1.libid, h1.begin
-> ;
+-------+------+---------------+-------+---------+----------+-------+-----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+-------+---------+----------+-------+-----------------+
| h1 | ALL | libid | NULL | NULL | NULL | 65681 | Using temporary |
| h2 | ref | libid,begin | libid | 4 | h1.libid | 18 | where used |
+-------+------+---------------+-------+---------+----------+-------+-----------------+
mysql> create temporary table ranges type = heap
-> select h1.libid as libid,
-> max(h1.end + 1) as begin,
-> min(h2.begin - 1) as end
-> from myhits as h1 inner join myhits as h2 on (h1.libid = h2.libid and h2.begin
> h1.end)
-> group by h1.libid, h1.begin
-> ;
[wait wait wait wait]
When I actually execute the query above, it seems to hang (for at
least three hours, until I killed it); show processlist says "Copying to
tmp table" for the whole time. I've set max_tmp_table to be 1500M
(there's 2Gb of memory in the machine), so I know (?) it's doing all of
the operations in memory, and it's definitely not swapping out. So why is
this taking so long? Is there some hidden O(N^4) operation going on? ;)
Thanks for any help,
-Aaron
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
