Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
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 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)


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 ...
4759 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, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 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

© WELL HOUSE CONSULTANTS LTD., 2021: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/2053_Wha ... -made.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb