Show slow performance response using order by

I have the following query on columnstore table type using cluster column index:

SELECT * FROM scheme.table cont WHERE game_id = ‘some-game-id’ AND event_arrival_time BETWEEN ‘2019-12-02 00:00:00’ AND ‘2019-12-31 23:59:59’ ORDER BY event_arrival_time

I get response time of almost 30 seconds

for the same query if I remove ORDER BY event_arrival_time i get the response in few seconds

This is the create table query:

CREATE TABLE cont_event ( event_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, action varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, correlation_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, status varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, event_arrival_time datetime DEFAULT NULL, create_time timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, create_ts bigint(20) DEFAULT NULL, operator_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, game_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, player_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, segment_code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, bet_amount_original decimal(15,4) DEFAULT NULL, bet_amount_converted decimal(15,4) DEFAULT NULL, cont_amount_player decimal(15,4) DEFAULT NULL, cont_amount_operator decimal(15,4) DEFAULT NULL, cont_amount_total decimal(15,4) DEFAULT NULL, operator_income decimal(15,4) DEFAULT NULL, cont_amount_jackpot decimal(15,4) DEFAULT NULL, original_currency varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, base_currency varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, currency_rate decimal(20,6) DEFAULT NULL, operator_game_code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, funnel_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, segment_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, operator_game_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, description varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, extra_fields longtext CHARACTER SET utf8 COLLATE utf8_general_ci, jackpot_game_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, game_version varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, event_type varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, event JSON COLLATE utf8_bin, KEY operator_id (event_arrival_time,action,operator_id,game_id,correlation_id) /*!90619 USING CLUSTERED COLUMNSTORE / /!90618 , SHARD KEY () / ) /!90621 AUTOSTATS_ENABLED=TRUE */

i do have cluster column index of:

event_arrival_time, action, operator_id, game_id, correlation_id

when doing memsql profile i can see the filter works fine(see attached):

this is an EXPLAIN log:

Top limit:[@@SESSION.sql_select_limit]

GatherMerge [remote_0.event_arrival_time] 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 = ‘RUUvr1574773090344’ AND cont.event_arrival_time >= ‘2019-12-02 00:00:00’ AND cont.event_arrival_time <= ‘2019-12-31 23:59:59’]

OrderedColumnStoreScan 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,153,049 est_filtered:1

iam not sure what am I missing? any suggestions for optimization?

Please post text profiles of the query with and without the order by.

My best guess is that your data is not well-sorted. This would be caused by the merger being behind (more information here: https://docs.memsql.com/v7.0/concepts/columnstore/#managing-columnstore-segments). optimize table cont_event full will fully merge all segments, creating a global sort order. That may help.

Hi @cwatts. by your request adding

Please post text profiles of the query with and without the order by.

Profile with order by:

Top limit:[@@SESSION.`sql_select_limit`] actual_rows: 300 exec_time: 0ms

GatherMerge [remote_0.event_arrival_time DESC] partitions:all alias:remote_0 actual_rows: 4,800 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:32.689

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...] actual_rows: 4,800 exec_time: 6ms start_time: [00:00:31.164, 00:00:32.680] network_traffic: 9,213.646484 KB network_time: 3ms

TopSort limit:[?] [cont.event_arrival_time DESC] actual_rows: 4,800 exec_time: 22ms start_time: [00:00:00.628, 00:00:01.785] memory_usage: 33,554.433594 KB

Filter [cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?] actual_rows: 110,794 exec_time: 294ms start_time: [00:00:00.001, 00:00:00.017]

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 actual_rows: 43,656,020 exec_time: 32,348ms start_time: [00:00:00.000, 00:00:00.013] memory_usage: 100,663.296875 KB segments_scanned: 55 segments_skipped: 217 segments_fully_contained: 0

Profile without order by:

Top limit:[@@SESSION.`sql_select_limit`] actual_rows: 300 exec_time: 0ms

Gather partitions:all alias:remote_0 actual_rows: 499 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:04.245

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...] actual_rows: 499 exec_time: 2ms start_time: [00:00:00.604, 00:00:01.850] network_time: 1ms

Top limit:[?] actual_rows: 501 exec_time: 0ms

