Lo peeps this is my first post to these forums, looks very active and I look forward to speeking with you guys. Anyway first question, i'm pretty new to PHP and MySQL ( about 3 weeks now ) but i'm learning fast, anyway I was studying the code of vBuleetin lite and I noticed a clause in one of the queries which I haven't come across yet. Can someone please tell me what the following means.
$query = "INDEX idxdisp(threadid,dateline)";
I'm v confused:confused:
that's in a CREATE TABLE statement right? that's creating an index (key) named idxdisp on the threadid and dateline columns together.
What exactly is an index anyways? I haven't come across this one yet!
basically it takes the contents of the column(s) you specify and sorts it (alphabetically or numerically) in another file. then when you do searching that involves that column (i.e. WHERE indexed_col = something) it can be found much quicker than looking through the whole table. they use different algorithms to find the row quickly. it works something like this: say you have 1,000,000 rows. if they are sorted and MySQL is looking for something that starts with the letter "e," where would be the best place to look in the rows? (assuming the values aren't exactly spaced evenly) look at the middle row and whatever its value is you can tell if you need to go backward or forward. and either way you've just eliminated 500,000 rows to look through b/c since they're sorted the value can't possibly be in the other direction. so say you had to go backwards more to find the "e," where would you look next? at the 250,000th row. see the pattern?
if you have 1,000 rows in a table the row will be found 100x faster with an index. w/o an index you have to look at 1,000 rows, w/ an index you only have to look at 10 wrong rows max.
try it yourself. ask someone to think of a number between 1 and 1,000 and have them tell you whether you need to go higher or lower. you'll guess the number in 11 tries max using the algorithm i mentioned. say the number i thought of was 1, you'd guess these numbers: 500, 250, 125, 75, 38, 19, 10, 5, 3, 2, 1. you're eliminating half of the remaining possibilities with each guess.
there's three types of indexes in MySQL:
PRIMARY KEY - must be unique values
UNIQUE - must be unique values
KEY - can contain non-unique rows. INDEX is a synonym for KEY
hope that helps.