I have a large sales transaction data and I want to create a table to store this data in GridDB and be able to quickly query it based on several criteria, including date, store location, product category, and payment method. Here's a CREATE TABLE statement:
CREATE TABLE sales_transactions (
transaction_id INT PRIMARY KEY,
transaction_date TIMESTAMP,
store_location VARCHAR(50),
product_category VARCHAR(50),
payment_method VARCHAR(50),
transaction_amount DECIMAL(10,2)
);
The table contains almost 5 million records and database is not responding when i am trying to retrieve data on several conditions in where clause the query is:
SELECT *
FROM sales_transactions
WHERE store_location = 'New York'
AND product_category = 'Electronics'
AND transaction_date BETWEEN '2022-01-01' AND '2022-01-31';
Please suggest any indexes which can help reduce the extraction time.
