MySQL WHERE substring_index count(*) 效率问题

Question

SELECT SUBSTRING_INDEX(domain,".",-1) as tld, count(*) AS count \
               FROM domain_whois WHERE flag <> -6 GROUP BY tld

domain_whois表中有两个字段，domain 与 flag。domain例：baidu.com, flag例：-6
大约七百万数据，运行上面的数据需要30+s时间，请问有什么技巧可以提高效率？

Answer 1

Exchange space for time. If you often perform such queries, add a field tld, run the result of SUBSTRING_INDEX directly when entering the database, and store it in this tld field

However, since you are scanning the entire table, this should not be fast enough. You can further create a redundant information table of tld, with two fields, one is tld, and the other is count quantity, which is stored in the database. Update the quantity every time, so if you want to get the count(*) data of tld such as com, net, you can directly read this table

This is a relatively simple and preliminary optimization solution. Remember that performance is closely related to how you query

Answer 2

You can try adding an index to the flag column and change the WHERE condition to flag>-6 or flag<-6