SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    *********! *********!!! jackli's Avatar
    Join Date
    Sep 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making all varchar fields an index...

    suppose i have a table that can get potentially very large...

    fields:
    id -- int primary key, auto-increment
    name -- varchar (50)
    renamed -- varchar (26)

    the "name" and "renamed" fields will often be JOIN'ed with another table. Should I make both "name" and "renamed" into indices?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, you should

    however, which indexes? that would be an additional question

    1. name
    2. renamed
    3. name and renamed as separate indexes
    4. (name,renamed)
    5. (name,renamed) and renamed as separate indexes
    6. (renamed,name)
    7. (renamed,name) and name as separate indexes
    8. (name,renamed) and (renamed,name) as separate indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    *********! *********!!! jackli's Avatar
    Join Date
    Sep 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok... now indices are beginning to scare me...

    criteria for determination?

    ---

    also, just to make sure, creating an index out of a column (or two or some combination thereof) doesn't change the way it's fetched?

    that is, even if "name" has become an index, i can still reference column "name" as $arr['name'] (given that $arr is populated by $arr=mysql_fetch_array(...) )

  4. #4
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jackli View Post
    criteria for determination?
    Oftenly used queries.

    Quote Originally Posted by jackli View Post
    even if "name" has become an index, i can still reference column "name" as $arr['name'] (given that $arr is populated by $arr=mysql_fetch_array(...) )
    Yes.

  5. #5
    *********! *********!!! jackli's Avatar
    Join Date
    Sep 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how would making (renamed,name) as one index be different than making renamed and name individually as two indices...?

  6. #6
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    List them both in brackets instead of just one.

    Code:
    create
     index `renamed_name`
        on `table_name`
         ( `renamed`
         , `name`
         )

  7. #7
    *********! *********!!! jackli's Avatar
    Join Date
    Sep 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't mean syntax-wise... but i mean what is the theoretical difference of:

    index nr2 on (name,renamed)
    vs
    name and renamed each as separate incides

  8. #8
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a) name_renamed works for queries like:
    ... where name = 'something' and renamed = 'something'
    ... where name = 'something'

    b) name works for queries like:
    ... where name = 'something' and renamed = 'something' (less efficiently than (a))
    ... where name = 'something'
    record gets indexed faster on data changing than (a)

    c) renamed works for queries like:
    ... where renamed = 'something' and name = 'something' (less efficiently than (a))
    ... where renamed = 'something'
    record gets indexed faster on data changing than (a)

    Note that index is important not only for where clauses, but also for data ordering and grouping.

  9. #9
    *********! *********!!! jackli's Avatar
    Join Date
    Sep 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ... and index is also important for for join's. can you give examples for join's?

    http://www.sitepoint.com/print/optim...ql-application
    ^ according to this, mysql would create a sort of organized flatfile of named and renamed if i made (named,renamed) one index.

    (and yes... rtfm doesn't work well for me since the fm usually doesn't make things as clear as earl-grey did above; please bear with my questions... >.< )

  10. #10
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The ON clause will act much like the WHERE clause does.

    Especially for INNER JOINs, you can just rephrase an explicit
    join into an implicit one and you will understand what difference index makes:
    Code:
    select t1.f1, t1.f2, t2.f2
      from t1
    left outer
      join t2
        on t1.f1 = t2.f1
    can be rephrased as:
    Code:
    select t1.f1, t1.f2, t2.f2
      from t1, t2
     where t1.f1 = t2.f1

  11. #11
    *********! *********!!! jackli's Avatar
    Join Date
    Sep 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is making (renamed, name) into an index faster than making each "name" and "renamed" into individual indices?

    both name and renamed are often called

  12. #12
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    depends.

    which do you call the most?
    Code:
    select * from table where name = "****"
    or

    Code:
    select * from table where rename = "*****"

    remember indices will make searching FASTER. but if you have too many, inserting, deleting and updating will be slowed down.

    also, if the query uses wildcard characters in the front and the end:

    Code:
    select * from table where name = "%smith%"
    mysql will ignore the indices since it cant index the middle of a string.


    also #2, if your columns in the query is also the index, mysql will not even look in the table but use the index to return the results, making it EVEN faster.

    for example:

    Code:
    select name from table
    will be really quick.
    leo d.


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
  •