SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Performance In PHP Script

    I'm looking at my latest script, which has really taken me from a beginner to an “intermediate” php programmer dude. Of course, this is a scary time because I know just enough to be dangerous. On the site I'm programming, I'm expecting high traffic and high usage.

    I have to keep track of all purchases for long periods of time. I was just looking at my purchase table. It has 100 rows and its file size is 26k. Once the site is launched, I'd expect 1,000 rows to be added daily. So, now that most of the loose ends of my script are finished, I'm considering ways optimize it. The file size doesn't bother me all that much, but it seams to indicate that there may be a “lighter” way of achieving the same things.

    I'm including data within this table that is also available on the other tables. I could easily remove 7 of the 36 columns without much work just using MySQL simply doing two queries instead of one on many of my scripts. I could knock out another 6 if I wanted to create another table.

    My gut seams to think that I should keep each table as small as possible and then do the extra queries or MySQL joins when they are needed (which won't be all the time)?

    Am I on the right track here?

    Brandon
    Home Recording Forum -
    Make 60% Commission Pushing my new mega system
    Killer Home Recording

  2. #2
    SitePoint Zealot Dachande663's Avatar
    Join Date
    Feb 2005
    Location
    Birmingham, UK
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The process you're describing is called normalization and there are masses of resources around the net about the best ways to do it. Ultimately, moving data into a foreign table and then referencing it by ID is better in (most) situations. Also, if you're looking to reduce record sizes, check the length of fields to make sure they're only just big enough, rather than waste space.
    Web Developer & Geek: hybridlogic.co.uk ~ lukelanchester.com

  3. #3
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    What you're getting into is relational database design. There are a ton of great articles and tutorials on designing effective database structures. I googled "relational database design" and got a slew of them. Check out the article below:

    http://www.edm2.com/0612/msql7.html

    The gist of it is that if you have more than one piece of information about an object, it should be in its own table. No field in the database should contain more than one value.

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks dudes. I just didn't know what to search for.

    "normalization" and "relational database design" should get me covered.

    Thanks,
    Brandon
    Home Recording Forum -
    Make 60% Commission Pushing my new mega system
    Killer Home Recording

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please also search for "denormalization". This will help you to strike a balance between full normalisation and denormalisation. This could be useful for improving performance at times.


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
  •