CODEWITHSUNDEEP
sqlsql-serverdatabase-indexes
OutOfBounds
OutOfBounds

Reputation: 25

Creating indexes based on specific SQL queries

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!

Upvotes: 0

Views: 265

Answers (1)

Fernando Col&#237;n Perusquia
Fernando Col&#237;n Perusquia

Reputation: 68

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.

Upvotes: 2

Related Questions