Indexed fields

Last updated
Save as PDF

Understand Index Fields

Indexed fields play a crucial role in optimizing data retrieval and search operations in databases.

What Indexed Fields Do?

An Indexed Field refers to a column in a table that has been strategically optimized to improve the efficiency of data retrieval and search operations.

When a field is indexed, the database engine creates a data structure known as an index. This index functions as a streamlined guide, facilitating swift access to relevant rows of data. In Data Query, leveraging indexed fields is pivotal for enhancing query performance, ensuring faster and more efficient data retrieval.

Effective reduction in table size:

Utilizing indexed fields can significantly reduce the size of tables scanned during query execution, enhancing the speed and efficiency of your queries.

Overcoming input row limitation:

Data Query Live imposes a limitation on the number of input records per table after filters have been applied, set at 10 million. However, utilizing queries with indexed fields helps reduce the number of rows scanned, thereby preventing input row limits from being reached.

Examples

These cases illustrate the impact of using indexed fields and optimized query conditions on performance, providing insights into improving query efficiency

The examples given in this section illustrate the impact of using indexed fields and optimized query conditions on performance, providing insights into improving query efficiency.

Query Limit Exceeded:

Attempting to count all records in the InvoiceItem table results in a failure due to exceeding the input row limit of 10,000,000.

SELECT count(*) FROM InvoiceItem;

Fails due to input row limit constraints.

Query Without Indexed Fields:

A query without explicitly specified indexed fields, but with a filter in the WHERE clause, avoids the input limit exception.

SELECT count(*) FROM InvoiceItem, Subscription WHERE InvoiceItem.chargenumber = 'C-04468289';

Completed in 11:02 minutes, scanning 14M rows at a rate of 21.2K rows/s.

Query with Indexed Fields in WHERE Clause:

Optimizing by using indexed fields in the WHERE clause, the query successfully passes the input limit constraint.

SELECT count(*) FROM InvoiceItem, Subscription WHERE InvoiceItem.subscriptionid ='2c92a0fe718678c901719fafc062349a';

Completed in 8:32 minutes, scanning 14M rows at a rate of 27.4K rows/s.

Further Optimized Query with Joins:

Achieving additional optimization by utilizing joins on indexed fields and adding specific conditions in the filter.

SELECT * FROM InvoiceItem JOIN Subscription ON InvoiceItem.subscriptionid = Subscription.id WHERE InvoiceItem.subscriptionid = '2c92a0fe718678c901719fafc062349a';

Completed successfully in 0.33 minutes.

When Input Limit is Reached:

Even with indexed field optimization, if the input row limit is still a concern, users can explore Zuora Warehouse. This efficient alternative provides a scalable solution for optimizing query performance, ensuring that users can unlock the full potential of their data analysis.

List of tables within Data Query (Live) and corresponding indexed fields

Account

Id
UpdatedDate
AccountNumber
Batch
BillCycleDay
BillToId
CommunicationProfileId
CrmId
Currency
DefaultPaymentMethodId
InvoiceTemplateId
Name
ParentId
SoldToId
Status

AccountingCode

Type
Id
UpdatedDate

AccountingPeriod

Id
UpdatedDate
EndDate
Name
StartDate

Amendment

Id
Name
Status
SubscriptionId
Type
UpdatedDate

ApplicationGroup

UpdatedDate
PaymentId
RefundId

AquaTaskLog

JobId
Id
UpdatedDate

Attachment

Id
UpdatedDate
AssociatedObjectType
AssociatedObjectId

Auditloginevent

Year
Month
Day

Auditsettingchangeevent

Year
Month
Day

Auditobjectchangeevent

Year
Month
Day

BillingRun

Id
CreatedById
UpdatedDate
BillingRunNumber
ExecutedDate
Status

ChargeContractualValue

RatePlanChargeId
SubscriptionId
UpdatedOn

ChargeMetrics

ChargeNumber
Currency
InvoiceOwnerAccountNumber
SubscriptionName
SubscriptionOwnerAccountNumber
AmendmentId
ProductId
ProductRatePlanId
ProductRatePlanChargeId
RatePlanChargeId
StartDate
EndDate
CreatedDate
UpdatedDate

Collections_CollectionsWindowInfo

Amount_Due
InCollections
LastOpenInvoice
CollectUserId
AccountId

Collections_Commentables

