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
