A table with too many indices - "almost live" copy of table the solution?

Hi experts )

I’ve made a web-based football (that’s soccer) game that includes a lot of clubs, managers, employees and of course football players.

The managers have to able to search these players. They can search these players on a lot attributes, for example their ability in finishing, dribling, jumping and so on.

A typical search could look like this:
Find players where age is between 20 and 23 and finishing is between 15 and 30. They players should not be on auction and should have plans on retiring. Order the search by the players value.

Needless to say this results in too many indices in order to speed up the queries. Still, some queries are still very slow because it is impossible to cover all possible search combinations by indices.

This slows down the table that besides the searches are heavily used by the rest of the application.

I would imagine community- and dating sites have the same problems when giving their users the ability to search their user base through a lot of attributes. What do “they” do?

I’d imagine that a “almost live” copy of the table would solve the problem. The searches will not be faster, but rest of the site would not suffer any longer. Is this a good solution or do you have a better idea? )

I know about “traditional mysql replication”, but as far as I know this requires two instances of the mysql-server. For me, it would be a lot easier if I could just have some kinda “mirror” of the players-table within the same database.

However, if this is not possible I might just start another instance of the database on the same server… the performance problem must be solved, one way or the other.

Alternative solutions are very welcome.

  • Rasmus
[B]razdaman wrote on Mon, 20 August 2007 06:05[/B]

A typical search could look like this:
Find players where age is between 20 and 23 and fishing is between 15 and 30.

Fishing?! I thought these guys were football players )

[B]razdaman wrote on Mon, 20 August 2007 06:05[/B]

This slows down the table that besides the searches are heavily used by the rest of the application.

If youre using MyIsam tables, maybe the biggest performance gain you could make would be to switch to innodb, which vastly improves concurrency when tables are in use.

[B]Quote:[/B]

However, if this is not possible I might just start another instance of the database on the same server… the performance problem must be solved, one way or the other.

I dont think this is a good idea. The queries are either slow because of MyIsam locking (convert to innodb), or because the queries are using a lot of cpu/disk (because of lack of indices, poor execution plan etc).
Running a second process will only compound that problem since the servers will compete for ram and it could increase disk seeks