SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to have more than 16 Columns Indexed ?

    Hi

    I have a table with about 65 fields, and out of 65 fields abut Half need INDEX as there are a lot of data, and we need to have search facility on those 30 fields.

    The data in the 28 fields (Varchar size 1) are like "1" and "0" and 2 fields are date field and I want to all these indexed.

    Please help me how to solve the issue.

    Thanks
    Zeeshan

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The maximum number of indexes per MyISAM table is 64. Which storage engine are you using?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by zeeshanhashmi View Post
    The data in the 28 fields (Varchar size 1) are like "1" and "0"
    this is a big problem, but easily solved

    since you did not share the table design with us, i'm gonna make up an example

    let's pretend that these columns are called something like this --
    likes_dogs, likes_kids, owns_car, owns_jet, previously_married, ...
    remove these columns from your table

    (indexing them wouldn't help your query performance anyway, because of high cardinalities)

    create a new table as follows:
    Code:
    CREATE TABLE properties
    ( main_id INTEGER NOT NULL 
    , property VARCHAR(99)
    , PRIMARY KEY ( main_id, property )
    , yes_or_no TINYINT
    );
    get the idea?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, yes i get the idea !

    But what if I index it ?
    There will be like 5000 Rows after 1 year. 10000 in 2 years, and then I can put a code in my PHP for Showing the current Records, and Showing the past Records. Will that be ok ?

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,799
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by zeeshanhashmi View Post
    There will be like 5000 Rows
    A few billion rows isn't a problem. Databases are designed to be able to handle lots of rows.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    A few billion rows isn't a problem. Databases are designed to be able to handle lots of rows.
    A few billion rows presents MANY problems! The table doesn't fit in memory anymore, may not even fit on disk anymore, maintaining indexes can become unacceptable overhead...

    Quote Originally Posted by zeeshanhashmi
    okay, yes i get the idea !

    But what if I index it ?
    Read Rudy's advice carefully as it's important. If most of your columns are just 0/1 values, then an index is not likely to help (and unlikely to even be used), but a better database design may.
    Last edited by Dan Grossman; Jan 6, 2010 at 01:41.


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
  •