How to increase the performance of COUNT (DISTINCT field1) ... GROUP BY field2?

Question

I have the following request

EXPLAIN SELECT COUNT(DISTINCT ip_address) as ip_address, exec_date FROM requests GROUP BY exec_date; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE requests range NULL daily_ips 263 NULL 488213 Using index for group-by (scanning) 

With daily_ips coverage daily_ips

 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment requests 1 daily_ips 1 exec_date A 16 NULL NULL YES BTREE requests 1 daily_ips 2 ip_address A 483492 NULL NULL YES BTREE 

Can this query be further optimized?

What exactly does Using index for group-by (scanning) mean? Does this mean that the entire GROUP BY is executed entirely from the index, and that the COUNT(DISTINCT ip_address) part COUNT(DISTINCT ip_address) is not in the expression?

Answer 1

Based on the data you provided, I see no way to optimize the query.

As for your subsequent question, the MySQL man page describing the output of the explanation for Using the index for group-by says:

Similar to the method of accessing the table Using Index Table, Using Index for Group indicates that MySQL has found an index that can be used to retrieve all columns of the GROUP BY or DISTINCT query without additional access to the virtual table. In addition, the index is used in the most efficient way, so for each group only a few index entries are read. See Section 8.13.10, “GROUP BY Optimization,” for details.

Your index is especially suitable for speeding up your query. Since only indexed fields are selected (each column in your query is also displayed in the index), MySQL may not even need to hit the table at all, since all relevant data appears in the index.

If executing the query was like doing a search on google , imagine that you don’t need to go to any of the related sites because you found the information you were looking for directly in the search results — it looks like you don’t need to crawl the table data. Here's more information on how MySQL uses indexes :

In some cases, a query can be optimized to retrieve values ​​without consulting data rows. (An index that provides all the necessary results for a query is called a covering index .) If the query uses only columns from the table, which are numeric and which form the leftmost prefix for some key, the selected values ​​can be obtained from the index tree for faster speed:

SELECT key_part3 FROM tbl_name WHERE key_part1 = 1

Answer 2

You can specify Objectify:

Objectify ofy = ObjectifyService.begin (); Query query = ofy.query (here is the class name.class) .filter ("column name in the table", value for the query) .list ();

Prior to this, you may need to add a jar for Objectify.