0

I have a MySQL table "number_records" with more than 10 million entries. The table has following structure. 

id | number     | cat_id | dialed | uuid |

1  | 2123727345 | 148    |   0    |      |

2  | 2123727346 | 148    |   0    |      |

3  | 212372737  | 147    |   0    |      |

I have two processes having same cat_id(148) but different UUID concurrently using above table to get numbers.

First Process uuid = abcd-efgh-12345-78908

Second Process uuid = zxcv-qwrt-uuuu-kklll

I want that each process gets only a unique number and after processing set its dialed status to '1'. (Both processes do not get the same number)

I am doing above in three queries in each process so that each process gets only one unique number.

For the first process.

  1. update numbers_records set uuid = 'abcd-efgh-12345-78908' where cat_id = 148 and dialed = 0 and uuid = "";

  2. select number from numbers_records where uuid = 'abcd-efgh-12345-78908' and dialed = 0 and cat_id = 148;

  3. update numbers_records set dialed = 1 where uuid ='abcd-efgh-12345-78908' and cat_id = 148 and dialed = 0;

For the Second process.

  1. update numbers_records set uuid = 'zxcv-qwrt-uuuu-kklll' where cat_id = 148 and dialed = 0 and uuid = "";

  2. select number from numbers_records where uuid = 'zxcv-qwrt-uuuu-kklll' and dialed = 0;

  3. update numbers_records set dialed = 1 where uuid = 'zxcv-qwrt-uuuu-kklll'

This process is working fine. And above queries make sure that I get one unique number per process.

But table contains more than 10 million records. And these queries takes more than 5 seconds. I want to speed up the process.

My manager asked me to shift above table to Redis.And I am confused how to shift above table.

How could I use Redis in above case?

Any suggestion is much appreciated. Best Regards.

user3310052
  • 71
  • 1
  • 7

2 Answers2

0

Looks like you are using MySql table as tasks queue. You can use Redis LIST with LPOP or BLPOP operations to get next task in workers and HASH to store task information. Also if you need processing based on cat_id you need to push tasks to different lists:

... number_records_tasks:147 number_records_tasks:148 ...

To get multiple tasks at once use suggestions from this question.

Community
  • 1
  • 1
uglide
  • 1,695
  • 14
  • 19
0

As you are not deleting entries in this table, then any solution based just on queue like structure will not be equal - after processing item in queue you will lose it.

Only thing you could do is to add other mechanism on top of MySQL table to handle work queue (redis list, rabbitmq, beanstalkd).

In my opinion you should check other solutions first, they may be way easier in implementation:

  • do you have multicolumn index (cat_id, dialed, uuid) and big enough memory buffers in your database? Even with 10 millions records those queries should be really fast when using index
  • you could use table partitioning ( https://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html ) to divide this table into smaller ones
my-nick
  • 691
  • 1
  • 5
  • 11