SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A question about indexes

    Hi,

    What is the best way to create indexes? ...
    I have a large table with approx 50 columns, 10+ Million Records

    I have a few queries that I will be running on the table, for example

    Code MySQL:
    select col1, col2, col3 ....
    from mytable
    inner join othertable on
    mytable.field=othertable.field
    where col1=something, col7=something
    group by col1, col2
    for a query like this, would you create a seperate index for each column you are calling in select portion?
    or a seperate index for each column you are calling in the where clause
    or a sepeate index for each column in the group by
    or an index on multiple columns together (col1, col2, col3, col7)

    Any feedback is appritiated.
    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chemicaluser View Post
    What is the best way to create indexes? ...
    with the CREATE INDEX statement

    Quote Originally Posted by chemicaluser View Post
    for a query like this, would you create a seperate index for each column you are calling in select portion?
    columns in the SELECT list do not require indexing

    Quote Originally Posted by chemicaluser View Post
    or a seperate index for each column you are calling in the where clause
    a compound index if possible

    any further advice dependent on actual tables and columns, not made up ones
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks


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
  •