List:General Discussion« Previous MessageNext Message »
From:Bob Loeffler Date:November 28 2003 9:44am
Subject:RE: off into the weeds
View as plain text  
Hi Dean,

If I'm not mistaken, it looks like you are just joining the two tables
(list_rank and entry) and not specifying which matching records you want
(with a "where..." clause), so mysql is matching each row in one table with
each row in the other table.  That will be a HUGE result set, so it will
take a lot of time.

Bob


-----Original Message-----
From: Dean A. Hoover [mailto:dhoover@stripped]
Sent: Friday, November 28, 2003 1:38 AM
To: mysql@stripped
Subject: off into the weeds


I am running mysql  Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)
on redhat 9. I have defined a simple database as follows:

DROP DATABASE IF EXISTS xhistory;
CREATE DATABASE xhistory;
USE xhistory;

CREATE TABLE category
(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   parent_id INT,
   INDEX (parent_id),
   FOREIGN KEY (parent_id) REFERENCES category(id) ON DELETE CASCADE,

   title VARCHAR(60) NOT NULL,
   INDEX (title)
) TYPE=InnoDB;

CREATE TABLE entry
(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   product_code VARCHAR(30) UNIQUE NOT NULL,
   INDEX (product_code),

   title VARCHAR(100) NOT NULL,
   INDEX (title),

   description TEXT NOT NULL,

   percent FLOAT NOT NULL,
   INDEX (percent)
) TYPE=InnoDB;

CREATE TABLE list_rank
(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   category_id INT NOT NULL,
   INDEX (category_id),
   FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE CASCADE,

   entry_id INT NOT NULL,
   INDEX (entry_id),
   FOREIGN KEY (entry_id) REFERENCES entry(id) ON DELETE CASCADE,

   current ENUM ("T", "F") NOT NULL,
   INDEX (current),

   rank INT,
   INDEX (rank),

   changed DATETIME NOT NULL
) TYPE=InnoDB;

====
I have populated the database, which has 6319 records in the list_rank table
and 2472 records in the entry table. Everything looks fine, and I can easily
do the simple queries such as:
mysql> select * from list_rank;

However, when I run the following simple join, mysqld seems like its playing
chess against a chess master. It went on for minutes until I aborted it. It
seems like it should have returned almost immediately. Can anyone point out
the error of my ways?

mysql> select list_rank.category_id,entry.id,entry.title from
list_rank,entry
order by entry.title;

Thanks.
Dean Hoover


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
off into the weedsDean A. Hoover28 Nov
  • RE: off into the weedsBob Loeffler28 Nov
  • Re: off into the weedsMartijn Tonies28 Nov