>>>>> "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
