Q: Select only recent updated rows in MySQL db with a large data volume I'm working with a InnoDB table that contains up to 30000 records. Rows are updated frequenty with stock_quantity value. What i need to do is to select only the most recent updated rows with a scheduled task and perform some actions thru a WebService.
Just trying to understand which is the best way for doing this without kill performance. I'm thinking at 3 different solution:


*

*using a datetime column and update its value on each modify. Then select rows where date_col > NOW()-20 min. (20 min. is the frequency crontab is running)

*using a boolean column and set the value to true each time the row is modified. Then select rows where boolean_col is true. If the task is executed set back the value of boolean_col to false.

*using a second table to store recent updated columns. On each update of a row in table_1 copy the row to table_2. Then select all rows from table_2, perform actions and truncate table_2.


Anyway I'm pretty sure the right solution is not listed up there... so does anyone have some good advice? Thanks.

A: fist at all,
30,000 record is not that big ...  
i prefer method 1 with some additional changes


*

*set the datetime column default to on update current_timestamp

*build an index of this column


method 2 will incurred redundant write, and read
method 3 is the worse, it almost double x double the write and read operations

A: I would personally use your option 2.
I would seriously look at a tigger to set the value to 1 if the row is edited. Of course excluding and update that only effect the boolean col.
I would then have the cron search the table when boolean = 1, return the list process the file and update the field back to 0 once complete.
This would be my approach, but like you said there might be a better way.
Another Idea:  You might also want to look at replacing your cron with the tigger and preform the action your cron does on record update might work...
