Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hi Frank,

Frank Schönheit wrote:
> Hello alexander,
<cut>

>>Query which will provide 256 million rows and which can store them in some 
>>temporary space before sending to client will be always slow.
> 
> 
> Ah, yes ... looking at the (wrongly generated) statement, again:
> 
>   SELECT `artists`.`name`, `albums`.`name`
>   FROM `albums`, `tracks`, `artists`
>   GROUP BY `tracks`.`al_id`
>   HAVING 0 = 1
> 
> ... Being *that* used to seeing "WHERE 0 = 1" in OOo-generated
> statements, I didn't realize that we're talking about a HAVING here,
> which of course is a different thing.

I want to be sure you understand what I am pointing to you. Having is not a 
problem, nor is GROUP BY. What makes query "wrong by idea" is Cartesian Product. 
It joins 3 tables without Join condition.

SELECT ... FROM `albums`, `tracks`;

This translates as "Combine each row in albums with each row in tracks".

Such request can hardly makes sense.

SELECT ... FROM `albums`, `tracks` WHERE albums.id = tracks.al_id;

on the other hand reads
"Combine rows from albums with their corresponding rows in tracks (and return 
these rows only for which there is such relation)"

So if your query was:

SELECT `artists`.`name`, `albums`.`name`
FROM `albums`, `tracks`, `artists`
WHERE albums.id = tracks.al_id
   AND tracks.ar_id = artists.id;

...

It returns

3638 rows in set (0.10 sec)

All these rows makes pefrect sense for both me and you.

Adding GROUP BY clause to this query can possibly make some sense, but not exactly 
this GROUP BY clause with this SELECT part.

Your query actually utilises MySQL ANSI exiention (some can claim it is violation)
ANS SQL does not allow usage in GROUP BY of columns or aliases which don't appear 
in SELECT part.

However this is beyond topic of current discussion.

>>I don't agree with you about
>>"closing as INVALID, because it's an MySQL bug."
> 
> 
> You're completely right, sorry for sueing MySQL! This, indeed, is an
> amount of data which the next 4 generations of my PC won't be able to
> handling easily :).


Some more info.

MySQL 4.0.14 on Linux "survived" your original query with your data:

mysql> SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`, `artists` 
GROUP BY `tracks`.`al_id` HAVING 0 = 1;
Empty set (11 min 46.55 sec)


Yes it is very slow, but final result is expected given that  HAVING 0 = 1 is 
always false. MySQL did not hang, nor machine did. CPU usage remain at usual level 
- no noticeable slowdown caused by MySQL.

I don't have these ~48GB memory. The reason that MySQL was able to process 
original query is that it sucessfully optimizes GROUP BY clause and make usage of 
Indexes in tables.

mysql> explain SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`, 
`artists` GROUP BY `tracks`.`al_id` HAVING 0 = 1;
+---------+-------+---------------+------------------+---------+------+------+----------------------------------------------+| 
table   | type  | possible_keys | key              | key_len | ref  | rows | Extra 
 
|+---------+-------+---------------+------------------+---------+------+------+----------------------------------------------+| 
albums  | index | NULL          | albums_webindex  |     100 | NULL |  305 | Using 
index; Using temporary; Using filesort || tracks  | ALL   | NULL          | NULL 
            |    NULL | NULL | 3638 | 
  || artists | index | NULL          | artists_webindex |     100 | NULL |  231 | 
Using index 
|+---------+-------+---------------+------------------+---------+------+------+----------------------------------------------+



Trying to run:
SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`, `artists`;

simply leads to error 28:  No space left on device


Running original query without using indexes looks it will take forever to finish.
When I'm writing it it works for 1.5 hours.


> I'll state this in the IZ-issue (citing you there :), and reopen it.

I have no idea what IZ is.


>>This is very good example of careless SQL usage. No matter if this query is 
>>written by hand or generated by some programs.
> 
> 
> Generated by OOo in this case, unfortunately :(. I already submitted an
> issue for this improper generation, before I asked here, but as said:
> reading the HAVING as WHERE didn't let me realized that it's also the
> real problem. Sorry!

No need to apologize.
Whenever you think you have hit MySQL bug don't hesitate to report it. Preferred 
way is to fill report in http://bugs.mysql.com.

You don't need to be advised what repeatable test case means. Your


> Thanks & Ciao
> Frank

You are welcome

Best regards

-- 
  Are you MySQL certified? -> http://www.mysql.com/certification
  For technical support contracts, visit https://order.mysql.com/?ref=msal
     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski <[email protected]>
   / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
  /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
          <___/  www.mysql.com




-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]