CakePHP 3.x - single model with multiple tables using suffix

Need Help
1

I have “logs” mysql table which I might like to reconfigure to multiple smaller tables as logs_01, logs_02, logs_03, logs_xx, and so on for better performance

The user table would have a field called log_table_name to identify which log_xx table contains the user’s data…

Below is what I tried so far, it worked.

Just wondering if this is the good approach.

LogsController.php (just saving some records to users logs table)

$this->Logs->table($user->log_table_name);
$this->Logs->save($log);

LogsTable.php (use log table based on logged in user preferences)

use Cake\Network\Session;

class LogsTable extends Table {

public function initialize(array $config)
{
$this->table('visits');
$this->session = new Session();
if($this->session->read('user.log_table_name')) {
    $this->table($this->session->read('user.log_table_name'));
}
....

Thanks !

2

I don’t understand the problem.
But maybe you could easy use Partitions in MySQL, grouping by sets of 1000 users, so you have some “virtual” sub-tables (improve perfomance) and still having one (maybe to generate reports)

See MySQL and partitioning tables with millions of rows for example.
Of course, it depends your use case.