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:[EMAIL PROTECTED] Sent: Friday, November 28, 2003 1:38 AM To: [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
