SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2006
    Location
    High Wycombe UK.
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Large collection, need speed

    Hi,

    So i basically have a link table connecting the user to an event, the structure is basically this:

    pointstable
    userID
    eventID
    mark1
    mark2


    This table is massive with each user having about 24 entries and there are alot of users. I was just wondering if there is a way that i could optimise the select query or if MySQL does the optimisation for me.

    Query example:

    SELECT * FROM pointstable where userID='xyz123';

    Thanks for the help,
    Nick
    Never stop kicking me.

  2. #2
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    I think the first thing you should do is apply an
    index to userID and eventId if you don't have just it
    as pk.

    Bye.

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Funzo View Post
    This table is massive with each user having about 24 entries and there are alot of users.
    Massive as in the hundreds of millions of rows?

    Unless you are in the millions of rows your table isn't considered large and with properly indexed tables, should be speaking of fractions of a second for a query.

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2006
    Location
    High Wycombe UK.
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the users need to be able to grow exponentially, there are around 6000 users at the moment.

    Are there any constraints to indexed tables? the users ID is something like CBA1, it would make sense to alphabetize as its based on the users initals. Ill go do some research.
    Never stop kicking me.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right so 6000 users with 24 rows, you are talking less than 150000 rows. I'm telling you above that in the millions of rows your queries would still be lightning fast.

    You don't need to alphabetize the users ID field, that is what an index will do. You add the rows in whatever order and the index speeds up the search. Without an index every row would have to be compared in your search. With an index it would quickly jump to "C" then "CB" then "CBA" etc. if you get my example.


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
  •