如何应用索引此查询

Question

SELECT  user_id,username,full_name,display_name,profile_pic,email,
        fb_id,image1,image2,image3,image4,image5,default_pic,
        street_address,locality,country,state,is_verified,is_online,
        city,image6,image7,image8,image9,image10,last_login,
        IFNULL( (
                SELECT  STATUS
                    FROM  vidioo_contacts
                    WHERE  (contact_id = '55000'
                              AND  user_id = vu.user_id
                          )
                      OR  (contact_id = vu.user_id
                              AND  user_id = '55000')),0) AS STATUS,
        (3959 * ACOS( COS(RADIANS(0)) * COS(RADIANS(latitude)) * 
           COS(RADIANS(longitude) - RADIANS(0)) + SIN(RADIANS(0)) *
           SIN(RADIANS(latitude)))
           ) AS distance
    FROM  vidioo_users vu
    WHERE  user_id != '55000'
      AND  gender LIKE 
        ( SELECT  CASE WHEN show_me = 'everyone'
                     THEN '%'
                     ELSE IF(LENGTH(show_me) < 1, '%', show_me)
                  END
            FROM  vidioo_users
            WHERE  user_id = '55000'
            LIMIT  1 
        )
      AND  IFNULL(vu.is_deleted,0) != 55000
      AND  vu.user_id NOT IN (
        SELECT  DISTINCT contact_id
            FROM  vidioo_blocked_users
            WHERE  bloked_by_user = 55000
                          )
      AND  DATEDIFF(NOW(),last_login) < 7
    ORDER BY  last_login DESC
    LIMIT  0,20

我希望将索引应用于此查询。

Answer 1

如果表有一个多列索引，那么mysql使用索引的最左边的前缀。例如，您有以下选择查询 -

'从 tbl_name中选择 *，其中 COL1=VAL1;从tbl_name中选择 *，其中 COL1=VAL1 和 COL2=VAL2;

从 tbl_name 中选择 *，其中 COL2=VAL2;从 tbl_name 中选择 *，其中 COL2=VAL2 和 COL3=VAL3

;

如果 (col1， col2， col3( 上存在索引，则只有前两个查询使用该索引。第三个和第四个查询确实涉及索引列，但 (col2( 和 (col2， col3( 不是 (col1， col2， col3( 的最左侧前缀。

在您的情况下，您必须创建 3 个索引作为

1. Index_1(user_id、性别、last_login(
2. Index_2(user_id(
3. Index_3(bloked_by_user(

注意：- 太多的索引会减慢 INSERT 查询处理速度。欲了解更多详情，请单击此处

Answer 2

除了其他建议外，AND DATEDIFF(NOW(),last_login) < 7更改为

last_login > NOW() - INTERVAL 7 DAY

您拥有的内容将列last_login隐藏在函数(DATEDIFF(中，使其无法使用索引。 然后，还添加

INDEX(last_login)

(这可能无济于事，但可能会。

另外，更改

AND  vu.user_id NOT IN (
    SELECT  DISTINCT contact_id
        FROM  vidioo_blocked_users
        WHERE  bloked_by_user = 55000
                      )

自

AND NOT EXISTS( SELECT * FROM vidioo_blocked_users
        WHERE  bloked_by_user = 55000
          AND  contact_id = vu.user_id )

䋰

INDEX(bloked_by_user, contact_id) -- in either order

Answer 3

尝试在这四个表列上添加索引，它应该有帮助：

contact_id,
user_id,
is_deleted,
last_login.