What a difference a MySQL Index made

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 under

S050 - MySQL - General
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [2861] MySQL and Java - connectivity past, present, and future thoughts - (2010-07-09)
  [2567] Extra MySQL course dates (2 day course, UK) - (2010-01-08)
  [2561] The future of MySQL - (2010-01-03)
  [2559] Moving the product forward - ours, and MySQL, Perl, PHP and Python too - (2010-01-01)
  [2426] Which version of MySQL am I running? - (2009-09-26)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [2134] Oracle take over Sun who had taken over MySQL - (2009-04-21)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)

S154 - MySQL - Designing an SQL Database System
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)

5607

Back to
How was my web site compromised?

Previous and next
or
Horse's mouth home

Forward to
Tuning httpd / the supermarket checkout comparison

Some other Articles

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 ...

4083 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Public Training Courses

Running regularly at our UK training Centre.
[Schedule] - [About] - [Book]

Like this? ??