ofcourse. it’s not trouble at all!
without order-by
PROFILE SELECT * FROM contribution.cont_event cont WHERE game_id = 'RUUvr1574773090344' AND event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-31 23:59:59';
{
"profile":[
{
"executor":"Top",
"limit":"@@SESSION.`sql_select_limit`",
"actual_row_count":{ "value":300 },
"actual_total_time":{ "value":0 },
"inputs":[
{
"executor":"Gather",
"partitions":"all",
"query":"SELECT `cont`.`event_id` AS `event_id`, `cont`.`action` AS `action`, `cont`.`correlation_id` AS `correlation_id`, `cont`.`status` AS `status`, `cont`.`event_arrival_time` AS `event_arrival_time`, `cont`.`create_time` AS `create_time`, `cont`.`create_ts` AS `create_ts`, `cont`.`operator_id` AS `operator_id`, `cont`.`game_id` AS `game_id`, `cont`.`player_id` AS `player_id`, `cont`.`segment_code` AS `segment_code`, `cont`.`bet_amount_original` AS `bet_amount_original`, `cont`.`bet_amount_converted` AS `bet_amount_converted`, `cont`.`cont_amount_player` AS `cont_amount_player`, `cont`.`cont_amount_operator` AS `cont_amount_operator`, `cont`.`cont_amount_total` AS `cont_amount_total`, `cont`.`operator_income` AS `operator_income`, `cont`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `cont`.`original_currency` AS `original_currency`, `cont`.`base_currency` AS `base_currency`, `cont`.`currency_rate` AS `currency_rate`, `cont`.`operator_game_code` AS `operator_game_code`, `cont`.`funnel_id` AS `funnel_id`, `cont`.`segment_name` AS `segment_name`, `cont`.`operator_game_name` AS `operator_game_name`, `cont`.`description` AS `description`, `cont`.`extra_fields` AS `extra_fields`, `cont`.`jackpot_game_name` AS `jackpot_game_name`, `cont`.`game_version` AS `game_version`, `cont`.`event_type` AS `event_type`, `cont`.`event` AS `event` FROM `contribution_0`.`cont_event` as `cont` WHERE ((`cont`.`game_id` = 'RUUvr1574773090344') AND (`cont`.`event_arrival_time` BETWEEN '2019-12-02 00:00:00' AND '2019-12-31 23:59:59')) LIMIT NULL OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)",
"alias":"remote_0",
"actual_row_count":{ "value":484, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"actual_total_time":{ "value":0 },
"start_time":{ "value":0 },
"end_time":{ "value":4226 },
"inputs":[
{
"executor":"Project",
"out":[
{
"alias":"",
"projection":"cont.event_id"
},
{
"alias":"",
"projection":"cont.action"
},
{
"alias":"",
"projection":"cont.correlation_id"
},
{
"alias":"",
"projection":"cont.status"
},
{
"alias":"",
"projection":"cont.event_arrival_time"
},
{
"alias":"",
"projection":"cont.create_time"
},
{
"alias":"",
"projection":"cont.create_ts"
},
{
"alias":"",
"projection":"cont.operator_id"
},
{
"alias":"",
"projection":"cont.game_id"
},
{
"alias":"",
"projection":"cont.player_id"
},
{
"alias":"",
"projection":"cont.segment_code"
},
{
"alias":"",
"projection":"cont.bet_amount_original"
},
{
"alias":"",
"projection":"cont.bet_amount_converted"
},
{
"alias":"",
"projection":"cont.cont_amount_player"
},
{
"alias":"",
"projection":"cont.cont_amount_operator"
},
{
"alias":"",
"projection":"cont.cont_amount_total"
},
{
"alias":"",
"projection":"cont.operator_income"
},
{
"alias":"",
"projection":"cont.cont_amount_jackpot"
},
{
"alias":"",
"projection":"cont.original_currency"
},
{
"alias":"",
"projection":"cont.base_currency"
},
{
"alias":"",
"projection":"cont.currency_rate"
},
{
"alias":"",
"projection":"cont.operator_game_code"
},
{
"alias":"",
"projection":"cont.funnel_id"
},
{
"alias":"",
"projection":"cont.segment_name"
},
{
"alias":"",
"projection":"cont.operator_game_name"
},
{
"alias":"",
"projection":"cont.description"
},
{
"alias":"",
"projection":"cont.extra_fields"
},
{
"alias":"",
"projection":"cont.jackpot_game_name"
},
{
"alias":"",
"projection":"cont.game_version"
},
{
"alias":"",
"projection":"cont.event_type"
},
{
"alias":"",
"projection":"cont.event"
}
],
"subselects":[],
"actual_row_count":{ "value":484, "avg":30.250000, "stddev":20.550243, "max":109, "maxPartition":6 },
"actual_total_time":{ "value":5, "avg":3.000000, "stddev":2.000000, "max":5, "maxPartition":0 },
"start_time":{ "value":551, "avg":1434.437500, "stddev":0.000000, "max":1755, "maxPartition":15 },
"network_time":{ "value":3, "avg":1.500000, "stddev":1.500000, "max":3, "maxPartition":0 },
"inputs":[
{
"executor":"Top",
"limit":"?",
"actual_row_count":{ "value":486, "avg":30.375000, "stddev":20.766183, "max":110, "maxPartition":6 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"inputs":[
{
"executor":"Filter",
"condition":[
"cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?"
],
"subselects":[],
"actual_row_count":{ "value":498, "avg":31.125000, "stddev":23.127027, "max":120, "maxPartition":6 },
"actual_total_time":{ "value":50, "avg":42.000000, "stddev":8.000000, "max":50, "maxPartition":0 },
"start_time":{ "value":15, "avg":36.000000, "stddev":0.000000, "max":60, "maxPartition":15 },
"inputs":[
{
"executor":"ColumnStoreScan",
"db":"contribution",
"table":"cont_event",
"alias":"cont",
"index":"KEY operator_id (event_arrival_time, action, operator_id, game_id, correlation_id) USING CLUSTERED COLUMNSTORE",
"storage":"columnar",
"est_table_rows":"259200577",
"est_filtered":"1",
"actual_row_count":{ "value":7007223, "avg":437951.437500, "stddev":250062.862187, "max":1402339, "maxPartition":3 },
"actual_total_time":{ "value":4187, "avg":4184.500000, "stddev":2.500000, "max":4187, "maxPartition":8 },
"start_time":{ "value":14, "avg":35.000000, "stddev":0.000000, "max":59, "maxPartition":15 },
"memory_usage":{ "value":100663296, "avg":6291456.000000, "stddev":0.000000, "max":6291456, "maxPartition":0 },
"segments_scanned":{ "value":34, "avg":2.125000, "stddev":0.330719, "max":3, "maxPartition":3 },
"segments_skipped":{ "value":217, "avg":13.562500, "stddev":0.496078, "max":14, "maxPartition":0 },
"segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"segments_filter_encoded_data":{ "value":18, "avg":1.125000, "stddev":0.330719, "max":2, "maxPartition":3 },
"inputs":[]
}
]
}
]
}
]
}
]
}
]
}
],
"version":"2",
"warning":"requires compile",
"info":{
"memsql_version":"6.8.11",
"memsql_version_hash":"e973c625ae6d372c2d41d39b19612202c244fd7a",
"num_online_leaves":"2",
"num_online_aggs":"1",
"context_database":"(null)"
}
}
with order-by
PROFILE: SELECT * FROM contribution.cont_event cont WHERE game_id = 'RUUvr1574773090344' AND event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-31 23:59:59' ORDER BY event_arrival_time DESC
{
"profile":[
{
"executor":"Top",
"limit":"@@SESSION.`sql_select_limit`",
"actual_row_count":{ "value":300 },
"actual_total_time":{ "value":0 },
"inputs":[
{
"executor":"GatherMerge",
"order":[
"remote_0.event_arrival_time DESC"
],
"partitions":"all",
"query":"SELECT `cont`.`event_id` AS `event_id`, `cont`.`action` AS `action`, `cont`.`correlation_id` AS `correlation_id`, `cont`.`status` AS `status`, `cont`.`event_arrival_time` AS `event_arrival_time`, `cont`.`create_time` AS `create_time`, `cont`.`create_ts` AS `create_ts`, `cont`.`operator_id` AS `operator_id`, `cont`.`game_id` AS `game_id`, `cont`.`player_id` AS `player_id`, `cont`.`segment_code` AS `segment_code`, `cont`.`bet_amount_original` AS `bet_amount_original`, `cont`.`bet_amount_converted` AS `bet_amount_converted`, `cont`.`cont_amount_player` AS `cont_amount_player`, `cont`.`cont_amount_operator` AS `cont_amount_operator`, `cont`.`cont_amount_total` AS `cont_amount_total`, `cont`.`operator_income` AS `operator_income`, `cont`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `cont`.`original_currency` AS `original_currency`, `cont`.`base_currency` AS `base_currency`, `cont`.`currency_rate` AS `currency_rate`, `cont`.`operator_game_code` AS `operator_game_code`, `cont`.`funnel_id` AS `funnel_id`, `cont`.`segment_name` AS `segment_name`, `cont`.`operator_game_name` AS `operator_game_name`, `cont`.`description` AS `description`, `cont`.`extra_fields` AS `extra_fields`, `cont`.`jackpot_game_name` AS `jackpot_game_name`, `cont`.`game_version` AS `game_version`, `cont`.`event_type` AS `event_type`, `cont`.`event` AS `event` FROM `contribution_0`.`cont_event` as `cont` WHERE ((`cont`.`game_id` = 'RUUvr1574773090344') AND (`cont`.`event_arrival_time` BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59')) ORDER BY 5 DESC LIMIT NULL OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)",
"alias":"remote_0",
"actual_row_count":{ "value":4800, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"actual_total_time":{ "value":0 },
"start_time":{ "value":0 },
"end_time":{ "value":34472 },
"inputs":[
{
"executor":"Project",
"out":[
{
"alias":"",
"projection":"cont.event_id"
},
{
"alias":"",
"projection":"cont.action"
},
{
"alias":"",
"projection":"cont.correlation_id"
},
{
"alias":"",
"projection":"cont.status"
},
{
"alias":"",
"projection":"cont.event_arrival_time"
},
{
"alias":"",
"projection":"cont.create_time"
},
{
"alias":"",
"projection":"cont.create_ts"
},
{
"alias":"",
"projection":"cont.operator_id"
},
{
"alias":"",
"projection":"cont.game_id"
},
{
"alias":"",
"projection":"cont.player_id"
},
{
"alias":"",
"projection":"cont.segment_code"
},
{
"alias":"",
"projection":"cont.bet_amount_original"
},
{
"alias":"",
"projection":"cont.bet_amount_converted"
},
{
"alias":"",
"projection":"cont.cont_amount_player"
},
{
"alias":"",
"projection":"cont.cont_amount_operator"
},
{
"alias":"",
"projection":"cont.cont_amount_total"
},
{
"alias":"",
"projection":"cont.operator_income"
},
{
"alias":"",
"projection":"cont.cont_amount_jackpot"
},
{
"alias":"",
"projection":"cont.original_currency"
},
{
"alias":"",
"projection":"cont.base_currency"
},
{
"alias":"",
"projection":"cont.currency_rate"
},
{
"alias":"",
"projection":"cont.operator_game_code"
},
{
"alias":"",
"projection":"cont.funnel_id"
},
{
"alias":"",
"projection":"cont.segment_name"
},
{
"alias":"",
"projection":"cont.operator_game_name"
},
{
"alias":"",
"projection":"cont.description"
},
{
"alias":"",
"projection":"cont.extra_fields"
},
{
"alias":"",
"projection":"cont.jackpot_game_name"
},
{
"alias":"",
"projection":"cont.game_version"
},
{
"alias":"",
"projection":"cont.event_type"
},
{
"alias":"",
"projection":"cont.event"
}
],
"subselects":[],
"actual_row_count":{ "value":4800, "avg":300.000000, "stddev":0.000000, "max":300, "maxPartition":0 },
"actual_total_time":{ "value":5, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"start_time":{ "value":33799, "avg":34093.812500, "stddev":0.000000, "max":34466, "maxPartition":6 },
"network_traffic":{ "value":9213646, "avg":575852.875000, "stddev":41.005907, "max":575938, "maxPartition":10 },
"network_time":{ "value":11, "avg":6.000000, "stddev":5.000000, "max":11, "maxPartition":8 },
"inputs":[
{
"executor":"TopSort",
"limit":"?",
"order":[
"cont.event_arrival_time DESC"
],
"actual_row_count":{ "value":4800, "avg":300.000000, "stddev":0.000000, "max":300, "maxPartition":0 },
"actual_total_time":{ "value":24, "avg":22.500000, "stddev":1.500000, "max":24, "maxPartition":8 },
"start_time":{ "value":621, "avg":2473.000000, "stddev":0.000000, "max":2828, "maxPartition":13 },
"memory_usage":{ "value":33554432, "avg":2097152.000000, "stddev":0.000000, "max":2097152, "maxPartition":0 },
"inputs":[
{
"executor":"Filter",
"condition":[
"cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?"
],
"subselects":[],
"actual_row_count":{ "value":110794, "avg":6924.625000, "stddev":79.359841, "max":7068, "maxPartition":14 },
"actual_total_time":{ "value":297, "avg":285.500000, "stddev":11.500000, "max":297, "maxPartition":8 },
"start_time":{ "value":2, "avg":4.937500, "stddev":0.000000, "max":16, "maxPartition":13 },
"inputs":[
{
"executor":"ColumnStoreScan",
"db":"contribution",
"table":"cont_event",
"alias":"cont",
"index":"KEY operator_id (event_arrival_time, action, operator_id, game_id, correlation_id) USING CLUSTERED COLUMNSTORE",
"storage":"columnar",
"est_table_rows":"258396693",
"est_filtered":"1",
"actual_row_count":{ "value":46819110, "avg":2926194.375000, "stddev":239207.291250, "max":3849633, "maxPartition":3 },
"actual_total_time":{ "value":34162, "avg":34029.500000, "stddev":132.500000, "max":34162, "maxPartition":0 },
"start_time":{ "value":1, "avg":3.687500, "stddev":0.000000, "max":15, "maxPartition":13 },
"memory_usage":{ "value":100663296, "avg":6291456.000000, "stddev":0.000000, "max":6291456, "maxPartition":0 },
"segments_scanned":{ "value":55, "avg":3.437500, "stddev":0.496078, "max":4, "maxPartition":2 },
"segments_skipped":{ "value":217, "avg":13.562500, "stddev":0.496078, "max":14, "maxPartition":0 },
"segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"segments_filter_encoded_data":{ "value":55, "avg":3.437500, "stddev":0.496078, "max":4, "maxPartition":2 },
"inputs":[]
}
]
}
]
}
]
}
]
}
]
}
],
"version":"2",
"info":{
"memsql_version":"6.8.11",
"memsql_version_hash":"e973c625ae6d372c2d41d39b19612202c244fd7a",
"num_online_leaves":"2",
"num_online_aggs":"1",
"context_database":"(null)"
}
}
additionally:
Dont you think I shall add event_arrival_time to that coulmnar index key as well? all our queries are based on date. wont it be better while sorting?
also attaching here my pref question to you from my prev reply:
I suggest sticking with keyless sharding for now.
regarding the shard-key. dont you think as best practice would be better to use the correlation_id (instead of default) as it has high cardinality ?
Thanks