MySQL log data table, how much faster is MyISAM than InnoDB query?

Table of Contents

Briefly introduce the two engines

Start actual measurement

Comparing the capacity occupied by data, MyISAM occupies less space.

From the comparison of query efficiency, MyISAM is indeed faster.

MYISAM table structure

Laravel table creation statement

Laravel scheduled task-clean logs and keep only the latest 3 million records

---

A brief introduction to the two engines

• InnoDB supports transaction functions, foreign keys, row-level locks and other advanced functions of relational databases.
• MYISAM has better performance and takes up less storage space.

Both storage engines have their own advantages and disadvantages, and the choice depends on the specific application.

Log tables only have two operations: add and query. The business is simple, so of course MyISAM is more suitable.

Let’s test the specific difference between query and storage.

Start actual measurement

data preparation:

1. InnoDB and MyISAM each create a table and import 100,000 pieces of data respectively.
2. Create a full-text index for the `sql` field

MYISAM table structure:

1CREATE TABLE `api_request_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT '0' COMMENT 'admin_id',
  `user_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'admin_name',
  `method` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Interface request method',
  `path` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Interface address',
  `params` varchar(5000) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Request parameters',
  `result` varchar(5000) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'return result',
  `exec_time` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Interface time, unit seconds',
  `sql` text COLLATE utf8mb4_unicode_ci COMMENT 'executed sql',
  `ip` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Client IP',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
  PRIMARY KEY (`id`),
  KEY `api_request_log_method_index` (`path`),
  FULLTEXT KEY `api_request_log_params_fulltext` (`params`),
  FULLTEXT KEY `api_request_log_sql_fulltext` (`sql`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Interface request record';

innodbdb table The actual data is the same as the data in the myisam table. The one shown in the picture below is not accurate.

1. From the comparison of the capacity occupied by data, it can be seen that MyISAM takes up less space

2. From the query efficiency comparison, MyISAM is indeed faster

Laravel’s table creation statement

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateApiRequestLogTable extends Migration
{
    public function up()
    {
        Schema::create('api_request_log', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id')->nullable()->default(0)->comment('admin_id');
            $table->string('user_name',50)->nullable()->default(0)->comment('admin_name');
            $table->string('method',10)->nullable()->default('')->comment('Interface request method');
            $table->string('path')->nullable()->default('')->comment('interface address')->index();
            $table->string('params',5000)->nullable()->default('')->comment('request parameter')->fulltext();
            $table->string('result',5000)->nullable()->default('')->comment('return result');
            $table->string('exec_time',10)->nullable()->comment('Interface time, unit seconds');
            $table->text('sql')->default('')->nullable()->comment('executed sql')->fulltext();
            $table->string('ip',15)->default('')->nullable()->comment('Client IP');
            $table->timestamp('created_at')->useCurrent()->comment('Created_at');

            // $table->index('path');
            $table->engine = 'MyISAM';
        });
        DB::statement("alter table `api_request_log` comment 'Interface request record'");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('api_request_log');
    }
}

Laravel scheduled task – clean the log, keep only the latest 300,000 records

<?php

namespace App\Console\Commands;

use App\Models\ApiRequestLog;
use Illuminate\Console\Command;

class CleanApiLog extends Command
{
    protected $signature = 'api-log:clean {limit=300000}';
    
    protected $description = 'Clean the interface request record table data. The optional parameter limit is used to set the latest number of data to retain (the default value is 300,000) ';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle(){
        // Get parameters. How many pieces of data to keep?
        $limit = $this->argument('limit');

        $delete_count = $last_id = 0;

        // Get the ID of the latest data
        $last_id = ApiRequestLog::query()->latest()->value('id');
        if ($last_id){
            $end_id = $last_id - $limit; // Get the end ID
            if ($end_id > 0){
                $delete_count = ApiRequestLog::query()->where('id', '<', $end_id)->delete();
            }
        }
        $msg = 'Clean interface request record table data: execution completed limit='.$limit.',last_id='.$last_id.',delete_count='.$delete_count;
        info($msg);
        return $this->info($msg);
    }
}