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

Frank,

Frank Schönheit wrote:
> Hi,
> 
> During using OpenOffice.org with MySQL, it seems a user discovered a bug
> in MySQL.
> The details are described in
> http://www.openoffice.org/issues/show_bug.cgi?id=18039.
> 
> In short: If a sample SELECT statement (attached to the issue above) is
> executed against MySQL, with the sample data also attached to the above
> issue, then the CPU usage of the mysdqld-nt process jumps to 100%, and
> the query doesn't return.

We will investigate this case, but Query in question is Cartesian Product between 
3 tables!

mysql> select count(*) from albums;
+----------+
| count(*) |
+----------+
|      305 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from tracks;
+----------+
| count(*) |
+----------+
|     3638 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from artists;
+----------+
| count(*) |
+----------+
|      231 |
+----------+
1 row in set (0.00 sec)


305*3638*231 = 256315290

Query which will provide 256 million rows and which can store them in some 
temporary space before sending to client will be always slow.

I don't agree with you about
"closing as INVALID, because it's an MySQL bug."

Interrim result of this Cartesian product consists of 2 columns CHAR(100) + 1 Byte 
per column * 256315290 rows = 51519373290 bytes which is almost 48GB of data. This 
number is huge by any means. You should not expect your machine to handle easily 
this amount of data.


I see nothing strange. You are just using MySQL in a way it is not intended to be 
used. Cartesian Product is almost never meaningfull.

You can knock down any server with sending huge amount of data request.


This is very good example of careless SQL usage. No matter if this query is 
written by hand or generated by some programs. Processing 3 very small tables 
less than 2 Mb in size and very few rows can demand huge amount of resources.

> The original bug problem was discovered while using OpenOffice.org to
> access the MySQL database, but it persists if the MySQL console is used
> to execute the statement, so this is clearly a MySQL problem.
> 
> Any hints anybody?

Do you agree with me this is not a bug really?

> Thanks & Ciao
> Frank

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]