Mixing
How to create index in servral sql condition?
Data is like starttime, endtime, id, a, b, c, d, e, f, g...
How to create index on clickhouse, most sql is as follows:
1.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and id=?
2.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=?
group by a,c
order by sum(f) desc
limit 20
3.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=?
limit 20
4.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=? and a=? and c=?
group by a,c
order by sum(f) desc
limit 20
5.select a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=? and d=? and e=?
order by a,d,e
limit 20
tips:
a) always have starttime, endtime
b) some SQL have an id to search small data, example 1. but others to search large data, example 2,3,4,5
clickhouse
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
add a comment |
Data is like starttime, endtime, id, a, b, c, d, e, f, g...
How to create index on clickhouse, most sql is as follows:
1.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and id=?
2.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=?
group by a,c
order by sum(f) desc
limit 20
3.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=?
limit 20
4.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=? and a=? and c=?
group by a,c
order by sum(f) desc
limit 20
5.select a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=? and d=? and e=?
order by a,d,e
limit 20
tips:
a) always have starttime, endtime
b) some SQL have an id to search small data, example 1. but others to search large data, example 2,3,4,5
clickhouse
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
Please format your question better. Putting everything in one line is not good.
– onetwo12
Nov 21 '18 at 11:28
Are those time ranges between starrtime and enddtime narrow (like minutes) or wide (days/weeks/month)?
– Ivan Blinkov
Dec 10 '18 at 8:01
add a comment |
Data is like starttime, endtime, id, a, b, c, d, e, f, g...
How to create index on clickhouse, most sql is as follows:
1.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and id=?
2.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=?
group by a,c
order by sum(f) desc
limit 20
3.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=?
limit 20
4.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=? and a=? and c=?
group by a,c
order by sum(f) desc
limit 20
5.select a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=? and d=? and e=?
order by a,d,e
limit 20
tips:
a) always have starttime, endtime
b) some SQL have an id to search small data, example 1. but others to search large data, example 2,3,4,5
clickhouse
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
Data is like starttime, endtime, id, a, b, c, d, e, f, g...
How to create index on clickhouse, most sql is as follows:
1.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and id=?
2.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=?
group by a,c
order by sum(f) desc
limit 20
3.select starttime,endtime,id,a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=?
limit 20
4.select a,c,sum(f),avg(g) from tbl1
where starttime>=? and endtime<=? and a=? and c=?
group by a,c
order by sum(f) desc
limit 20
5.select a,b,c,d,e,f,g from tbl1
where starttime>=? and endtime<=? and a=? and d=? and e=?
order by a,d,e
limit 20
tips:
a) always have starttime, endtime
b) some SQL have an id to search small data, example 1. but others to search large data, example 2,3,4,5
clickhouse
clickhouse
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
edited Nov 22 '18 at 1:14
HelloWorld001
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
asked Nov 21 '18 at 11:09
HelloWorld001HelloWorld001
11
11
Please format your question better. Putting everything in one line is not good.
– onetwo12
Nov 21 '18 at 11:28
Are those time ranges between starrtime and enddtime narrow (like minutes) or wide (days/weeks/month)?
– Ivan Blinkov
Dec 10 '18 at 8:01
add a comment |
Please format your question better. Putting everything in one line is not good.
– onetwo12
Nov 21 '18 at 11:28
Are those time ranges between starrtime and enddtime narrow (like minutes) or wide (days/weeks/month)?
– Ivan Blinkov
Dec 10 '18 at 8:01
Please format your question better. Putting everything in one line is not good.
– onetwo12
Nov 21 '18 at 11:28
Please format your question better. Putting everything in one line is not good.
– onetwo12
Nov 21 '18 at 11:28
Are those time ranges between starrtime and enddtime narrow (like minutes) or wide (days/weeks/month)?
– Ivan Blinkov
Dec 10 '18 at 8:01
Are those time ranges between starrtime and enddtime narrow (like minutes) or wide (days/weeks/month)?
– Ivan Blinkov
Dec 10 '18 at 8:01
add a comment |
1 Answer
1
active
oldest
votes
Clickhouse is a COLUMN oriented database with one Primary Key, he store each column on separate optimized "storage" which doesn't need secondary indexes
you can choose *MergeTree table engine with starttime and endtime in primary key
every kind of MergeTree engine describe here https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/
All your queries will run as fast as possible
answered Nov 23 '18 at 8:32
SlachSlach
658514
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410837%2fhow-to-create-index-in-servral-sql-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Clickhouse is a COLUMN oriented database with one Primary Key, he store each column on separate optimized "storage" which doesn't need secondary indexes
you can choose *MergeTree table engine with starttime and endtime in primary key
every kind of MergeTree engine describe here https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/
All your queries will run as fast as possible
answered Nov 23 '18 at 8:32
SlachSlach
658514
add a comment |
Clickhouse is a COLUMN oriented database with one Primary Key, he store each column on separate optimized "storage" which doesn't need secondary indexes
you can choose *MergeTree table engine with starttime and endtime in primary key
every kind of MergeTree engine describe here https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/
All your queries will run as fast as possible
answered Nov 23 '18 at 8:32
SlachSlach
658514
add a comment |
Clickhouse is a COLUMN oriented database with one Primary Key, he store each column on separate optimized "storage" which doesn't need secondary indexes
you can choose *MergeTree table engine with starttime and endtime in primary key
every kind of MergeTree engine describe here https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/
All your queries will run as fast as possible
answered Nov 23 '18 at 8:32
SlachSlach
658514
Clickhouse is a COLUMN oriented database with one Primary Key, he store each column on separate optimized "storage" which doesn't need secondary indexes
you can choose *MergeTree table engine with starttime and endtime in primary key
every kind of MergeTree engine describe here https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/
All your queries will run as fast as possible
answered Nov 23 '18 at 8:32
SlachSlach
658514
answered Nov 23 '18 at 8:32
SlachSlach
658514
answered Nov 23 '18 at 8:32
SlachSlach
658514
answered Nov 23 '18 at 8:32
SlachSlach
658514
658514
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410837%2fhow-to-create-index-in-servral-sql-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please format your question better. Putting everything in one line is not good.
– onetwo12
Nov 21 '18 at 11:28
Are those time ranges between starrtime and enddtime narrow (like minutes) or wide (days/weeks/month)?
– Ivan Blinkov
Dec 10 '18 at 8:01