Varshini S
asked on
WHERE CLAUSE ORDER
I have the following table
Customer_sales
CompanyId int(2) Primary key
CustomerId char(20) Primary key
SaleDate DateTime
SalesAmount Decimal(12,2)
SalesStatus char(1)
This table has more than 8989998788 records
I am using the below where condition to fetch the records
select customerId , SaleDate,SalesAmount from Customer_sales
where CustomerId >'12345'
and CustomerId <'879999'
Should I have to include the CompanyId in the where condition since it is a composite primary key ? Will this improve the query performance ?
And, Does where clause in the same order how the composite primary key builded ?
(ie) where CompanyId =12 and CustomerId >'12345'
Customer_sales
CompanyId int(2) Primary key
CustomerId char(20) Primary key
SaleDate DateTime
SalesAmount Decimal(12,2)
SalesStatus char(1)
This table has more than 8989998788 records
I am using the below where condition to fetch the records
select customerId , SaleDate,SalesAmount from Customer_sales
where CustomerId >'12345'
and CustomerId <'879999'
Should I have to include the CompanyId in the where condition since it is a composite primary key ? Will this improve the query performance ?
And, Does where clause in the same order how the composite primary key builded ?
(ie) where CompanyId =12 and CustomerId >'12345'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have a bizarrely high number of records. I assume 9 trillion is an exaggeration.
If you have an excessive number of records, like a few hundred million or more, consider looking into partitioning the table on CompanyId.
If there are a lot of inserts and updates, be sure to schedule a regular check of the index and rebuild as needed.