You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/01/06 12:14:05 UTC
[GitHub] [incubator-shardingsphere] alexkuku opened a new issue #3881: Is there any way optimizing "limit" in the following situation？
alexkuku opened a new issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881
## Is there any way optimizing "limit" in the following situation？
I have a table like this：
sign_time|other_fileds
---|---
I want to sharding this table by range strategy using sign_time field.There is a characteristic for the sharded tables is that the sign_time filed will absolutely bigger in the latter tables than before.
##
Suppose there are two physical tables of the record table:
***record_0: 2019's data***
sign_time|other_fileds
---|---
2019/01/01 12:23:21|
2019/04/01 11:23:21|
......|
2019/12/31 22:23:21|
***record_1: 2020's data***
sign_time|other_fileds
---|---
2020/01/01 12:23:21|
2020/04/01 11:23:21|
......|
2020/12/31 22:23:21|
```
SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,10;
```
If I want to run the sql above.This sql will be runing like:
```
1.SELECT sign_time, other_fileds FROM record_1 WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 0,100010;
2.SELECT sign_time, other_fileds FROM record_2 WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 0,100010;
3.Merge and get the result in (100000, 100010]
```
But there is a better way:
```
1.SELECT count(*) FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,10;
2.
if the count_result >= 10
return SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,10;
else
return
SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,count_result
+
SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 0,10-count_result;
```
Is there any way can I achieve the effect of the latter situation?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] alexkuku closed issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
alexkuku closed issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-571147160
This is a good idea, but it only can use without order by, is it?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-572192697
@alexkuku If this optimizing is efficient for your own system case, you can implement it in your project.
Have fun and keep moving.
Look forward to your next idea about ShardingSphere.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-571502531
the `order by` in SQL is up to user, how about is the `order by item` is not sharding key?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] alexkuku commented on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
alexkuku commented on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-571168302
Thanks for your attention!
In this situation, I think we can use order by on the sign_time field, because the sign_time filed is the sharding field and not absolutely ordered by time asc. But other fields, as you said, can not use order by.
This limit strategy are mainly used in log data,like record,attendance and so on,which can be easily range sharded by time dimension. Have I made myself clear?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] alexkuku opened a new issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
alexkuku opened a new issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881
## Is there any way optimizing "limit" in the following situation？
I have a table like this：
sign_time|other_fileds
---|---
I want to sharding this table by range strategy using sign_time field.There is a characteristic for the sharded tables is that the sign_time filed will absolutely bigger in the latter tables than before.
##
Suppose there are two physical tables of the record table:
***record_0: 2019's data***
sign_time|other_fileds
---|---
2019/01/01 12:23:21|
2019/04/01 11:23:21|
......|
2019/12/31 22:23:21|
***record_1: 2020's data***
sign_time|other_fileds
---|---
2020/01/01 12:23:21|
2020/04/01 11:23:21|
......|
2020/12/31 22:23:21|
```
SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,10;
```
If I want to run the sql above.This sql will be runing like:
```
1.SELECT sign_time, other_fileds FROM record_1 WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 0,100010;
2.SELECT sign_time, other_fileds FROM record_2 WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 0,100010;
3.Merge and get the result in (100000, 100010]
```
But there is a better way:
```
1.SELECT count(*) FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,10;
2.
if the count_result >= 10
return SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,10;
else
return
SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 100000,count_result
+
SELECT sign_time, other_fileds FROM record WHERE sign_time BETWEEN 2019/12/15 00:00:00 AND 2020/01/02 00:00:00 LIMIT 0,10-count_result;
```
Is there any way can I achieve the effect of the latter situation?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu edited a comment on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
terrymanu edited a comment on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-571502531
The `order by` in SQL is up to user, how about is the `order by item` is not sharding key?
The precondition of discussion is talking about the regular solution. The solution about `log data` is special situation which should not consider about a regular database middleware, it is not serve for `log data` only.
We won't talk about any special situation, ShardingSphere is just for regular SQL and provide a regular solution.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu edited a comment on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
terrymanu edited a comment on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-571502531
the `order by` in SQL is up to user, how about is the `order by item` is not sharding key?
We won't talk about any special situation, ShardingSphere is just for regular SQL and provide a regular solution.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-572056742
Maybe you can try to summary your idea and extract them as regular rules to implement the limit merge.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] alexkuku commented on issue #3881: Is there any way optimizing "limit" in the following situation？
Posted by GitBox <gi...@apache.org>.
alexkuku commented on issue #3881: Is there any way optimizing "limit" in the following situation？
URL: https://github.com/apache/incubator-shardingsphere/issues/3881#issuecomment-571514438
OK.Thank you!This is indeed a special situation.
It seems that I can only get that effect by optimizing my business code.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services