Re: how to speed up query on a table with over 20million records?

[li zheng]

here is another table with only 2 million records.
I don't think 2 million records in a table should be a problem to mysql.
but still the query below takes 14 minute.
anything wrong with my database?

server:
aws Aurora MySQL 5.7.12
db.t2.small (1 vCPU, 2GB memory)


table structure:
CREATE TABLE `jpstock_price_1w` (
`symbol_id` int(11) NOT NULL,
`datetime` datetime NOT NULL,
`open` double NOT NULL,
`high` double NOT NULL,
`low` double NOT NULL,
`close` double NOT NULL DEFAULT '0',
`volume` bigint(11) NOT NULL,
`updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`symbol_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


rows:
2,523,912


sql:
select symbol_id, max(datetime) from jpstock_price_1w group by symbol_id;


explain:
"id": 1,
"select_type": "SIMPLE",
"table": "jpstock_price_1w",
"partitions": null,
"type": "range",
"possible_keys": "PRIMARY",
"key": "PRIMARY",
"key_len": "4",
"ref": null,
"rows": 10436,
"filtered": 100,
"Extra": "Using index for group-by"


Trace:
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `jpstock_price_1w`.`symbol_id` AS `symbol_id`,max(`jpstock_price_1w`.`datetime`) AS `max(datetime)` from `jpstock_price_1w` group by `jpstock_price_1w`.`symbol_id`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`jpstock_price_1w`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"table": "`jpstock_price_1w`",
"const_keys_added": {
"keys": [
"PRIMARY"
],
"cause": "group_by"
},
"range_analysis": {
"table_scan": {
"rows": 2523912,
"cost": 518850
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"symbol_id",
"datetime"
]
}
],
"best_covering_index_scan": {
"index": "PRIMARY",
"cost": 510318,
"chosen": true
},
"group_index_range": {
"potential_group_range_indexes": [
{
"index": "PRIMARY",
"covering": true,
"rows": 10436,
"cost": 10753
}
]
},
"best_group_range_summary": {
"type": "index_group",
"index": "PRIMARY",
"group_attribute": "datetime",
"min_aggregate": false,
"max_aggregate": true,
"distinct_aggregate": false,
"rows": 10436,
"cost": 10753,
"key_parts_used_for_access": [
"symbol_id"
],
"ranges": [
],
"chosen": true
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "index_group",
"index": "PRIMARY",
"group_attribute": "datetime",
"min_aggregate": false,
"max_aggregate": true,
"distinct_aggregate": false,
"rows": 10436,
"cost": 10753,
"key_parts_used_for_access": [
"symbol_id"
],
"ranges": [
]
},
"rows_for_plan": 10436,
"cost_for_plan": 10753,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`jpstock_price_1w`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 10436,
"access_type": "range",
"range_details": {
"used_index": "index_for_group_by(PRIMARY)"
},
"resulting_rows": 10436,
"cost": 12840,
"chosen": true,
"use_tmp_table": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 10436,
"cost_for_plan": 12840,
"sort_cost": 10436,
"new_cost_for_plan": 23276,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`jpstock_price_1w`",
"attached": null
}
]
}
},
{
"clause_processing": {
"clause": "GROUP BY",
"original_clause": "`jpstock_price_1w`.`symbol_id`",
"items": [
{
"item": "`jpstock_price_1w`.`symbol_id`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`jpstock_price_1w`.`symbol_id`"
}
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"index_order_summary": {
"table": "`jpstock_price_1w`",
"index_provides_order": true,
"order_direction": "asc",
"index": "PRIMARY",
"plan_changed": false
}
}
},
{
"refine_plan": [
{
"table": "`jpstock_price_1w`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}