NotesId
CollectUserId

Collections_ConnectorExecutions

LockboxId

Collections_Groups

Name

Collections_LockboxPayment

LockboxRecordId

Collections_LockboxRecords

ConnectorExecutionId

Collections_Replies

NotesId
CollectUserid
CommentableId

Collections_Retryattempts

RetryCycleId
AttemptNumber
Success
CreatedDate

Collections_Retrycycles

InvoiceId
PaymentMethodId
DebitMemoId
AccountId
AttemptNumber
CustomerGroupId
NextAttempt
Currency
Processing
Status

Contact

Id
UpdatedDate
AccountId
Country
FirstName
PersonalEmail

ContactSnapshot

Id
UpdatedDate
ContactId

Country

CreditBalanceAdjustment

AccountId
AccountingPeriodId
Id
UpdatedDate

CreditMemo

MemoNumber
Status
InvoiceId
AccountId
Id
UpdatedDate

CreditMemoApplication

Id
UpdatedDate
EffectiveDate
AccountId
ApplicationGroupId

CreditMemoApplicationItem

EffectiveDate
ApplicationGroupId
Id
UpdatedDate

CreditMemoItem

SubscriptionId
InvoiceItemId
RatePlanChargeId
CreditMemoId
Id
UpdatedDate

CreditMemoPart

Id
UpdatedDate
AccountId
CreditMemoId

CreditMemoPartItem

Id
UpdatedDate
TaxationItemId
CreditMemoPartId
DebitMemoItemId
InvoiceItemId
DebitTaxationItemId
CreditMemoItemId
CreditTaxationItemId

CreditTaxationItem

CreditMemoItemId
TaxationItemId
Id
UpdatedDate

DebitMemo

MemoDate
MemoNumber
Status
InvoiceId
AccountId
Id
UpdatedDate

DebitMemoItem

SubscriptionId
InvoiceItemId
ProductRatePlanChargeId
RatePlanChargeId
DebitMemoId
Id
UpdatedDate

DebitTaxationItem

DebitMemoItemId
TaxationItemId
Id
UpdatedDate

DiscountAppliedMetrics

DiscountRatePlanChargeId
RatePlanChargeId
Id
UpdatedDate

DiscountApplyDetail

Id
UpdatedDate
RatePlanChargeId

DiscountContractualValue

SubscriptionId
RatePlanChargeId
AppliedToRatePlanChargeId
UpdatedOn

FXCustomRate

Id
UpdatedDate
CurrencyFrom
CurrencyTo
RateDate

Feature

Id
UpdatedDate
FeatureCode
Status

Invoice

AccountId
InvoiceDate
InvoiceNumber
SourceId
Status
Id
UpdatedDate

InvoiceAdjustment

AccountId
InvoiceId
AccountingCode
AdjustmentNumber
ReasonCode
Status
Id
UpdatedDate

InvoiceHistory

InvoiceId
Id

InvoiceItem

InvoiceId
RatePlanChargeId
AccountingCode
SubscriptionId
Id
UpdatedDate

InvoiceItemAdjustment

AccountId
AccountingCode
AdjustmentNumber
InvoiceId
SourceId
Status
Id
UpdatedDate

InvoicePayment

InvoiceId
PaymentId
Id
UpdatedDate

JournalEntry

AccountingPeriodId
Currency
Number
Status
Id
UpdatedDate

JournalEntryItem

Id
UpdatedDate

JournalRun

Number
Status
TargetEndDate
TargetStartDate
Id

MemoHistory

MemoId
Id

NonSubscriptionInvoiceItem

InvoiceId
RatePlanChargeId
AccountingCode
SubscriptionId
Id
UpdatedDate
BookingReference

NotificationHistoryCallout

Id
ObjectId
AccountId
CreateTime

NotificationHistoryEmail

Id
NotificationId
ObjectId
AccountId
EventName
CreatedOn
Status

NotificationHistoryEmailEvent

Id
AccountId
NotificationId
EmailHistoryId
EventTimestamp
EventType
EventSubtype
CreatedDate
UpdatedDate

OrderAction

Id
UpdatedDate
SubscriptionVersionAmendmentId
OrderId

OrderActionRatePlan

Id
OrderActionId
RatePlanId
UpdatedDate

OrderContact

Id
UpdatedDate
Country
FirstName

OrderElp

