I have these two tables:
orders
order_id, customer_id, order_date, product_id, order_quantity
products
product_id, ProductName, SupplierID, UnitPrice, UnitsInStock
order_id and product_id are the primary keys.
I want to create indexes based on these queries which are executed regularly
SELECT *
FROM orders
WHERE product_id = @id
SELECT *
FROM orders
WHERE customer_id = @id AND order_date > @date
SELECT *
FROM orders
WHERE order_date > @date1 AND order_date < @date2
SELECT product_id, ProductName, UnitsInstock
FROM products
WHERE UnitsInstock < @units
SELECT *
FROM products
WHERE ProductName = @name AND SupplierID = @Id
I am confused on how to create these indexes since I don’t know exactly how the ‘@’ symbol works in SQL and how to implement it in a index with where clause statement. Any help will be appreciated!
>Solution :
The parameters got nothing to do with the indexes.
The indexes are based on the columns that you use more in the WHERE clause.
In the first table you use more:
product_id
customer_id
order_date
In the second table you use more:
UnitsInstock
ProductName
SupplierID
you can add an index on this columns but just remember that an index is good for querying the table but are bad for inserting and updating in the table (the insert/update is slower).
On other hand the @field are just parameters (you can think of it as variables)
In some other part of the script this parameters are getting the value.