mysql performance

[bluegman991]

i was just wondering how many fields in a table are considered to much and would slow down queries that associate with that table.

if it depends on how many rows are in the table also say about 10k rows

[Zeggy]

If you think you've got too many fields, you're probably not designing your database correctly.

The number of rows in a table can go into tens of millions - that's what mysql is made for.

BUT if you don't have correct indexes on your tables, it can become really slow.

EDIT:

Sorry, the emphasis of my post is - use indexes correctly and your database will do just fine.

If your queries are using indexes, even if your table has a billion rows, it won't have to look through them all to select the rows you want.

Here's a quick guide:

Any time you have a select where [...], add indexes on the columns that you are selecting by.

Same for sorting/ordering.

And if you are selecting/sorting by more than one column, you can add indexes on the columns individually, or you could a composite index of the two columns together, so an index on (col1, col2).

If you have a query "select `id` from `players` where `level`>1 and `level`<10", then add an index to the level column.

If you have a query "select `id` from `players` where `level`>1 and `money`>1000", then you can add an index to level and money, or you could add an index to (level, money). The order of the columns in the index is also important - it should follow the order that you used them in the query.

 

The problem with this is that if you have loads of columns in your table, your table may end up with loads of indexes on almost every column. Every time you write to the database, the database needs to update the indexes (and indexes also take space).

So indexes will speed up your reads from the database, but slow down your writes. Since web games are pretty write-intensive, you need to figure out how many indexes you need/want - OR write your queries better.

[a_bertrand]

Well all depends on the operations you do on the tables. A single row query (which means you do a select for example on the primary key) will be fast even with million rows. Now if you need to do grouping, it will be slow. So just like that it will be very hard to answer.

[DigitalGerm]

Msql performance drops down when you start to delete rows and not doing the optimize query after that.

Also drops when you start using multiple db connectors ( open -> query -> close or reuse )

If you know the number of results ex. (10 rows, add the Limit 0,10 or, if you query for a login LIMIT 0,1 ) this way if have 10000 users, no point to query all.

... probably theres more. I don't think the number of rows influence much. This board has over 50k rows :)) and around 350 users connected