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)

 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)

Back to How was my web site compromised?

Previous and next or Horse's mouth home

Forward to Tuning httpd / the supermarket checkout comparison

Train and Coach fares from London (and airports) to Melksham
Web Site Loading - experiences and some solutions shared
Effect on server when memory runs out and swapping starts
Tuning httpd / the supermarket checkout comparison
What a difference a MySQL Index made
How was my web site compromised?
A Presentation about our company - web and PHP
Why the Pony Tail?
Why Choose Well House Consultants for your course?
Learning to program in PHP, Python, Java or Lua ...