Large collection, need speed

[Funzo]

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

[whisher]

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.

[guelphdad]

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.

[Funzo]

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.

[guelphdad]

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.