CREATE TABLE name SELECT ... really slow
Hello.
While playing with queries I came around a bit weird thing.
First - I'm using mysql 4.1.14.
So, when I do:
SELECT GROUP_CONCAT(id ORDER BY id SEPARATOR ',') ids, hash FROM imagesnew GROUP BY hash HAVING COUNT(hash) > 1;
it works perfectly, i.e. returns results in 30 seconds (and caches it, btw).
But I need it's result stored in another table, so I call:
CREATE TABLE myres SELECT GROUP_CONCAT(id ORDER BY id SEPARATOR ',') ids, hash FROM imagesnew GROUP BY hash HAVING COUNT(hash) > 1;
and here the problem starts:
Query OK, 5849 rows affected (18 min 47.00 sec)
I also tried creating the table, then using:
INSERT INTO myres (ids, hash) SELECT GROUP_CONCAT(id ORDER BY id SEPARATOR ',') ids, hash FROM imagesnew GROUP BY hash HAVING COUNT(hash) > 1;
it takes a lot of time too.
Then, being curious (since maybe indexing the table takes that time?), I tried a little php script, that does CREATE TABLE, SELECT GROUP_CONCAT (...), builds one huge INSERT INTO myres VALUES query and executes it. Well, it took around 2 seconds.
Now I have no idea what else should I do.
I'm compiling mysql 5.0 now, maybe this helps.
Anyway, should I submit a bugreport or whatever?
I hope I'm posting it in the right place...
Thanks for your time.
** Little update:
So, I've got 5.0.16 now, but the problem still exists.
When I issue the CREATE TABLE ... query, cpu usage jumps up to 50% (same with select only, so it's just generating select results I guess), but then it drops to 0% again and MySQL still shows 'Sending data' in show processlist.
Table files (.MYD, .MYI) seem to be empty (well, index file has 1024bytes), after query finishes (lots of time) MYD file is around 300KB (so, that's not really much data, is it). I still have no idea, what do I do wrong.
--
toruvinn
Edited 1 time(s). Last edit at 11/25/2005 01:27PM by Marek Mizera.