What is faster?

EXAMPLE
----------
Table peoplesnames

id - bigint(100) - auto_increment
firstname - varchar(20)
lastname - varchar(20)
age - int(2)
otherdetails - varchar(20)

Indexes
id - UNIQUE
age INDEX

-----------

My database has 3 million records. I want to find a record where I know the ID number already.

My question is: Does the database need to search through each record until it finds the ID number that I am looking for?

SELECT id FROM peoplesnames WHERE id= '1234567';

Would it be quicker to use the index AGE as well to cut down on the ID numbers to look at?

SELECT id FROM peoplesnames WHERE age = '21' AND id= '1234567';


Thanks

 

id is already a UNIQUE key. There is no need to use any additional index. Though you can use EXPLAIN to compare both the queries.

 

Agreed. It would be a waste of time to use the age = in the where clause because of the unique id.

 

Thanks guys, but does the database need to look through each record to find the number even though its unique?

And is this the same for doing a delete, because I delete about 100,000 lines a day.

Funny about the explain, I just found out about that 2 days ago.

Cheers

 

You can find out this in EXPLAIN's output (column named 'rows').

For your convenience, MySQL does not need to look through all the records if the column is UNIQUE.

 

For an unique field, mysql has the fastest search

 

Thanks guys