Database: Help me with correct index(es) for a table and query

Question

Hi,

I am running some queries in my database and want to increase the performance and have created some indexes but I still think that the response time is to great so I want to see if I can create a better or another index to increase the speed.

My schema for the table I think has the biggest bottleneck look like this:

R_D(  **id** int not null,  **SDD** date not null,  numa int,   numt int,   price decimal,  
FOREIGN KEY (room_type_id) REFERENCES R_T (id)

What is interesting is the SDD attribute which contains dates (e.g. 2010-05-20) and in my query I make a range search like this: SDD >= '2010-05-03' and SDD < '2010-05-08'

The index I have, which indeed increase the performance, is

INDEX sdd ON R_D (SDD, numa, numt, price, id)

The problem is when I make a range search with a great distance like 2010-05-03 and 2010-06-04 it takes about 6-10 seconds to perform the query and I would really like to tune it.

I tried several indexes and even a cluster index on SDD, but the best result I’ve got is so far the index above.

Any advice would really be appreciated.

Sincerely

Mestika

Answer 1

What happens if you just change the query to

SELECT * FROM YourTable where SDD >= '2010-05-03' and SDD < '2010-05-08'

This will tell you if it's the date restriction that is causing slowdown or something else. Also, be sure that the database is interpreting "2010-05-08" as a date, and not a string. Some dbs have a special syntax for dates, you could try

SELECT * FROM YourTable where SDD >= DATE('2010-05-03') and SDD < DATE('2010-05-08')