You are invited to Log in or Register a free Frihost Account!

Efficient search transfer list in Game

I have started to make an online athletics game (track and field). I haven't got very far yet.

I want a transfer list where you can search athletes to buy. The thing is that the users should be able to set some search parameters to easily find the athlete they want. For example if we want to list athletes that have more than 5 in speed and more than 3 in stamina but costs less than 25000 the query will probably look something like this:
SELECT * FROM athletes WHERE on_transfer_list=1 AND speed>5 AND stamina>3 AND cost<25000

What I'm worrying about is that if there is a huge number of athletes on the transfer list it might get slow to do this kind of queries. I have thought about using indexes but it looks almost impossible. There will off course be a lot more parameters than just speed and stamina. If I add an index on speed and one index on stamina it will not be possible to use indexes all the way to pick the rows. if I set an index on both speed and stamina it will not help if I search for another parameter. I can't add indexes for all possible combinations. It will be TOO slow during inserts. So my question are (if possible) how I can make this kind of queries efficient.

Note that I have not yet decided if it's best to create a new table for the athletes on the transfer list or to use the on_transfer_list column. adding a new table can probably remove the index or/and search load but will can result in redundant data.
how many queries per second do you expect and how many rows will the table have? Also would it be possible to categorize the athletes and put them in different tables?

If you have less than tens of thousands of rows and/or less than 1 query a second there is nothing to worry about.
The athletes will not be categorized. They will have different skills that makes them different good in different events but they will still be able to compete in all events.

The number of queries per second and rows in the table is hard to know because it will depend much upon how popular the game will be. I aim quite high this time and try to make it to work good even with much traffic but if I should be realistic I think that ten of thousands of rows and 1 query a second will be really hard do get.

I have realised that I don't know exactly how MySQL can make use of indexes when using other than equals (=). Should I bother with indexes here or should I skip it. Something within me tells me to always use indexes to avoid going through the whole table but will one index containing all searchable columns work and will it be worth it?
Well, even if the game is really popular, are people going to be spending all their time by athletes? Because you might have 5 million players, but an athlete might only be bought every 2 seconds.

-Nick Smile Smile Smile
Related topics
Who knows Knight online passwords
C++ (how to make a game)
Isn't this cool?
[OFFICIAL] Naruto - No more new threads about this
Which search engine do you prefer?
Mod Projects lots of info
Watched Topics Page
SEO Software
Search engine submiter
search engines list
Free Frihost Search Engines (Google toolbar**NEW** + FF)
Funny Google Tricks
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

© 2005-2011 Frihost, forums powered by phpBB.