July 23rd, 2012, 04:56 AM
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!
July 23rd, 2012, 08:18 AM
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)
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.
July 23rd, 2012, 01:30 PM
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
July 23rd, 2012, 11:32 PM
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).
Originally Posted by icuras
Not much, because keys are just specialized indexes.
I totally agree with majlumbo that you need to learn to normalize your table.
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):
From the above query, you might want to
C.ID AS CustomerID
, C.Name AS CustomerName
, I.ID AS ItemID
, I.Name AS ItemName
Transactions T INNER JOIN Customers C ON T.Customer=C.ID
INNER JOIN Items I ON T.Item=I.ID
- 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.