If you lead with timestamp key, you might want to consider experimenting with salting <http://phoenix.apache.org/salted.html> as writing data would hotspot on single region if keys are monotonically increasing.
On Tue, Oct 4, 2016 at 8:04 AM, Ciureanu Constantin < [email protected]> wrote: > select * from metric_table where metric_type='x' > -- so far so good > > and timestamp > 'start_date' and timestamp < 'end_date'. > -- here in case the timestamp is long (BIGINT in Phoenix) - it should work > fine! > Try also with "timestamp between (x and y)" > > Anyway - my proposal would be to reverse the key parts - have timestamp > first, then metric type, then other parts in the key. > > Using the timestamp it would define the start+stop of the scan range > (that's a must, step 1) - then it would filter locally the metric type with > Skips when it's not the searched value then some other parts of the key > with lower importance (if any of them are part of the where clause). > > Note: This new key proposal would solve your current use-case / but > wouldn't be perfect for potential new use-case - then you would need > indexes or duplicated data in other tables ... > > 2016-10-04 6:03 GMT+02:00 Krishna <[email protected]>: > >> You have two options: >> - Modify your primary key to include metric_type & timestamp as leading >> columns. >> - Create an index on metric_type & timestamp >> >> On Monday, October 3, 2016, Kanagha <[email protected]> wrote: >> >>> Sorry for the confusion. >>> >>> metric_type, >>> timestamp, >>> metricId is defined as the primary key via Phoenix for metric_table. >>> >>> Thanks >>> >>> Kanagha >>> >>> On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister < >>> [email protected]> wrote: >>> >>>> > >>>> >>>> there is no indexing available on this table yet. >>>> >>>> > >>>> >>>> >>>> >>>> So you haven’t defined a primary key constraint? Can you share your >>>> table creation DDL? >>>> >>>> >>>> >>>> Michael McAllister >>>> >>>> Staff Data Warehouse Engineer | Decision Systems >>>> >>>> [email protected] | C: 512.423.7447 | skype: >>>> michael.mcallister.ha | webex: https://h.a/mikewebex >>>> >>>> This electronic communication (including any attachment) is >>>> confidential. If you are not an intended recipient of this communication, >>>> please be advised that any disclosure, dissemination, distribution, copying >>>> or other use of this communication or any attachment is strictly >>>> prohibited. If you have received this communication in error, please >>>> notify the sender immediately by reply e-mail and promptly destroy all >>>> electronic and printed copies of this communication and any attachment. >>>> >>>> >>>> >>>> *From: *Kanagha <[email protected]> >>>> *Reply-To: *"[email protected]" <[email protected]> >>>> *Date: *Monday, October 3, 2016 at 5:32 PM >>>> *To: *"[email protected]" <[email protected]>, " >>>> [email protected]" <[email protected]> >>>> *Subject: *Re: Question regarding designing row keys >>>> >>>> >>>> >>>> there is no indexing available on this table yet. >>>> >>> >>> >
