我的mySql查询将在15秒内为500用户在loader.io中进行阈值处理

Question

My mysql query is

SELECT tbl_members.id,
       tbl_members.username,
       tbl_members.confirmcode,
       tbl_members.preferredlive,
       tbl_members.sex,
       tbl_members.unlock_for,
       tbl_members.createddate,
       tbl_members.createddate,
       tbl_members.headline
FROM tbl_members
WHERE tbl_members.status ='Active'
  AND tbl_members.id <> '355'
  AND (sex = 2
       OR sex = 2)
  AND tbl_members.whocanseeme = 'visible'
ORDER BY tbl_members.id DESC

There are 4163 records and showing 2040 records due to sex check.

Explain mySql Query :- Explain sql Query

Its my table structure Table structure

I have also added indexes in id,zipcode,suburb,status,whocanseeme,sex.

Please help So how i can make it fast and stop threshold in loder.io with max . Thanks in Advance.

Answer 1

to improve your query performance you have to create a composit index on status,whocanseems,sex those 3 fields are exact matching columns so you can get better performance if you have a index in that order .

i think member id is primary key in this table

You have some large text fields in this table and it contain large number of columns please try to normalize it else it will show poor performance on large data set