Filter [cont.game_id = ? AND cont.event_arrival_time >= ? AND cont.event_arrival_time <= ?] actual_rows: 534 exec_time: 41ms start_time: [00:00:00.001, 00:00:00.027]

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,003,673 est_filtered:1 actual_rows: 7,129,836 exec_time: 4,235ms start_time: [00:00:00.000, 00:00:00.026] memory_usage: 100,663.296875 KB segments_scanned: 35 segments_skipped: 217 segments_fully_contained: 0

The explain for the query with order by in the OP contains an OrderedColumnStoreScan, but the profile just has a ColumnStoreScan. It’s probable that the query in the OP was different than the query you used to get the profile. Can you double-check that (and paste the exact query texts you used to get each profile for clarity)?

@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)"
    }
}

Hi Idan,
Here is what I think (I could be wrong). Your shard Key is random, so your data will be distributed randomly. I would have a shard key on game_id at least so all same game_id are grouped together on same partition. I would try with a shard key on game_id, event_arrival_time too to see if you get better performance. For your clustered column index, i would probably create it in the order:

game_id, event_arrival_time, action, operator_id, correlation_id

If your query is to get the fastest information about a game_id, you want to make sure the same values (game_id) are grouped together on same partition.

@idan, thanks for pasting the profiles. It appears that the runtime of both queries is about 11 seconds. Can you confirm this?

@franck.leveneur thanks for your response if I will use shard-key of gameId that means we wont have our transactions distributed right. as I understand shard-key should be set on high cardinality key’s (e.g our correlationId which is unique per transaction) we have like 100 games. each game can have millions of transactions while other wont. if I use shardkey i mean have an unbalanced cluster or perhaps I missing something?

Thank you,
Idan

Your right. now I tried it again and it shows 15 seconds. its not consisted but it is better then 30 seconds. I havnt done anything. can you explain what’s going on?:slight_smile: what should I do next in your opinion?
between I must say I tried something else: instead of doing select * I specified specific fields and it got better aswell (6 seconds)

Hope you could explain both things out?

Thank you again

Idan,
If you don’t have good data spread between game ID, then you’ll have data skewing in partitions. I do believe data is still distributed in different leaf nodes. If your query will also involve date, and the amount of data per day is similar, i would consider using date(event_arrival_time) as a shard key, and instead of using event_arrival_time between x and y, you can just use event_arrival_date=‘date’. You can still keep event_arrival_time and index it for faster sort and if you need a specific time slot.
I think your case is a good candidate to create a fact table about your event. I’m not sure if cont_event is your raw data, but it’s good practice for scalability and reporting speed to aggregate your data in fact and dim. You just need to define your grain properly.

Idan,
Yes Select * is usually not a good idea. You want to specify the column to return. I’m not sure what is stored inside your table. If your table has json data, it will slowdown your queries when included in Select. (especially if you return lost of data). Which points back to what I was saying in previous post. You should use your raw data and aggregate it for fast reporting capabilities. I’ve had Memsql tables with billions of rows aggregated return results in sub-seconds. Any query over 3 seconds in MemSQL is a good candidate for optimization.

It’s difficult for me to say why your issue no longer reproduces. One guess I have is that your original order by query was not DESC. You can try seeing if removing DESC in the order by reproduces your slowdown.

instead of doing select * I specified specific fields and it got better aswell (6 seconds)

In older releases, we sometimes unnecessarily materialize projected non-filter columns. If you upgrade to 7.0, I expect that the select * query will be about as fast as your query that projects specific fields.

Regarding shard key tuning, I don’t think this is the right path to take for improving this query’s latency. I suggest sticking with keyless sharding for now. You may see a benefit on this specific query by placing game_id first in your clustered columnstore key. Alternatively, upgrading to 7.0 will also allow you to create a hash index on game_id, which I suggest experimenting with.

@cwatts,

You can try seeing if removing DESC in the order by reproduces your slowdown.

I truly apologize for this inaccuracy it wasnt that I missed DESC it was the dates range which I changed. On the first example had much higher range in the BETWEEN condition(29 days) compared to the last one (1 day). Now that I switched it back to 29 days I can reproduce easily the issue and it shows again ~25 seconds

so thats the query with the order by:
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

and iam also adding here the corresponding PROFILE text json:

