SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    325
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trying to make a column index

    i dont know if im even going about this the right way, but heres what i am trying to do:

    mysql> CREATE TABLE tbl_articles (
    -> articleID INTEGER AUTO_INCREMENT PRIMARY KEY,
    -> issue VARCHAR (20),
    -> authorID INTEGER INDEX,
    -> catID INTEGER INDEX,
    -> subcat VARCHAR (50),
    -> title VARCHAR (50),
    -> subhead1 VARCHAR (100),
    -> subhead2 VARCHAR (100),
    -> specs TEXT,
    -> body TEXT
    -> );

    but then i get this:

    ERROR 1064: You have an error in your SQL syntax near 'INDEX,
    catID INTEGER INDEX,
    subcat VARCHAR (50),
    title VARCHAR (50),
    subhead1 VA' at line 4


    Obviously i want to use the authorID and catID fields as an index. What am I doing wrong?
    From here on, it's instinctual...even straight roads meander.

  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)
    yeah, you can't specify INDEX in the column's attributes (only works with PRIMARY KEY). 2 other things: you should make all of your columns NOT NULL, unless you need to store NULL values, which i doubt. and you may want to make your INT columns UNSIGNED if you aren't going to store any negative values. here's how i'd write the CREATE TABLE statement:

    Code:
    CREATE TABLE tbl_articles ( 
    articleID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    issue VARCHAR (20) NOT NULL, 
    authorID INT UNSIGNED NOT NULL, 
    catID INT UNSIGNED NOT NULL, 
    subcat VARCHAR (50) NOT NULL, 
    title VARCHAR (50) NOT NULL, 
    subhead1 VARCHAR (100) NOT NULL, 
    subhead2 VARCHAR (100) NOT NULL, 
    specs TEXT NOT NULL, 
    body TEXT NOT NULL,
    INDEX (authorID),
    INDEX (catID)
    );
    i don't know if you meant that you want the indexes like that, or together as a single index? if you want them together, replace the 2 INDEX() lines with INDEX (authorID, catID).
    - 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 Addict
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    325
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah, i see, thank you very much for the quick reply. The way you have it is perfect. would would be the advantage of combining the two index columns?
    From here on, it's instinctual...even straight roads meander.

  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)
    there wouldn't necessarily be an advantage. it depends on what queries you're using. if you're searching for values in just the authorID column (or using it for a join), or just the catID, then the way you have it is best. however, if you're searching for values in both authorID and catID in the same query all the time, then you may get a benefit from making a multi-column index across both of them.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    325
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i gotcha...no the way i have it now is fine. thanks again fo ryour help. I'm sure you'll be seing a lot fo me around here...
    From here on, it's instinctual...even straight roads meander.


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
  •