List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 1 1999 10:19pm
Subject:Beginner's Question: Optimized Join
View as plain text  
>>>>> "Pete" == Pete Kazmier <pete-geocrawler@stripped> writes:

Pete> Hello,
Pete> I am VERY new to MySQL, SQL, and database design.  Nevertheless, after
Pete> seeing the new Oreilly book, I decided to learn more on MySQL and
Pete> databases in general.  I thought adding some sort of database backend
Pete> to something on my web page would be a good learning experience.

Pete> So I created the following tables:

Pete> CREATE TABLE User (
Pete>        UserId INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
Pete>        UserName VARCHAR(8) NOT NULL,
Pete>        UserPassword VARCHAR(8) NOT NULL,
Pete>        UserFirstName VARCHAR(20),
Pete>        UserLastName VARCHAR(50)
Pete>        );

Pete> CREATE TABLE Pager (
Pete>        PagerId INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
Pete>        PagerName VARCHAR(50) NOT NULL,
Pete>        PagerPin VARCHAR(50) NOT NULL,
Pete>        PagerStatus ENUM("Enabled","Disabled") NOT NULL,
Pete>        PagerTypeId INT UNSIGNED NOT NULL,
Pete>        UserId INT UNSIGNED NOT NULL,
Pete>        TimePeriodId INT UNSIGNED,
Pete>        INDEX (UserID)
Pete>        );

Pete> After reading the MySQL book, the MySQL documenation (specifically
Pete> chapter 10 and chapter 7's description of EXPLAIN), and browsing the
Pete> mailing lists, I am simply confused regarding table joins and
Pete> optimization.  All I want to do is something like this:

Pete> SELECT * FROM User,Pager 
Pete>        WHERE User.UserID=Pager.UserID AND User.UserID=4;

Pete> So that works fine, but after reading about optimizing tables with the
Pete> EXPLAIN statement, I gave it a whirl and got this:

Pete> table  type   possible_keys  key      key_len  ref   rows  Extra 
Pete> -----------------------------------------------------------------
Pete> User   const  PRIMARY        PRIMARY        4  ???      1        
Pete> Pager  ALL    NULL           NULL        NULL  NULL   204        


Hi!

MySQL will change your query to:

SELECT * FROM User,Pager 
WHERE Pager.UserID=4 AND User.UserID=4;

But this shouldn't change the output from EXPLAIN

Can you please post me the following information:

mysqldump --no-data 'database' User Pager

The problem is that something looks wrong!

Under 'Possible keys' for the Pager table, you should have 'UserID'.

Regards,
Monty
Thread
Beginner's Question: Optimized JoinPete Kazmier1 Sep
  • Beginner's Question: Optimized Joinsinisa1 Sep
  • Beginner's Question: Optimized JoinMichael Widenius2 Sep
    • Re: Beginner's Question: Optimized JoinPete Kazmier2 Sep