Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was at an interview for a new job today and got asked a question regarding a SELECT query:

SELECT lastname FROM users WHERE name='John';

This SELECT query is to run over a million rows.

I was asked: How would you make that faster/more efficient?

I didn't have the answer, and some googling also didn't reveal anything to me.

I know the possibility of this question being closed is quite high, but I'd really like to know how you would make that query run faster. Any ideas?

share|improve this question
3  
The answer is always "It depends." because if you use this answer then you have free reign to ask additional questions about the structure of the database and gain knowledge about the best course of action to take. There is no 100% right answer so they just want to make sure that you are the type of person that is able to figure out issues rather than provide knee-jerk answers. – MonkeyZeus Feb 25 at 19:51
1  
@MonkeyZeus: That's what I thought. I did send him the answer with my assessment that I had to write :) – Albert Feb 29 at 7:36
up vote 14 down vote accepted

The table may have a million or a billion or a trillion rows. The answer is the same:

Add an appropriate index on the columns used in the WHERE (and other clauses).

In this case, a simple index on (name) would be enough. Assuming that the number of rows that match the condition name = 'John' is small, say 0.1%, then the optimizer will use the index to find the matching rows and then read only those rows from the table (this is done with some additional lookups), to find the lastname values.

With a very large table, this may not be fast enough. If the percent of matching rows is 0.1% with a billion rows, that means a million rows with name = 'John' and a million lookups on the table. We can then have instead a "covering" index - on (name, lastname) - so we don't have to do the additional lookups on the table. Only the index will be used and the million values will be found with a simple index seek (and partial scan).

Response to your comment

This is, as far as I can see, as simple as altering the table structure and just setting the name column to be an index. Correct?

Basically yes, with either ALTER TABLE ... ADD INDEX ... or CREATE INDEX ... The table structure doesn't really change, the index is an additional (physical) structure.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.