MySQL lottery processing best practices

Question

I have a situation with a lottery system with a very high level of processing transactions at a time, let me put you in context:

1. There are 10 different s draws.

2. Each draw has many games and each game many numbers.

3. Each number has a limit, if the number have reached the limit , I can not accept more bets in this number.

4. Aprox. the total numbers bet in a single day is 68,000, that is 68,000 rows that I have on a view.

5. I have this relation on MySQL View, example : result from a join of 2 tables that has the transactions:

   Draw   Game  Number   accumulated   limit   
     01    16    10      70,000         75,000
     02    17    0102    65,000         60,000
     03    18    123     20,000         25,000
     ...    ..    ...     ....          ......
     ...    ..    ...     ....          ......
   

   Total rows = 68,000

   My problem is that I have to check for every incoming new number the accumulated from this view in order to avoid exceed the limit.

   I have done many approaches but still the system is very slow:

   1. I make a select from a view, but the views in MySQl are very slow, not good practice.
   2. I make a 'select insert into tmptable from the view ' in order to make the query faster, but still slow.
   3. If I make query directly from the 2 tables and avoid the view it will lock the tables and cause problems.

   The server is very powerful, but I need a new approach to improve this process. Thanks in advance.

Answer 1

Consider using redis for peak shaving. Use the set method of redis to atomically increment each request to ensure that the data to the database does not exceed the limit.