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

Hi Alexander,

>>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.
> ...

Yes, I see this clearly.
What I wanted (means: failed :) to express was that I associated the "0
= 1" I saw with the "WHERE 0 = 1" which OOo regularily uses to determine
the structure of a result set. And since "WHERE 0 = 1" can be (and
probably is, in MySQL?) implemented without actually retrieving any data
- since it's obvious that the result set will always be empty -, I
assumed the same for the statement I saw there. This lead me to thinking
it's a MySQL problem.

Speaking strictly, it would probably be possible to optimize a "HAVING 0
= 1", too, since there the same arguing leads to an empty result set
immediately. But for HAVING this does not really sound as ... natural to
me as for WHERE.

> So if your query was:

(I have to mention here that the original problem, together with all the
sample data/queries, was reported by some other user of OOo :)

> SELECT `artists`.`name`, `albums`.`name`
> FROM `albums`, `tracks`, `artists`
> WHERE albums.id = tracks.al_id
>    AND tracks.ar_id = artists.id;
> ...
> Adding GROUP BY clause to this query can possibly make some sense, but not exactly 
> this GROUP BY clause with this SELECT part.

Yep.

> 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.

Hmm. You mean I have migrate to Linux? :) Okay, that's becoming
off-topic ... :)


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

That's the time neither me nor the original reporter waited - as said,
on Windows it's terribly slowing down the whole machine, the MySQL
server process takes 99-100% of the CPU, and even terminating it takes
half a minute because everything (including consoles, which are normally
the last thing working) is extremly unresponsive.

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

Sorry. IZ = IssueZilla, the bug tracking system of OpenOffice.org.
What I wanted to say is that I will (and meanwhile did) state what you
said in the respective bug in IssueZilla, to not leave the "this is a
MySQL bug" there :).


Thanks & Ciao
Frank




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