Thread: Indexes help?

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    Hello.

    I need to speed up my MySQL Table. I do not understand indexes. I have a primary key that already checks to see if there are any duplicates, but I recently read that indexes can speed up the table. If I am only searching one row, should I add an index? How?

    Thanks!
    yoshi
    datera@datera.com http://www.datera.com
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    the judicious use of indexes can optimize your SELECT speeds greatly. On the other hand, overuse of indexes can use extra processor cycles without speeding up your queries. The key is relevancy.

    For example, let's say you have a table called 'users' containing a primary key column called 'user_id', then 'first_name', 'last_name', 'email_address', and 'password'.

    Of course you usually want to index your primary key, but since you do a lot of queries based on the user's last name, your next most likely place to put an index is last_name. This is especially relevant if you do many queries that for example show all users with last names beginning with "b", or something like that. You very rarely do searches based on the password column, because the user has already been identified before that point, and your script is just checking the password of the relevant record (based on user_id). Therefore, unless you had a special reason, indexing the 'password' column is not relevant.

    So, your table create query would look like:

    CREATE TABLE indextest(
    user_id INT not null , first_name VARCHAR(30) not null ,
    last_name VARCHAR (30) not null , email_address TINYTEXT not null ,
    password VARCHAR (12) not null ,
    PRIMARY KEY (user_id),
    INDEX (user_id, last_name),
    UNIQUE (user_id)
    )


    Now the good stuff:

    MySQL let's you index not just a full colunm but also just a prefix of the column (This applies only to CHAR and VARCHAR columns). So you could make the primary key of *just* the first character of the last name. That way, searches based on first letter of the last name are optimized, but no energy is spent further indexing that column.

    So:
    {table create, etc...}
    INDEX (user_id, last_name(1)),
    UNIQUE (user_id)
    )

    would index the first character of last_name. You could do last_name(2) for the first 2 characters, etc...

    In MySQL, you can index up to 16 columns in a table, so if your table has complex search needs, you might want to use several indexes. Most of the time it is pointless to index more than two or three columns, though. If you need more, perhaps your table should be split into multiple tables.

    There's a lot more to it than what I can describe above, so you should read: http://www.mysql.com/documentation/m...ce.html#IDX252

    [This message has been edited by rycamor (edited August 09, 2000).]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    rycamor,

    Thank you so much for the reply. I am using your advice right now.

    There are 7 columns in my table in all, but I am always searching in the integer column (there can be more than one row with the same integer). Should I index only that column? How does indexing improve the speed?

    Thank you again,
    yoshi
    datera@datera.com http://www.datera.com
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Yes, you should index the integer column, even if it contains duplicates.

    It's purely a logical thing: indexing a column will only improve the speed if you are using that column as a basis for your searches, such as
    SELECT * FROM mytable WHERE my_indexed_column = 34;

    or

    SELECT * FROM mytable WHERE my_indexed_column > 22 LIMIT 15;

    etc...

    Again, read the link I gave you above carefully, because there is much more to this for complicated searches, for example; you can tie indexes of different columns together for 'cross-optimization'.

    [This message has been edited by rycamor (edited August 10, 2000).]

Similar Threads

  1. partial indexes
    By Joeba in forum PostgreSQL Help
    Replies: 2
    Last Post: May 25th, 2004, 03:12 PM
  2. how to create indexes in a new tablespace
    By kollisri in forum Database Management
    Replies: 1
    Last Post: October 28th, 2003, 04:34 PM
  3. 2d array. indexes to pointers.
    By balance in forum C Programming
    Replies: 8
    Last Post: February 28th, 2003, 09:30 AM
  4. creating indexes?
    By cmck in forum MySQL Help
    Replies: 6
    Last Post: February 19th, 2002, 10:18 AM
  5. many indexes slow mysql table?
    By cmck in forum MySQL Help
    Replies: 3
    Last Post: February 17th, 2002, 03:06 AM

IMN logo majestic logo threadwatch logo seochat tools logo