{
    "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"
                    ],
                    "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 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":21008 },
                    "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":4, "avg":3.500000, "stddev":0.500000, "max":4, "maxPartition":8 },
                            "start_time":{ "value":19776, "avg":20554.437500, "stddev":0.000000, "max":21003, "maxPartition":4 },
                            "network_traffic":{ "value":9215912, "avg":575994.500000, "stddev":68.802253, "max":576170, "maxPartition":6 },
                            "network_time":{ "value":19, "avg":10.000000, "stddev":9.000000, "max":19, "maxPartition":8 },
                            "inputs":[
                                {
                                    "executor":"Top",
                                    "limit":"?",
                                    "actual_row_count":{ "value":4800, "avg":300.000000, "stddev":0.000000, "max":300, "maxPartition":0 },
                                    "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":65914, "avg":4119.625000, "stddev":26.506780, "max":4189, "maxPartition":11 },
                                            "actual_total_time":{ "value":194, "avg":192.000000, "stddev":2.000000, "max":194, "maxPartition":8 },
                                            "start_time":{ "value":27, "avg":37.750000, "stddev":0.000000, "max":50, "maxPartition":15 },
                                            "inputs":[
                                                {
                                                    "executor":"OrderedColumnStoreScan",
                                                    "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":"259183530",
                                                    "est_filtered":"1",
                                                    "actual_row_count":{ "value":30011346, "avg":1875709.125000, "stddev":236285.090670, "max":2787664, "maxPartition":3 },
                                                    "actual_total_time":{ "value":20777, "avg":20671.500000, "stddev":105.500000, "max":20777, "maxPartition":0 },
                                                    "start_time":{ "value":26, "avg":36.750000, "stddev":0.000000, "max":49, "maxPartition":15 },
                                                    "memory_usage":{ "value":182452224, "avg":11403264.000000, "stddev":2695753.079351, "max":15728640, "maxPartition":2 },
                                                    "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":42, "avg":2.625000, "stddev":0.780625, "max":4, "maxPartition":2 },
                                                    "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)"
    }
}

btw: I havnt applied yet your first suggestion:
optimize table cont_event full

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 ?

Thank you

Can you run the query with and without order by, one after another, and again paste the profiles? It looks like the data may have changed: in the most recent profile, 65914 rows passed the filter, but in Post #5, only 405 rows passed the filter. Sorry for the trouble.

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

@franck.leveneur I was trying to browse the net for an example of how to use Fact and Dim methodologies. couldnt find single on except this theoretical post: https://www.memsql.com/blog/third-normal-form/

Do you have some technical ref (video/blog/etc…) where I can take an example for that?

Hi @cwatts, Did you had a chance to look on my last response? I added the traces which you requested

Thanks

The issue here involves the implicit LIMIT in your queries. You have the session variable sql_select_limit = 300, which is like appending LIMIT 300 to your queries. When there is no order by, we can stop the scan when we get 300 rows. However, in the presence of order by, we need to look at all rows, then sort and take the top 300. In the profile, you can see that the non-order-by query scans only 7M rows, but the order-by query scans 47M. This is the reason for the slowdown.

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 ?

Using high-cardinality columns for shard keys is good because it gives you even distribution of your data, yes. Keyless sharding also gives you even distribution of your data. There are other considerations to make when choosing a shard key, like making your common joins partition-local. See here: https://docs.memsql.com/v7.0/guides/development/development/optimizing-table-data-structures/#choosing-a-shard-key
In any case, the choice of shard key is not relevant to the latency of this query, which is why I recommended leaving it unchanged for this performance investigation.

I was trying to browse the net for an example of how to use Fact and Dim methodologies.

You’ll be more successful in your searches if you search for “fact and dimension tables” - “dim” is short for “dimension” here.

A clustered columnstore key of event_arrival_time, game_id, action, operator_id, correlation_id is pretty close to what you have now and should give you better segment elimination for this query.

In the profile, you can see that the non-order-by query scans only 7M rows, but the order-by query scans 47M. This is the reason for the slowdown

@cwatts Iam not sure regarding the solution. What do you actuall suggesting to boost that query?

You mentioned:

A clustered columnstore key of event_arrival_time, game_id, action, operator_id, correlation_id is pretty close to what you have now and should give you better segment elimination for this query.

Today we have:

event_arrival_time, action, operator_id, game_id, correlation_id

Isnt it almost the same?

btw: action, operatorId, gameId have a very small distinct values

Thanks,
Idan