Django ORM Query Too Slow-CodePudding

I use this database table with more than 10000000 datas. enter image description here

And use query

SELECT mytable.project, COUNT(*) AS `total`  
FROM mytable   
WHERE (`mytable`.`date` >= '2022-04-05' AND NOT (`mytable`.`user` = 'sw_int'))   
GROUP BY `mytable`.`project`
ORDER BY `total`

But this takes too many time despite the use of indexes. (about 20 secs) How can I solve this problem?

Additionally, index is like below enter image description here This made with django.

CodePudding user response：

Any of these may help the speed some. I can't tell which would be best, since it depends on the distribution of the data.

INDEX(date, user, project)
INDEX(user, project, date)
INDEX(project, date, user)

Each is "covering", meaning that the query can be performed entirely in the Index's BTree; this gives some performance boost.

One of the first two may shine due to filtering on date or user. The third may avoid the sort for GROUP BY project. (The sort for ORDER BY is unavoidable.)

Add all three; run it. Also do EXPLAIN SELECT ... to see which index that the Optimizer decided might be best.

(In the future, please include text, not images.)