[SOLVED] Indexing

[play_]

I've never really had to consider indexing as sites ive made were always fairly small. But just in case something happens in the future, i'll ask now.

 

I always index the first column of every table, which is the ID, which auto_increments.

 

So i guess this makes it faster when I have a query such as

"select id, title, content from tbl where userID = 23"

 

Now, what if i just do

"select title from table where userID = 23" ?

 

Would the query be slower since I am not explicitly selecting the indexed column? or does mysql does that automatically?

 

Same question when selecting *:

"select * from table where userID = 23"

 

Any bit of info would be great

[corbin]

Indexes have nothing to do with the actual retrieval of data.  Sometimes I even have queries like this:

 

 

SELECT a.*, c.* FROM

a JOIN b ON b.col1 = a.col2

JOIN c ON c.col3 = a.col2

WHERE b.col4 = some value;

 

Indexes just come into play when searching a table.  An index basically works like this:

 

 

SELECT * FROM table WHERE username = 'Corbin';

 

Ok, look at all the username's that start with C.

Are there still some?  If not, return 0 rows.

Look at the subset of the C starting values and see which ones continue with o.

If none, return 0 rows.

Look at the subset of the Co values and look for r....

If none, return 0 rows.

 

 

So on...

 

 

MySQL usually uses b-trees which essentially make easy to follow branches.  The branches still have to be searched, but they can be searched in a systematic method and often times the "searching" is more of "check if rows exists in a specific area of the b-tree."

 

(If you think about why LIKE '%corbin' will never be indexed and you think about how b-trees work, you'll probably quickly see why 'corbin%' can be indexed and even 'cor%bin' to some extent, but never '%corbin'.)

 

 

 

 

 

Anyway, short answer:

 

 

All three of those queries would use the index.

 

 

Indexes simple map a value to a row number.  (Think of an index in the back of a book.  Thomas Jefferson    5, 73, 105.  Then I would look on pages 5, 73, and 105 for information on Thomas Jefferson.  Indexes work the same way.  "Oh, user_id 5 maps to row 3."  Then the columns you actually want will be extracted and returned.)

[play_]

Ahh that helps. thank you

[corbin]

No problem .

 

 

Edit: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

 

That might help some too.

[play_]

gotcha.

 

thanks again, ill give it a read