@cwatts my bad. iam re-pasting:
I hope iam running your requests as expected (thats why i added the actually queries iam running on the editor
to be double sure ive also pasted 2 JSON files which I exported from the visual memsql screen
without order by query using profile:
query:
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';
SHOW PROFILE;
result:
Top limit:[@@SESSION.`sql_select_limit`]
Gather partitions:all alias:remote_0
Project [cont.event_id, cont.action, cont.correlation_id, cont.status, cont.event_arrival_time, cont.create_time, cont.create_ts, cont.operator_id, cont.game_id, cont.player_id, cont.segment_code, cont.bet_amount_original, cont.bet_amount_converted, cont.cont_amount_player, cont.cont_amount_operator, cont.cont_amount_total, cont.operator_income, cont.cont_amount_jackpot, cont.original_currency, cont.base_currency, cont.currency_rate, cont.operator_game_code, cont.funnel_id, cont.segment_name, cont.operat...]
Top limit:[?]
Filter [cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?]
ColumnStoreScan contribution.cont_event AS cont, KEY operator_id (event_arrival_time, action, operator_id, game_id, correlation_id) USING CLUSTERED COLUMNSTORE est_table_rows:256,0
with order by query using profile:
query:
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-03 23:59:59' ORDER BY event_arrival_time DESC
SHOW PROFILE;
result:
Top limit:[@@SESSION.`sql_select_limit`]
GatherMerge [remote_0.event_arrival_time DESC] partitions:all alias:remote_0
Project [cont.event_id, cont.action, cont.correlation_id, cont.status, cont.event_arrival_time, cont.create_time, cont.create_ts, cont.operator_id, cont.game_id, cont.player_id, cont.segment_code, cont.bet_amount_original, cont.bet_amount_converted, cont.cont_amount_player, cont.cont_amount_operator, cont.cont_amount_total, cont.operator_income, cont.cont_amount_jackpot, cont.original_currency, cont.base_currency, cont.currency_rate, cont.operator_game_code, cont.funnel_id, cont.segment_name, cont.operat...]
TopSort limit:[?] [cont.event_arrival_time DESC]
Filter [cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?]
ColumnStoreScan contribution.cont_event AS cont, KEY operator_id (event_arrival_time, action, operator_id, game_id, correlation_id) USING CLUSTERED COLUMNSTORE est_table_rows:255,995,247 est_filtered:1
without order-by query:
{
“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”:405, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“actual_total_time”:{ “value”:0 },
“start_time”:{ “value”:0 },
“end_time”:{ “value”:11308 },
“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”:405, “avg”:25.312500, “stddev”:3.215563, “max”:30, “maxPartition”:6 },
“actual_total_time”:{ “value”:2, “avg”:1.500000, “stddev”:0.500000, “max”:2, “maxPartition”:8 },
“start_time”:{ “value”:626, “avg”:1421.625000, “stddev”:0.000000, “max”:1696, “maxPartition”:1 },
“network_traffic”:{ “value”:197245, “avg”:49311.250000, “stddev”:6112.270829, “max”:57450, “maxPartition”:14 },
“network_time”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“inputs”:[
{
“executor”:“Top”,
“limit”:"?",
“actual_row_count”:{ “value”:405, “avg”:25.312500, “stddev”:3.215563, “max”:30, “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”:405, “avg”:25.312500, “stddev”:3.215563, “max”:30, “maxPartition”:6 },
“actual_total_time”:{ “value”:113, “avg”:91.500000, “stddev”:21.500000, “max”:113, “maxPartition”:0 },
“start_time”:{ “value”:1, “avg”:7.937500, “stddev”:0.000000, “max”:24, “maxPartition”:7 },
“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”:“256003673”,
“est_filtered”:“1”,
“actual_row_count”:{ “value”:17137298, “avg”:1071081.125000, “stddev”:250172.376141, “max”:2033123, “maxPartition”:3 },
“actual_total_time”:{ “value”:11253, “avg”:11231.500000, “stddev”:21.500000, “max”:11253, “maxPartition”:8 },
“start_time”:{ “value”:0, “avg”:7.000000, “stddev”:0.000000, “max”:23, “maxPartition”:7 },
“memory_usage”:{ “value”:72351744, “avg”:4521984.000000, “stddev”:1560527.621297, “max”:6291456, “maxPartition”:2 },
“segments_scanned”:{ “value”:23, “avg”:1.437500, “stddev”:0.496078, “max”:2, “maxPartition”:2 },
“segments_skipped”:{ “value”:249, “avg”:15.562500, “stddev”:0.496078, “max”:16, “maxPartition”:0 },
“segments_fully_contained”:{ “value”:0, “avg”:0.000000, “stddev”:0.000000, “max”:0, “maxPartition”:0 },
“segments_filter_encoded_data”:{ “value”:20, “avg”:1.250000, “stddev”:0.433013, “max”:2, “maxPartition”:3 },
“inputs”:[]
}
]
}
]
}
]
}
]
}
]
}
],
“version”:“2”,
“info”:{
“memsql_version”:“6.8.11”,
“memsql_version_hash”:“e973c625ae6d372c2d41d39b19612202c244fd7a”,
“num_online_leaves”:“2”,
“num_online_aggs”:“1”,
“context_database”:"(null)"
}
}
with order-by query:
{
"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-31 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":405, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"actual_total_time":{ "value":0 },
"start_time":{ "value":0 },
"end_time":{ "value":11779 },
"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":405, "avg":25.312500, "stddev":3.215563, "max":30, "maxPartition":6 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":10971, "avg":11397.062500, "stddev":0.000000, "max":11774, "maxPartition":7 },
"network_traffic":{ "value":775571, "avg":48473.187500, "stddev":6157.593779, "max":57450, "maxPartition":6 },
"network_time":{ "value":3, "avg":1.500000, "stddev":1.500000, "max":3, "maxPartition":0 },
"inputs":[
{
"executor":"TopSort",
"limit":"?",
"order":[
"cont.event_arrival_time DESC"
],
"actual_row_count":{ "value":405, "avg":25.312500, "stddev":3.215563, "max":30, "maxPartition":6 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":844, "avg":1430.500000, "stddev":0.000000, "max":1894, "maxPartition":15 },
"memory_usage":{ "value":2097152, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 },
"inputs":[
{
"executor":"Filter",
"condition":[
"cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?"
],
"subselects":[],
"actual_row_count":{ "value":405, "avg":25.312500, "stddev":3.215563, "max":30, "maxPartition":6 },
"actual_total_time":{ "value":115, "avg":114.000000, "stddev":1.000000, "max":115, "maxPartition":0 },
"start_time":{ "value":1, "avg":5.500000, "stddev":0.000000, "max":15, "maxPartition":8 },
"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":"255995247",
"est_filtered":"1",
"actual_row_count":{ "value":17617578, "avg":1101098.625000, "stddev":244644.544568, "max":2042192, "maxPartition":3 },
"actual_total_time":{ "value":11653, "avg":11540.000000, "stddev":113.000000, "max":11653, "maxPartition":0 },
"start_time":{ "value":0, "avg":2.375000, "stddev":0.000000, "max":8, "maxPartition":1 },
"memory_usage":{ "value":72351744, "avg":4521984.000000, "stddev":1560527.621297, "max":6291456, "maxPartition":2 },
"segments_scanned":{ "value":23, "avg":1.437500, "stddev":0.496078, "max":2, "maxPartition":2 },
"segments_skipped":{ "value":249, "avg":15.562500, "stddev":0.496078, "max":16, "maxPartition":0 },
"segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"segments_filter_encoded_data":{ "value":23, "avg":1.437500, "stddev":0.496078, "max":2, "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)"
}
}
what should I do next in your opinion?