A very interesting MYSQL problem (related to indexing, millionth records, algorithm).

Question

This problem is rather difficult to describe and therefore difficult to find an answer. I hope that some expert can share with you opinions on this matter.

I have a table with about 1 million records. The table structure is similar to something like this:

items{
  uid (primary key, bigint, 15)
  updated (indexed, int, 11)
  enabled (indexed, tinyint, 1)
}

The scenario is as follows. Every day I have to select all the records and do some processing. It takes about 3 seconds to process each item.

I wrote a PHP script to extract 200 elements each time using the following.

select * from items where updated > unix_timestamp(now()) - 86400 and enabled = 1 limit 200;

Then I will update the "updated" field of the selected items to make sure that it will not be selected again within one day. The selected query is this.

update items set updated = unix_timestamp(now()) where uid in (1,2,3,4,...);

PHP , MYSQL.

---

, 3 , . PHP 10 .

, , . 100 !

---

- , ?

Answer 1

, , :

. unix_timestamp (now()) - 86400)

... now() , , .

. ,

(DISABLE KEYS) - (ENABLE KEYS).

Answer 2

, enabled - , . , UPDATE .

, , , 3 , 200. ?

Answer 3

:

• dispatcher.php: .
  • worker.php HTTP, UID ( , worker.php , UID )
  • worker.php. , , , . . PHP?".
  • , . MySQL LIMIT updated.
• worker.php:
  • .
  • ( )
• dispatcher.php: .
  • ,
• • worker.php , . " " .

.

Answer 4

:

ALTER TABLE items DISABLE KEYS;

, ,

ALTER TABLE items ENABLE KEYS;

, .

Answer 5

int, bigint.

Answer 6

:

Use HANDLER, which will greatly improve your performance:

http://dev.mysql.com/doc/refman/5.1/en/handler.html