SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: idxdisp

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Swansea, South Wales, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    idxdisp

    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
    signature

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's in a CREATE TABLE statement right? that's creating an index (key) named idxdisp on the threadid and dateline columns together.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Swansea, South Wales, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What exactly is an index anyways? I haven't come across this one yet!
    signature

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •