Solution to index failure in MySQL due to different field character sets

What is an index? Why create an index?

Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL must read the entire table starting from the first record until it finds the relevant row. The larger the table, the more time it takes to query data. If the column being queried in the table has an index, MySQL can quickly reach a location to search the data file without having to look through all the data, which will save a lot of time.

For example, there is a person table with 20,000 records, recording the information of 20,000 individuals. There is a Phone field that records each person's phone number. Now we want to query the information of the person whose phone number is xxxx.

If there is no index, the table will be traversed from the first record to the next record until the information is found.

If there is an index, the Phone field will be stored in a certain way so that when querying the information on this field, the corresponding data can be found quickly without having to traverse 20,000 data items. There are two types of index storage in MySQL: BTREE and HASH. That is, use a tree or Hash value to store the field. To know how to search in detail, you need to know the algorithm. Now we just need to know the role and function of the index.

introduction

I wrote a SQL statement today. The amount of data in the tables involved was about 500,000. The query took 8 seconds. This is just data from the test server. If it is put on the official server, it will definitely crash as soon as it is run.

SELECT
 Orders. NO,
 Guid No,
 Orders.CreateTime,
 sum(OrderItem.Quantity) AS Quantity,
 Brand.NAME AS BrandName,
 member.Mobile,
 Street AS deliveryaddress,
 Area
FROM
 Orders
INNER JOIN OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNo
INNER JOIN Brand ON Brand.Id = Orders.BrandId
INNER JOIN member ON member.Id = 13
INNER JOIN memberaddress ON member.Id = memberaddress.MemberId
WHERE
 orders.GuidNo IN (
  SELECT
   orderpayment.OrderGuidNo
  FROM
   paymentrecord
  LEFT JOIN orderpayment ON paymentrecord.`No` = orderpayment.PaymentNo
  WHERE
   paymentrecord.PaymentMethod = 'MemberCard'
  AND paymentrecord.Payer = 13
 )
GROUP BY
 GuidNo;

Then I used EXPLAIN to analyze it and found that the Orders table did not hit the index. However, the index had been set for the GuidNo in Orders, but it could not be hit.

Solution process

Then I split the above statement into two statements. First, modify the SQL statement as follows: write the subquery data directly into the SQL statement, and the query takes 0.12 seconds.

SELECT
 Orders. NO,
 Guid No,
 Orders.CreateTime,
 sum(OrderItem.Quantity) AS Quantity,
 Brand.NAME AS BrandName,
 member.Mobile,
 Street AS deliveryaddress,
 Area
FROM
 Orders
INNER JOIN OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNo
INNER JOIN Brand ON Brand.Id = Orders.BrandId
INNER JOIN member ON member.Id = 13
INNER JOIN memberaddress ON member.Id = memberaddress.MemberId
WHERE
 orders.GuidNo IN (
  '0A499C5B1A82B6322AE99D107D4DA7B8',
  '18A5EE6B1D4E9D76B6346D2F6B836442',
  '327A5AE2BACEA714F8B907865F084503',
  'B42B085E794BA14516CE21C13CF38187',
  'FBC978E1602ED342E5567168E73F0602'
 )
GROUP BY
 Guid No

Second: The SQL that runs the subquery alone also takes only 0.1s

SELECT
   orderpayment.OrderGuidNo
  FROM
   paymentrecord
  LEFT JOIN orderpayment ON paymentrecord.`No` = orderpayment.PaymentNo
  WHERE
   paymentrecord.PaymentMethod = 'MemberCard'
  AND paymentrecord.Payer = 13

Now the problem is clear. It must be a problem related to the subquery and the parent query. Because the subquery is very fast when it is alone, the parent query is also very fast when it uses the subquery data directly, but it is very slow when the two are combined. The problem can be roughly pinned on the two associated fields OrderGuidNo.

Finally, it is found that the character sets of the orderpayment table and the Orders table are different. The character set of one table is: utf8_general_ci, and the other is: utf8mb4_general_ci. (You don’t know until you check. You find that in a database, many tables have different character sets.)

Change the character set of the orderpayment table and the character set of OrderGuidNo in the table to: utf8_general_ci

ALTER TABLE orderpayment DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; //Change the character set of the table ALTER TABLE orderpayment CHANGE OrderGuidNo OrderGuidNo VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; //Change the character set of the field

Then use EXPLAIN to analyze it, and you can see that the indexes are used.

Then run, the query took 0.112 seconds.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.