Indexed fields
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
- Id
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
- Id
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
User
- Id