cryptoe opened a new pull request, #19685:
URL: https://github.com/apache/druid/pull/19685
We create an index like this :
https://github.com/implydata/druid/blob/528e8079cdc92a1c98bc46ea8e0af30032018b3a/server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java#L397-L406
Updating query to match the index above.
Real world results on mysql 8.0.0:
### Before
```
mysql> explain SELECT start, end FROM druid_segments WHERE dataSource =
'xxxx' AND used = false GROUP BY start , end limit 100 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: druid_segments
partitions: NULL
type: ref
possible_keys:
idx_druid_segments_used,idx_druid_segments_datasource_used_end_start,idx_druid_segments_datasource_upgraded_from_segment_id
key: idx_druid_segments_used
key_len: 1
ref: const
rows: xxxx
filtered: 50.00
Extra: Using where; Using temporary
1 row in set, 1 warning (0.01 sec)
````
As you can see the index used was `idx_druid_segments_used`
### After
```
mysql> explain SELECT start, end FROM druid_segments WHERE dataSource =
'xxxx' AND used = false GROUP BY end , start limit 100 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: druid_segments
partitions: NULL
type: range
possible_keys:
idx_druid_segments_used,idx_druid_segments_datasource_used_end_start,idx_druid_segments_datasource_upgraded_from_segment_id
key: idx_druid_segments_datasource_used_end_start
key_len: xxx
ref: NULL
rows: xxx
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
```
As you can see the index used is
`idx_druid_segments_datasource_used_end_start`
Will send in another PR to adjust the limit to the intervals as well.
--
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.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