Id
InvoiceOwnerId
OrderItemId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderItem

Id
UpdatedDate
EndDate
StartDate
RatePlanChargeId
OrderActionId

OrderLineItem

Id
UpdatedDate
ItemName
ItemNumber
ItemState
ItemType
OrderId
ProductCode
ProductRatePlanChargeId
UOM
RelatedSubscriptionNumber

OrderMrr

Id
UpdatedDate
DiscountChargeId
InvoiceOwnerId
OrderItemId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderMrrDefault

Id
UpdatedDate
DiscountChargeId
InvoiceOwnerId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderQuantity

Id
UpdatedDate
InvoiceOwnerId
OrderItemId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderQuantityDefault

Id
UpdatedDate
InvoiceOwnerId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderTcb

Id
UpdatedDate
DiscountChargeId
InvoiceOwnerId
OrderItemId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderTcbDefault

Id
UpdatedDate
DiscountChargeId
InvoiceOwnerId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderTcv

Id
UpdatedDate
DiscountChargeId
InvoiceOwnerId
OrderItemId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

OrderTcvDefault

Id
UpdatedDate
DiscountChargeId
InvoiceOwnerId
SubscriptionOwnerId
RatePlanChargeId
OrderActionId

Orders

Id
UpdatedDate
OrderDate
OrderNumber
AccountId

Payment

AccountId
EffectiveDate
GatewayOrderId
GatewayState
PaymentMethodId
PaymentMethodSnapshotId
PaymentNumber
ReferenceId
Source
SourceName
Status
Id
UpdatedDate

PaymentApplication

EffectiveDate
AccountId
PaymentId
ApplicationGroupId
Id
UpdatedDate

PaymentApplicationItem

EffectiveDate
TaxationItemId
PaymentApplicationId
ApplicationGroupId
DebitTaxationItemId
Id
UpdatedDate

PaymentGatewayReconciliationEventLog

Id
GatewayReferenceId
PaymentReconciliationJobId

PaymentItem

Id
UpdatedDate
PaymentItemNumber
InvoiceItemId
InvoicePaymentId
PaymentId
TaxationItemId

PaymentMethod

AccountId
AchAccountName
AchBankName
AchCountry
Active
IsSystem
BusinessIdentificationCode
Country
CreditCardMaskNumber
PaypalBaid
Type
Id
UpdatedDate

PaymentMethodSnapshot

AccountId
Country
CreditCardCountry
PaymentMethodId
Id
UpdatedDate

PaymentMethodTransactionLog

Id
TransactionId
PaymentMethodId

PaymentPart

Id
UpdatedDate
PaymentId
InvoiceId
AccountId

PaymentPartItem

Id
UpdatedDate
DebitMemoItemId
DebitTaxationItemId
InvoiceItemId
PaymentPartId
TaxationItemId

PaymentReconciliationAttempt

Id
PaymentReconciliationJobId

PaymentReconciliationJob

Id
JobNumber
Status

PaymentReconciliationLog

Id
Status

PaymentRun

Id
UpdatedDate
PaymentRunNumber

PaymentTransactionLog

Id
CreatedDate
GatewayReasonCode
TransactionDate
TransactionId
PaymentId

ProcessedUsage

UsageId
BillingPeriodEndDate
BillingPeriodStartDate
Id
UpdatedDate

Product

Name
SKU
Id
UpdatedDate

ProductDiscountApplyDetail

Id
UpdatedDate
ProductRatePlanChargeId

ProductFeature

Id
UpdatedDate
FeatureId
ProductId

ProductRatePlan

ProductId
Name
Id
UpdatedDate

ProductRatePlanCharge

deleted
BillCycleDay
ChargeModel
Id
UpdatedDate

ProductRatePlanChargeTier

Id
UpdatedDate
ProductRatePlanChargeId

ProductRatePlanCurrency

IsActive
Currency
ProductRatePlanId
Id
UpdatedDate

Ramp

Id
OrderId
UpdatedDate

RampInterval

Id
RampId
UpdatedDate

RampIntervalDeltaMetrics

Id
ProductRatePlanChargeId
RampIntervalId

RampIntervalDeltaMrr

Id
RampIntervalDeltaMetricsId

RampIntervalDeltaQuantity

Id
RampIntervalDeltaMetricsId

RampIntervalMetrics

