Solutions to Mysql index performance optimization problems

The optimization created by MySQL is to add indexes, but sometimes you may encounter situations where adding indexes cannot achieve the desired effect.

After adding so, the search still fails for all data. The reason is sql

EXPLAIN SELECT
      cs.sid,
      -- c.courseFrontTitle,
      -- c.imgBig,
      cs.studyStatus,
      coi.fee,
      -- act.PROC_INST_ID_ AS processId,
      cs.createDTM,
      cs.payStatus,
      cs.isCompleted,
      cs.saleChannel,
cs.isDelete
    FROM
      Biz_CourseStudy cs

    LEFT JOIN Biz_CourseOrderItem coi ON cs.sid = coi.CourseStudyID 
    
    WHERE
      cs.studentID = 00001 and cs.payStatus not in(0)

By looking at the index, the reason is that sid is bigint and the type of CourseStudyID is varchar. The reason is here. After changing the type to bigint, the query speed is instantly improved.

I have encountered such a situation. After analyzing the extra, I found that the speed was OK without order by 0.6s and added order by 6s.

The solution is to create an index for the order by. Here my order by is two fields.

order by endTime desc ,isDelete desc

Create a joint index for ab, index_a_b

SELECT xxx FROM manage a FORCE INDEX(index_a_b)
LEFT JOIN f_name f ON f.user_id = a.user_id
ORDER BY a.endTime desc,a.isDelete desc

At this time, looking at the performance, Using filesort has disappeared

The speed directly becomes 0.6s

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.