Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    1

    multiple column index

    Hi all,

    I have a qeury relating to multi column index.

    CREATE TABLE EMPLOYEE (ID INT, NAME TEXT);
    CREATE INDEX IDX ON EMPLOYEE(ID, NAME);
    SELECT * FROM EMPLOYEE WHERE ID > 10 AND NAME < "MAC"

    Does the above select statement use index IDX? If yes then how does comparison function evaluates the condition specified in WHERE clause?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    What database server are you using? SQL Server? Oracle? Something else?
    Indexes may be handled differently by different vendors, so post your question in the appropriate forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    sisharma

    Indexes are handled differently by each vendor, that's where a significant part of the competitive edge is implemented.

    However, indexing a TEXT column is a bit insane, IDX is 100% width (text column size) of the table; variable length and slow. Some vendors cannot index text columns at all. Make the indexed column CHAR(255) or something reasonable.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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