Tuning - Table Design and Query
Posted by: Safin Ahmed
Date: July 11, 2007 04:34AM

Hi,

I'm building a web application and I need it to be fast (as lightning)
I have a table Users with the following fields (simplified version):
user_id INT;
username VARCHAR(40); (this is unique)
password CHAR(40);
confirmed BIT;

And when I try to login I have this
SELECT user_id FROM Users WHERE username = X and password = Y and confirmed = 1;

I have three set questions (with my own answers) :

I want to apply an index to make this query faster, should I apply an index to each field? Or an aggregated index to username,password and confirmed?
If username is unique, a index to username should probably be sufficient, right?

The confirmed field is a BIT, is this indexable or should I change it to INT (and use only 0 and 1), or to an ENUM (with 0 and 1 possible values) ? Which of this DataTypes will give me better indexable performance?
Assuming that 95% of user will have this value to 1, and index in this field isn't a very good idea, right?

As for the query, would it be better to keep the query as it is or change the order to SELECT user_id FROM Users WHERE confirmed = 1 and username = X and password = Y; ? Is the fact confirmed has only two values make it better to be the first condition? Or the fact username is unique make that better to be the first? (I think this will also depend on what I index, answer to the first question)
Assuming a unique username index the original query will do just fine, right?

Of course, I will make some tests to see what gives me the better result, I just want to know if I'm on the right track (so I can avoid testing all the thousands of possible indexes, and combinations), that's why I have my own anwsers :)

Thank you



Edited 3 time(s). Last edit at 07/11/2007 05:39AM by Safin Ahmed.

Options: ReplyQuote


Subject
Written By
Posted
Tuning - Table Design and Query
July 11, 2007 04:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.