If you add an index to a column in a MySQL table, you provide a very quick way of looking up a row or set of rows based on the value in a certain column, but at the expense of additional disc space and a bit more internal management.
Here's an example in which I look up a record (by URL) in a table of around 15000 records, some of which are quite large.
mysql> select url, halflife from cn_content where url = '/net/recents.html';
+-------------------+----------+
| url | halflife |
+-------------------+----------+
| /net/recents.html | 2 |
+-------------------+----------+
1 row in set (0.30 sec)
That's running at around 3 requests per second, which as a part of our web site logging is a significant load. Let's create an index, stating that the URL will be unique in the first 100 characters:
mysql> create unique index pagename on cn_content (url(100));
Query OK, 15176 rows affected (1.80 sec)
Records: 15176 Duplicates: 0 Warnings: 0
mysql
This did not work for me first time; the were a couple of lines where the pagename was not unique in my table, and I had the "opportunity" to resolve the issues. Once sorted, my enquiry gave the same results (so it was plug - and - play compatible) but much faster:
mysql> select url, halflife from cn_content where url = '/net/recents.html';
+-------------------+----------+
| url | halflife |
+-------------------+----------+
| /net/recents.html | 2 |
+-------------------+----------+
1 row in set (0.02 sec)
That's up from 3 requests per second to 50 - and a huge improvement!
(written 2009-02-25)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S154 - MySQL - Designing an SQL Database System [59] MySQL - Pivot tables - (2004-09-22)
[361] Binary Large Objects or bars - (2005-06-27)
[375] Oops - I got my initial database design wrong - (2005-07-12)
[494] MySQL - a score of things to remember - (2005-11-12)
[515] MySQL - an FAQ - (2005-12-03)
[666] Database design - get it right from first principles - (2006-04-02)
[918] Databases needn't be frightening, hard or expensive - (2006-11-08)
[937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
[945] Code quality counts - (2006-11-26)
[1423] MySQL - table design and initial testing example - (2007-11-06)
[1575] Database design for a shopping application (MySQL) - (2008-03-15)
[1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
[2085] MySQL - licensing issues, even with using the name - (2009-03-16)
[2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
[2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
[3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
[3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
[3494] Databases - when to treat the rules as guidelines - (2011-10-23)
[4426] FileMaker Day to Unix Time conversion - (2015-02-15)
S050 - MySQL - General [2134] Oracle take over Sun who had taken over MySQL - (2009-04-21)
[2240] How do I query a database (MySQL)? - (2009-06-15)
[2426] Which version of MySQL am I running? - (2009-09-26)
[2559] Moving the product forward - ours, and MySQL, Perl, PHP and Python too - (2010-01-01)
[2561] The future of MySQL - (2010-01-03)
[2567] Extra MySQL course dates (2 day course, UK) - (2010-01-08)
[2861] MySQL and Java - connectivity past, present, and future thoughts - (2010-07-09)
Some other Articles
Train and Coach fares from London (and airports) to MelkshamWeb Site Loading - experiences and some solutions sharedEffect on server when memory runs out and swapping startsTuning httpd / the supermarket checkout comparisonWhat a difference a MySQL Index madeHow was my web site compromised?A Presentation about our company - web and PHPWhy the Pony Tail?Why Choose Well House Consultants for your course?Learning to program in PHP, Python, Java or Lua ...