CODEWITHSUNDEEP
singlestore
QEternity
QEternity

Reputation: 21

Why does creating an index in Memsql take so long?

Creating an index on a distributed table of ~500m narrow rows on a 20c/40t 256gb server takes many hours and for the life of me I cannot understand why.

CREATE TABLE users_userlocation ( id bigint(20) unsigned NOT NULL, user_id mediumint(9) unsigned NOT NULL, lat float NOT NULL, lon float NOT NULL, speed decimal(4,2) NOT NULL, status_id tinyint(4) unsigned NOT NULL, date datetime NOT NULL, prev_date datetime DEFAULT NULL, next_date datetime DEFAULT NULL, point geographypoint DEFAULT null, /*!90618 SHARD */ KEY user_id (user_id), KEY date (date DESC,user_id), KEY point (point), KEY date2 (user_id,date DESC), KEY date3 (date,user_id) );

alter table users_userlocation add index date3 (date, user_id);

As of this post, the above has been running for 6.5 hours.

Upvotes: 0

Views: 495

Answers (1)

Adam Prout
Adam Prout

Reputation: 739

Index build is a reasonably slow operation in MemSQL. A conversion of around 10 to 20 thousand rows a second per core is typical. It depends on the characteristics of the table or index your adding (skinny rows on a table with fewer indexes will be faster). Index build should have minimal impact on your running workload (some CPU use). If you can share your SHOW CREATE TABLE and CREATE INDEX statement I can check if your seeing something abnormal.

Upvotes: 2

Related Questions