Wot diffrence b/w 'Index' and Foreign Key?

[MohsinAli]

Plz tell me briefly wot is the difference b/w 'Indexes' and 'Foreign Key'.

[peterlaursen]

Very briefly:

1) an index is a “sorted list” of fields from a table. It speeds up MySQL performance to have an index built that matches the searches (WHERE-expressions and ORDER BY-expressions for instance) you use often. If there is no index available for a certain query, MySQL must read all data – if there is an appropriate index, MySQL will only need to read the index.

There can be a lot of indexes with a table (the exact number depends on MySQL-version and storage engine).

2) a “unique index” is an index where each value in the index corresponds to exactly one row in the table.

3) a “Primary Key” is a special case of a unique index. There can only be one Primary Key. An autoincrement integer column is often used as a Primary Key. It's is almost always a good idea to have a Primary Key (speeds up certain MySQL internal functions)

4) there is another special case of indexes called “fulltext index'es”. They are only available with MyISAM tables. You use it for finding substrings of string variables using the SQL-operator MATCH. If you have a string in your DB somewhere like 'Bunny is my favorite pet” an appropriate fulltext index would let you find this string when MATCHing with 'bunny' or 'favorite'. It would even let you find it when MATCHing with “bonny” and “favourite”.

5) A foreign key is an index that uses data from another table. To use foreign keys you must use a storage engine that supports it. InnoDB storage engine is mostly used for this. MyISAM tables do not support FK's yet (it is planned for MySQL ver 5.1)

If you have to tables with content

table content

*******************'***

food (hamburger, pizza, beer, wine, tea, milk, cola, sandwich, beef, redfish)

categories (drinks, fastfood, meat, fish)

… you can use a Foreign Key to map

'hamburger','pizza' and 'sandwich' from table “food” to 'fastfood' in table “categories”

'beer',wine','tea',milk' and 'cola' from table “food” to “drink” in table “categories”

'beef' from table “food” to 'meat' in table “categories”

and finally 'redfish' from table “food” to 'fish' in table “categories”.

then you can use the “categories”-table to let MySQL search in the “food”-table

But there are books and internet resources abot all this. And the MySQL documentation not to forget!

[MohsinAli]

Thx a lot for giving me this brief and to the point explanation

[peterlaursen]

Just created this simple showcase for you to show an example of how you can use a FK (same data as above)

[peterlaursen]

to be honest …

you really don't need FK's for this example. There are other ways to do it. But using FK has some advantages

– for instance (if defined properly) it wont let you delete or change a category-item that is still in use by the other table. And you can define the FK ins such a way (using the CASCADE keyword) that a category-item is automatically deleted, when the last item referring to it is deleted (if you want that).

[peterlaursen]

BTW … this must be a bug (see image)

I tried to change 'deep ocean' to 'deep-deep-deep ocean

latest SQL from history pane:

select count(*) from `madvarer` where `mytext`='redfish' and `kategori`='fish' and `specifik beskrivelse`='Lives in deep-deep ocean'

It uses alias with SQL on line 1. The SQL on line 3 does not use an alias for that result-column and does not raise error.

It is not beacuase of the relationsship.

Problem is the same with resultset from this query:

select mytext as wetryagain from madvarer;

raises err.msg “unknown column 'wetryagain' in where clause when trying to edit.

[Ritesh]

Check http://www.webyog.com/forums/index.php for the bug-fix.

[peterlaursen]

@MohsinAli

Sorry .. I was mistaken on one point.

It is the opposite way around. You can CASCADE on UPDATE and DELETE with the PK definition. That means that you can change a category-name or delete a category and the referring values will be automatically updated or deleted.

[Author]

Posts