CODEWITHSUNDEEP
singlestore
rayman
rayman

Reputation: 21616

memsql show slow performance response using order by

I have the following query:

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 index on: event_arrival_time, action, operator_id, game_id, correlation_id

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

enter image description here

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

Upvotes: 1

Views: 277

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522030

Here is your first query repeated:

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

The index you have currently defined looks either wrong to me, or at least sub-optimal. Try the following index instead:

CREATE INDEX idx ON cont (event_arrival_time, game_id);

Ideally, MemSQL would be able to scan the above index, in the order of the event arrival time, in the range you defined, and retrieve all matching records.

Upvotes: 0

Related Questions