Id
ProductRatePlanChargeId
RampIntervalId
RatePlanChargeId

RampIntervalMrr

Id
RampIntervalMetricsId

RatePlan

AmendmentId
SubscriptionId
Id
UpdatedDate

RatePlanCharge

RatePlanId
OriginalId
Id
UpdatedDate

RatePlanChargeTier

Currency
Id
UpdatedDate

RealTimeRatingProcessedUsage

UsageId
BillingPeriodEndDate
BillingPeriodStartDate
Id
UpdatedDate

Refund

AccountId
PaymentMethodSnapshotId
PaymentMethodId
ReasonCode
RefundNumber
SourceType
Id
UpdatedDate

RefundApplication

AccountId
ApplicationGroupId
EffectiveDate
RefundId
Id
UpdatedDate

RefundApplicationItem

Id
UpdatedDate

RefundInvoicePayment

InvoicePaymentId
RefundId
Id
UpdatedDate

RefundItem

Id
UpdatedDate
RefundItemNumber
RefundInvoicePaymentId
PaymentItemId

RefundPart

Id
UpdatedDate
PaymentId
RefundId
CreditMemoId
AccountId

RefundPartItem

Id
UpdatedDate

RefundTransactionLog

Id
TransactionDate
TransactionId
RefundId

RevenueChargeSummary

AccountId
Number
Id
UpdatedDate

RevenueChargeSummaryItem

AccountingPeriodId
Amount
Id
UpdatedDate

RevenueEvent

Number
Id
UpdatedDate

RevenueEventCreditMemoItem

Number
Id
UpdatedDate

RevenueEventDebitMemoItem

Number
Id
UpdatedDate

RevenueEventInvoiceItem

Number
Id
UpdatedDate

RevenueEventInvoiceItemAdjustment

Number
Id
UpdatedDate

RevenueEventItem

AccountingPeriodId
Id
UpdatedDate

RevenueEventItemCreditMemoItem

AccountingPeriodId
Id
UpdatedDate

RevenueEventItemDebitMemoItem

AccountingPeriodId
Id
UpdatedDate

RevenueEventItemInvoiceItem

AccountingPeriodId
Id
UpdatedDate

RevenueEventItemInvoiceItemAdjustment

AccountingPeriodId
Id
UpdatedDate

RevenueEventType

Name
SystemId
Id
UpdatedDate

RevenueSchedule

BillingTransactionId
Number
RevenueScheduleDate
AccountId
Id
UpdatedDate

RevenueScheduleCreditMemoItem

CreditMemoItemId
Number
RevenueScheduleDate
Id
UpdatedDate

RevenueScheduleDebitMemoItem

DebitMemoItemId
Number
AccountId
Id
UpdatedDate

RevenueScheduleInvoiceItem

Number
InvoiceItemId
AccountId
Id
UpdatedDate

RevenueScheduleInvoiceItemAdjustment

Number
AccountId
InvoiceItemAdjustmentId
Id
UpdatedDate

RevenueScheduleItem

AccountingPeriodId
Id
UpdatedDate

RevenueScheduleItemCreditMemoItem

AccountingPeriodId
Id
UpdatedDate

RevenueScheduleItemDebitMemoItem

AccountingPeriodId
Id
UpdatedDate

RevenueScheduleItemInvoiceItem

AccountingPeriodId
Id
UpdatedDate

RevenueScheduleItemInvoiceItemAdjustment

AccountingPeriodId
Id
UpdatedDate

State

Subscription

Id
AccountId
ContractEffectiveDate
CreatorAccountId
CreatorInvoiceOwnerId
InvoiceOwnerId
Name
OriginalId
PreviousSubscriptionId
Status
SubscriptionVersionAmendmentId
TermStartDate
Version
UpdatedDate

SubscriptionProductFeature

Id
UpdatedDate
FeatureId
RatePlanId

TaxableItemSnapshot

Id
TaxableItemId

TaxationItem

Id
UpdatedDate
InvoiceItemId

UpdaterBatch

BatchId
InternalStatus
RequestId
Status
UpdaterAccountId
Id
UpdatedDate

UpdaterDetail

BillingAccountId
RecordId
Status
TransactionId
UpdaterBatchId
PaymentMethodId
Id
UpdatedDate

Usage

AccountId, UOM, StartDateTime (composite index)
UOM
Id
UpdatedDate
ImportId