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.