SQL Issues

I’ve been doing a lot of SQL work on the Schedule Maker these past couple of days, when I realized something. The SQL query for a relatively common command is going to be absolutely huge! Observe:

-- Get all my friend's current schedules: 
SELECT * FROM   friends, semesters, schedules, schedule_sections,   
sections, section_times WHERE   friends.user_id='jeresig' AND   
semesters.semester_current = '1' AND   
schedules.semester_id = semesters.semester_id AND   
schedules.schedule_default = '1' AND   
schedules.user_id=friends.friends.friend_id AND
chedule_sections.schedule_id=schedules.schedule_id AND 
sections.section_id=schedule_sections.section_id AND  
section_times.section_id=sections.section_id;

That’s six joins. Insane. Especially since I was planning on having the results of this query be the main page whenever you log in to your account. So, this leads me to the next issue: How can this be resolved? It’s not completely apparent, as of yet, but these are a few of the modifications that I’ve made so far:

  • I’ve switched from PostgreSQL back to MySQL. MySQL is faster and more supported. I was using PostgreSQL originally for its nice advanced features (stored procedures, schemas, etc.) when I found that I was only using schemas in the end. That’s hardly a cause to stay with the database, especially considering that I can get better performance elsewhere. So, I’m still digging around as to how to work around this, I’ll have to see.
  • Searches were being performmed in memory, this has been moved to MySQL Full Text searches. In memory searches are fast (obviously) however, costly. I was storing about 40mb of data in memory per school. These adds up after a while and doesn’t work to well when I want to add more data or restart the server. First off, I had no idea that full text searches existed and man are they nice. Boolean queries, query expansion – the works! It’s search engine for dummies time! I’m definitely going to have to play around with this some more.
  • Finally, I began to realize that so many of my data was going to be virtually static (schedules, course data, etc. all change very infrequently). This is starting to push me in the direction of having a static XML copy of all data in a nicely organized directory structure. Observe:
    /users/ /users/jeresig/
    /users/jeresig/friends/
    /users/jeresig/friends/fred -> /users/fred
    /users/jeresig/friends/ted -> /users/ted
    /users/jeresig/schedules/
    /users/jeresig/schedules/20043/mysched.xml
    /users/jeresig/schedules/20043/default.xml ->
      /users/jeresig/schedules/20043/mysched.xml
    /users/jeresig/schedules/current.xml ->
      /users/jeresig/schedules/20043/default.xml

    Now observe this simple shell query:

    cat /users/jeresig/friends/*/schedules/current.xml

    Using only that command I can effectively acheive the same result of the massive SQL query above. I’m definitely going to explore this more as I’m still not sure if this is a viable way of going about this, but I am intrigued by the simplicity in it. (Also, since all the files are static, the caching implications are going to be quite interesting.)

I hope to report back soon with some of my final findings. I hope to talk with some of my database guru friends and see what we can cook up as being an ideal solution.

Posted: March 24th, 2005


Subscribe for email updates

6 Comments (Show Comments)



Comments are closed.
Comments are automatically turned off two weeks after the original post. If you have a question concerning the content of this post, please feel free to contact me.


Secrets of the JavaScript Ninja

Secrets of the JS Ninja

Secret techniques of top JavaScript programmers. Published by Manning.

John Resig Twitter Updates

@jeresig

Infrequent, short, updates and links.