SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru CompiledMonkey's Avatar
    Join Date
    Sep 2002
    Location
    Richmond, VA
    Posts
    975
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Indexing, keys, etc...

    Does anyone have a link or personal explaination of some of the key topics to database design? I've made a few databases myself. All of which have been pretty simple in design. I've used the linking (I think it would be called primary keys too) idea by linking two tables by a column. That's about it though. Am I missing something that could make my databases faster, better designed, or something? Thanks in advance.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's one article, full of excellent explanations
    http://searchdatabase.techtarget.com...285649,00.html

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words, jofa

    remind me to pay you later ;-)

    rudy

  4. #4
    SitePoint Enthusiast Powerlord's Avatar
    Join Date
    May 2003
    Location
    Mason, MI, USA
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by CompiledMonkey
    Does anyone have a link or personal explaination of some of the key topics to database design? I've made a few databases myself. All of which have been pretty simple in design. I've used the linking (I think it would be called primary keys too) idea by linking two tables by a column. That's about it though. Am I missing something that could make my databases faster, better designed, or something? Thanks in advance.
    If you're using the InnoDB table type, you can help preserve referential integrity by using Foreign Keys. Unfortunately, the other table types in MySQL don't support this.

    You should create an index on any column that you're going to be searching a lot using where clauses.

    Hrm... I'm sure there are more tips to offer than this, though.

    Oh yes, something I just thought of:

    If the column you're using in the join has the same name in both tables, you should try changing your syntax from this:

    SELECT Field1, Field2, Field3
    FROM Table1, Table2
    WHERE Table1.Field1 = Table2.Field1

    to this:

    SELECT Field1, Field2, Field3
    FROM Table1 INNER JOIN Table2
    USING (Field1)

    or, if they have different names

    SELECT Field1, Field2, Field3
    FROM Table1 INNER JOIN Table2
    ON (Table1.Field1 = Table2.OtherField)

    This may not have any performance increases, but it makes it more clear exactly how the tables are being joined together by removing them from the WHERE clauses.

    Tip: USING doesn't always work when you start mixing INNER and OUTER JOINs together. However, ON seems to work in all cases.
    Last edited by Powerlord; May 23, 2003 at 05:17.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •