Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

An index on a constantly changing table?

endoflegendofleg EntrantCurrent User Role Beginner
Hi,

Developing a web game and looking to optimise the MySQL back end, fairly new to databases and I KNOW there are things I could be doing to optimize it.

The game pretty much constantly hits the database, the queries are great, filtering out what isn't needed and only returning the exact information required.

Some of the tables are hit for information pretty much constantly (hundred queries a second) and are contantly updated. (game uses ajax and fires 5 queries, per player, every 3 seconds, not including manual events)

My main question is:

There are a couple of these rapidly updated tables which don't have indexes, is it worth while adding an index to these to increase performance? There isn't currently a unique field which could be indexed on, so I would be purely creating a field for index purposes

And my sub-question is:

Are there any other optimisation tips you have for a database being used for these purposes?


Thanks )


edit:

To add to that, and give an idea of hwo the table is laid out. (more info than this, but as this table stores historical data I wanted to explain why an index doesn't currently exist) gameclosed is changed to 1 when game has ended so there can be duplicate userid and gameid.. we may possibly need to rething the way it manages the game list, but I was hoping that we could get some joy out of sticking an index in there


GameID, UserID, GameInfo1, GameInfo2, GameClosed1, 100, x, y, 01, 130, a, b, 02, 175, g, h, 02, 122, r, s, 0</pre>


Our queries would be along the lines of:


WHERE GameID=999 AND UserID=100 AND GameClosed=0</pre>

Comments

  • teajay2teajay2 Entrant Current User Role Beginner
    Could the primary key actually be a combination of the three columns:

    GameID
    UserID
    GameClosed

    In that order.
  • endoflegendofleg Entrant Current User Role Beginner
    heh, was about to post to say i'd found out that you can create a key on more than one field. So i've created it on GameID and UserID and the command went through, wether it improves the performance.... only time will tell )

    Any other performance tips for a database of this kind? Like as a couple of the tables are getting rows deleted, added and ammended very regularly, is it efficient to otimize the tables on a regular basis (like, every few minutes) or should I just have it run daily?
  • teajay2teajay2 Entrant Current User Role Beginner
    You definitely want to make use of the ability to create indexes on more then one column.

    Look at the queries that you use often, and try to plan your where clauses and indexes accordingly.

    Based on this one query, it appears that the index I suggest may help. Let me know what you find.
  • endoflegendofleg Entrant Current User Role Beginner
    Thanks for the advice. But (forgive my noobishness) does the table need optimising very regularly to keep this effective? That table is getting shuffled around thousands of times per minute, will the indexes stay effective if it's only optimized daily? hourly? every 15 minutes?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.