the database currently has 1 million data (it has been running for about half a month)
the following sentence takes 6 seconds to run (1 million results for where alone), and both user_id and time are indexed
the database currently has 1 million data (it has been running for about half a month)
the following sentence takes 6 seconds to run (1 million results for where alone), and both user_id and time are indexed
found the problem and learned a new knowledge.
SELECT user_id,count(*) as count from prize_numbers where user_id > 0 and time > 1545972485 GROUP BY user_id ORDER BY count desc as mentioned, the query takes 6 seconds, and user_id,time has an index set up.
after many attempts, I found that the query speed of the following two sentences is very fast, within 1 second.
SELECT user_id,count(*) as count from prize_numbers where time > 1545972485 GROUP BY user_id ORDER BY count descSELECT user_id,count(*) as count from prize_numbers where user_id > 0 GROUP BY user_id ORDER BY count descsearched a lot of information and saw something called aggregate index. I tried it, and the speed was fast at once. The statement is as follows:
alter table add index (1,2) for example, my:
alter table prize_numbers add index union_user_id_time (user_id, time) what fields should be added to the aggregate index? I roughly understand that the conditional fields after the where statement are added to an aggregate index.
SELECT user_id,count(id) as count from prize_numbers where user_id > 0 and time > 1545972485 GROUP BY user_id ORDER BY count desc