MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:<sinisa Date:September 1 1999 1:35pm
Subject:Beginner's Question: Optimized Join
View as plain text  
Pete Kazmier writes:
 > Hello,
 > 
 > I am VERY new to MySQL, SQL, and database design.  Nevertheless, after
 > seeing the new Oreilly book, I decided to learn more on MySQL and
 > databases in general.  I thought adding some sort of database backend
 > to something on my web page would be a good learning experience.
 > 
 > So I created the following tables:
 > 
 > CREATE TABLE User (
 >        UserId INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 >        UserName VARCHAR(8) NOT NULL,
 >        UserPassword VARCHAR(8) NOT NULL,
 >        UserFirstName VARCHAR(20),
 >        UserLastName VARCHAR(50)
 >        );
 > 
 > CREATE TABLE Pager (
 >        PagerId INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 >        PagerName VARCHAR(50) NOT NULL,
 >        PagerPin VARCHAR(50) NOT NULL,
 >        PagerStatus ENUM("Enabled","Disabled") NOT NULL,
 >        PagerTypeId INT UNSIGNED NOT NULL,
 >        UserId INT UNSIGNED NOT NULL,
 >        TimePeriodId INT UNSIGNED,
 >        INDEX (UserID)
 >        );
 > 
 > After reading the MySQL book, the MySQL documenation (specifically
 > chapter 10 and chapter 7's description of EXPLAIN), and browsing the
 > mailing lists, I am simply confused regarding table joins and
 > optimization.  All I want to do is something like this:
 > 
 > SELECT * FROM User,Pager 
 >        WHERE User.UserID=Pager.UserID AND User.UserID=4;
 > 
 > So that works fine, but after reading about optimizing tables with the
 > EXPLAIN statement, I gave it a whirl and got this:
 > 
 > table  type   possible_keys  key      key_len  ref   rows  Extra 
 > -----------------------------------------------------------------
 > User   const  PRIMARY        PRIMARY        4  ???      1        
 > Pager  ALL    NULL           NULL        NULL  NULL   204        
 > 
 > The documentation said that "ALL" is bad so I tried changing the WHERE
 > clause a bit:
 > 
 > SELECT * FROM User,Pager
 >        WHERE User.UserID=Pager.UserID AND Pager.UserID=4;
 > 
 > Same results.
 > 
 > SELECT * FROM User,Pager
 >        WHERE User.UserID=4 AND Pager.UserID=4;
 > 
 > Same results.
 > 
 > I then came across something that said if I don't have a lot of data
 > in the table then MySQL might choose "ALL", so I populated the tables
 > with some data.
 > 
 > The only explanation that I could come up with is that perhaps the
 > "PRIMARY" index on the User table is not defined the same as the
 > "UserID" index on the Pager table.  That is to say, the index on the
 > Pager table does NOT have to be unique because a User can one or more
 > Pagers, but the index on the User table is unique because it is a
 > primary key.
 > 
 > What would be the proper design of these tables and what would be the
 > proper SELECT statement?
 > 
 > Thanks for the help,
 > Pete
 > 
 > -- 
 > Peter Kazmier                                 http://www.kazmier.com
 > PGP Fingerprint   4FE7 8DA3 D0B5 9CAA 69DC  7243 1855 BC2E 4B43 5654
 > 


Hi!

Mysql query optimizer may conclude that it is faster to scan entire
table then to go through keys.

This happens when number of rows is extremely low, which is your case
!

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+
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