Determining the best table structure for MySQL performance

Question

I am working on a browser-based RPG for one of my sites, and now I am trying to determine the best way to organize SQL tables for better performance and maintenance.

Here is my question:

Does the number of columns in an SQL table affect the speed at which it can be queried?

I'm not a newbie when it comes to PHP or MySQL. I used to develop things with the common goal of getting them to work, but I recently moved on to a scene where a functional program is not good enough if it is not fast and reliable.

In any case, now I have a members table that contains about 15 columns. It contains information such as player username, password, email address, logins, page views, etc. However, it does not contain any information about the player’s progress in the game. If I added columns for things like army size, gold, turns, and more, then it could easily rise to 40 or 50 full columns.

Oh, and my database structure is standardized.

Would a table with 50 columns that are constantly being queried be a bad idea? Should I split it into two tables; one for general user information and one for user game statistics?

I know that I can check the query time myself, but I have not created the tables yet, and I think I would be better off with some professional advice on this important decision for my game.

Thank you for your time!:)

Answer 1

The number of columns can be of measurable value if you rely on table scans or on caching page data tables. But the best way to get good performance is to create indexes to help your queries. If you have indexes that benefit your queries, the row width in the table is largely inconsequential. You scan specific rows using a much faster tool than scanning through a table.

Here are some resources for you:

• EXPLAIN Demystified
• Read more Mastering the art of indexing

Based on your caution at the end of your question, you already know that you should measure performance and only fix code that has problems. Do not try to do early optimization.

Unfortunately, there are no rules for defining indexes. The best set of indexes should be designed specifically for queries that you need faster. This is hard work, requiring a lot of analysis, testing and comparative performance measurements. It also requires a lot of reading to understand how your RDBMS technology uses indexes.