-1

Here is my query.

select user_id, sum(user_points.points) overallpts, username
from user
inner join user_points on user.user_id = user_points.user_id
where user_points.date >= '2020-01-01 00:00:00'
  and user_points.user_id > 1
group by user_id
order by overallpts desc
limit 0,10;

user table index

user_id - PK

user_points table index
id - PK
user_id - user_points_idx
(date,user_id) - user_points_date_userid_idx

here is the result of my explain:

id select_type table partitions type possible_keys key key_len ref rows filtered extra
1 SIMPLE user_points NULL ALL user_points_idx,user_points_date_userid_idx NULL NULL NULL 3M 10.65 Using where; Using temporary; Using filesort
1 SIMPLE user NULL eq_ref PRIMARY PRIMARY 8 NULL user_ponts.user_id 1 100.00

EDIT: Here is my SHOW CREATE TABLE

     CREATE TABLE `user` (
     `user_id` BIGINT(20) NOT NULL,
     `username` VARCHAR(50) NOT NULL,
     `points` DECIMAL(20,4) NOT NULL DEFAULT '0.0000',
     `CreatedDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `lastUpdateDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON     UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (`user_id`),
     UNIQUE KEY `username_UNIQUE` (`username`),
     KEY `points_idx` (`points`),
     CONSTRAINT `social_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;


    CREATE TABLE `user_points` (
     `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
     `userId` BIGINT(20) NOT NULL,
     `points` DECIMAL(20,4) NOT NULL DEFAULT '0.0000',
     `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `user_points_user_FK_idx` (`userId`),
      KEY `user_points_date_user_idx` (`date`,`userId`)) ENGINE=INNODB AUTO_INCREMENT=3077571 DEFAULT CHARSET=utf8mb4
4
  • force the index, in the end it is an alghorithm thqat follows it s rules and so can not deep to find the perfect answer, so you have to test the indexes yourself – nbk Jan 25 at 10:38
  • Here is my query. Your query is wrong. Either you skip GROUP BY or the output is indefinite (user_id and username will be taken from indefinite row). – Akina Jan 25 at 10:43
  • why is mysql not using the composite index on my query? Index selectivity is too low - so table scan is preferred. Try to force index usage - execution time must increase. – Akina Jan 25 at 10:45
  • @Akina yes my query that I put here in my question is wrong, my bad. But the explain result is the same. I tried to get it to work by using FORCE index to use user_points_date_userid_idx but is there any other way around? – JRA Jan 26 at 2:49
1

When more than about 20% of the table matches an index, that index is not useful. Instead, the Optimizer will simply scan the table.

Involving an index involves first locating the items in the index's BTree, then reaching over into the data's BTree.

The query has 2 range tests; once one range is used, the rest of the composite index is not used.

The query has another problem... An aggregation (SUM()) without a GROUP BY. Perhaps you want:

SELECT user_id
       ( SELECT sum(points)
           FROM user_points
           WHERE user_id = user.user_id
             AND date >= '2020-01-01'
       ) overallpts,
       username
FROM user
WHERE user_id > 1
ORDER BY overallpts DESC
LIMIT 0,10;

In this case, I would have this index on user_points:

INDEX(user_id, date)

If you wish to discuss this further, please provide SHOW CREATE TABLE for each table. (There are some subtle issues that may impact the optimal index.)

Sometimes it helps to turn the query inside out:

SELECT user_id, a.overallpts, u.username
    FROM (
        SELECT  user_id, 
                sum(points) AS overallpts
            FROM user_points
            WHERE user_id > 1
              AND date >= '2020-01-01'
            GROUP BY user_id
            ORDER BY overallpts DESC
            LIMIT 10
         ) AS a
    JOIN user AS u USING(user_id)
    ORDER BY overallpts DESC;  -- yes, repeated

The advantage here is that the "derived table" (the subquery in FROM) decreases the number of rows before touching the other table (user).

Let's see SHOW CREATE TABLE for each table. It may be advantageous to change, in user_points, to PRIMARY KEY(user_id, date, ...).

7
  • +1 although my original query that i posted here is incomplete, I forgot to input here my GROUP BY, my bad. What i am trying to get is the top 10 user who has the most pts. I got it to use my composite index by using FORCE INDEX but is there any other way? thank you – JRA Jan 26 at 2:52
  • @JRA - The real question is whether the query is faster when using the index. There are cases where FORCE slows down the query. – Rick James Jan 26 at 18:42
  • @JRA - How fast does my version work after adding ORDER BY and LIMIT? (But not GROUP BY or FORCE.) – Rick James Jan 26 at 18:46
  • The query took around 2-3secs before I applied the FORCE INDEX. Can you edit your query above so I can try to check its speed? – JRA Jan 27 at 6:04
  • @JRA - I augmented my first suggestion and added a second. – Rick James Jan 27 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.