SoFunction
Updated on 2025-04-12

The problem of slow query speed of mysql and its solution

MySQL association query speed is slow

1. Record the reason

MySQL association query speed is very slow, which is caused by inconsistent field character set rules.

1.1 In one online service

  • I found that there are several association query speeds that are particularly slow. I tried a variety of optimization solutions and finally fixed the problem in the count() function
  • Since it is a pagination query, there will definitely be count() to query the number of records, but every time you execute count(), it will be stuck for several seconds.

1.2 Final Discovery

  • The two associated tables The character set rules are inconsistent, one is utf8_general_ci, and the other is utf8mb4_0900_ai_ci
  • The specific reason may be that the mysql version of the online server and the local server is different, and there is a problem during the overlay.

2. Solution

# View character set rules for each field:show full columns from Table name;
# Modify the character set of the field:ALTER TABLE Table name CHANGE Field name Field name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
# Modify the default character set of the table:ALTER TABLE Table name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# Check the database encoding:SHOW CREATE DATABASE db_name;
# Check the table encoding:SHOW CREATE TABLE tbl_name;

3. Specific operation

  • Here I am changing one field and one field. The fields of the two tables are unified into utf8_general_ci format
ALTER TABLE Table name CHANGE Field name Field name varchar(255) 
CHARACTER SET utf8 COLLATE utf8_general_ci;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.