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

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Delphi and MYSQL indexes


    Can someone explain to me when and how to use indexes? What is teh difference between that and keys? When do i use INDEXES?

    I have a table with 100 fields with probably 70000 records. Obviously certain select queries take alot of time. I only have 1 key in the database? How do i decide what to make an index. I know how to create it but how do i use this.

    Thanks in advance!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    352
    Rep Power
    7
    To start with, if you have a table with 100 fields, then more than likely, you have not normalized your data. Here's a link on how to go about doing so.

    As far as an index, an index is different from a primary key (although a database will automatically create a "unique index" based on the primary key). An index helps a database increase the performance of searches on the data held within the table, although it slows down writes to the table.

    For example, given a table T with columns such as
    T.id (primary key)
    T.Last_Name
    T.First_Name
    T.Middle_name
    T.Date_of_Birth

    By definition, the database will create an index on T.id since it is the primary key. Say you would like to run a select and sort it by name, then it (may) be advantageous to have an index defined on Last_name, First_name and Middle_name.

    Additionally, you may want to sort or group your output by age, so an index on the date_of_birth would help there.

    A final UNIQUE index defined on Last_name, First_Name, Middle_initial and Date_of_birth would help to ensure that you don't enter the same person twice (although I guess it could be possible to have two people with the same exact name born on the same date).
    Last edited by majlumbo; July 23rd, 2012 at 01:49 PM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    To add a little more:
    Indexes hold the fields specified in the index sorted in the order of those fields plus
    The Primary Key field (or in some DBs an internal unique RowId maintained by the DB).

    This allows the DB to search for values in the index quickly and thus improves the speed
    of WHERE clauses and also table JOINS (place indexes on Foreign Key columns that will be used frequently in joins).

    The effectiveness of an index that is not a unique index will depend upon the cardinality and distribution of the data in that column.
    Cardinality being a measure of how many different values are in the column.
    The smaller the number of different values, the less effective the index because once
    the SQL engine finds a match it will then have to walk through every matching value
    until it finds the one with the correct PK (or rowId).

    There is more to be said; but this is a forum reply, not an article.

    HTH - Clive
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    Originally Posted by icuras
    Can someone explain to me when and how to use indexes?
    You use indexes when you want to find something faster than without one. in SQL realm, indexes are used behind the screen. You usually don't get to choose what index to use and how to use them. The database engine do these for you. The only exception is when you are advance enough so you may want to include execution plan along with your queries (and not all db engine allow this).

    What is teh difference between that and keys?
    Not much, because keys are just specialized indexes.

    I have a table with 100 fields with probably 70000 records. Obviously certain select queries take alot of time. I only have 1 key in the database?
    I totally agree with majlumbo that you need to learn to normalize your table.

    How do i decide what to make an index. I know how to create it but how do i use this.
    The simple answer is that you want to create index for fields you frequently use in WHERE and JOIN clause of your queries. Take the following query for example (the query has not been tested):

    Code:
    SELECT 
      C.ID AS CustomerID
      , C.Name AS CustomerName
      , I.ID AS ItemID
      , I.Name AS ItemName
    FROM
      Transactions T INNER JOIN Customers C ON T.Customer=C.ID
      INNER JOIN Items I ON T.Item=I.ID
    WHERE
      C.Name='John Doe'
    From the above query, you might want to
    • index Customers table on ID and Name field (obviously you would already have primary key on its ID field).
    • index field ID of Items table (usually use primary key)
    • index the combination of Customer and Item fields in Transactions table.


    Note that this does not mean you must index ALL fields referenced in your WHERE and JOIN clause of your queries. Use this information as the base, and decide yourself which ones the best candidates for indexing.

    Special for Unique Index, it can be used to make sure you have only a single existence of a value in a field or combination of values accross certain fields.
    Last edited by Luthfi; July 23rd, 2012 at 11:37 PM.

IMN logo majestic logo threadwatch logo seochat tools logo