MySQL Updating Index Statistics

The MySQL query optimizer uses two API calls to ask the storage engines how index values are distributed when deciding how to use indexes. The first is the records_in_ range( ) call, which accepts range end points and returns the (possibly estimated) number of records in that range. The second is info( ), which can return various types of data, including index cardinality (how many records there are for each key value).

When the storage engine doesn’t provide the optimizer with accurate information about the number of rows a query will examine, the optimizer uses the index statistics,which you can regenerate by running ANALYZE TABLE, to estimate the number of rows. MySQL’s optimizer is cost-based, and the main cost metric is how much data the query will access. If the statistics were never generated, or if they are out of date,the optimizer can make bad decisions. The solution is to run ANALYZE TABLE.

Each storage engine implements index statistics differently, so the frequency with which you’ll need to run ANALYZE TABLE differs, as does the cost of running the statement:

 - The Memory storage engine does not store index statistics at all.
 - MyISAM stores statistics on disk, and ANALYZE TABLE performs a full index scan to compute cardinality. The entire table is locked during this process.
 - InnoDB does not store statistics on disk, but rather estimates them with random index dives the first time a table is opened. ANALYZE TABLE uses random dives for InnoDB, so InnoDB statistics are less accurate, but they may not need manual updates unless you keep your server running for a very long time. Also, ANALYZE TABLE is nonblocking and relatively inexpensive in InnoDB, so you can update the statistics online without affecting the server much.

You can examine the cardinality of your indexes with the SHOW INDEX FROM command. For example:

mysql> SHOW INDEX FROM albums

*************************** 1. row ***************************
Table: albums
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 250
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

*************************** 2. row ***************************
Table: albums
Non_unique: 1
Key_name: reg_id
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 250
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

This command gives quite a lot of index information, which the MySQL manual explains in detail. We do want to call your attention to the Cardinality column,though. This shows how many distinct values the storage engine estimates are in the index. You can also get this data from the INFORMATION_SCHEMA.STATISTICS table in MySQL 5.0 and newer, which can be quite handy. For example, you can write queries against the INFORMATION_SCHEMA tables to find indexes with very low electivity.

A simple slideshow using mouse wheel

A JavaScript event for developers to take advantage of that- the "onmousewheel" event. As its name suggests, this event fires whenever the user rotate the mouse wheel either upwards or downwards, most commonly to scroll a webpage, "onmousewheel" is supported in IE6, Opera9+, Safari2+, and Firefox1+, though in FF (as of FF3.x), the equivalent "DOMMouseScroll" should be used, as "onmousewheel" as an event name isn't yet recognized.
Lets start a tour with onmusewheel event:

<img id="slideshow" src="1.jpg" />

<script type="text/javascript">
    var myimages=["1.jpg","2.jpg", "3.jpg"];
    var slideshow=document.getElementById("slideshow");
    var nextslideindex=0;

    function rotateimage(e)
   {
        //equalize event object
        var evt=window.event || e;
     
       //delta returns +120 when wheel is scrolled up, -120 when scrolled down
        var delta=evt.detail? evt.detail*(-120) : evt.wheelDelta;
       
       //move image index forward or back, depending on whether wheel is scrolled down or up
        nextslideindex = (delta<=-120)? nextslideindex+1 : nextslideindex-1;
       
       //wrap image index around when it goes beyond lower and upper boundaries
        nextslideindex = (nextslideindex<0)? myimages.length-1 : (nextslideindex>myimages.length-1)? 0 : nextslideindex;

        slideshow.src=myimages[nextslideindex];
        if (evt.preventDefault)
        {//disable default wheel action of scrolling page
            evt.preventDefault();
        }
       else
      {
         return false;
       }

   }

//FF doesn't recognize mousewheel as of FF3.x
var mousewheelevt=(/Firefox/i.test(navigator.userAgent))? "DOMMouseScroll" : "mousewheel";

if (slideshow.attachEvent) //if IE (and Opera depending on user setting)
    slideshow.attachEvent("on"+mousewheelevt, rotateimage)
else if (slideshow.addEventListener) //WC3 browsers
    slideshow.addEventListener(mousewheelevt, rotateimage, false)
</script>

Note the use of preventDefault() and return false to disable the default mouse wheel action when the cursor is over the image, which is to scroll the page.

Encryption algorithms

Encryption is the process of converting a plaintext message into ciphertext which can be decoded back into the original message. An encryption algorithm along with a key is used in the encryption and decryption of data. There are several types of data encryptions which form the basis of network security. Encryption schemes are based on block or stream ciphers.

The type and length of the keys utilized depend upon the encryption algorithm and the amount of security needed. In conventional symmetric encryption a single key is used. With this key, the sender can encrypt a message and a recipient can decrypt the message but the security of the key becomes problematic. In asymmetric encryption, the encryption key and the decryption key are different. One is a public key by which the sender can encrypt the message and the other is a private key by which a recipient can decrypt the message.

The main purpose of encryption algorithms is to provide the following:

Authentication - Proving one's identity before granting access.
Privacy and confidentiality - Ensuring that outsiders cannot read data intended for specific parties.
Integrity - Ensuring that the message has not be modified in any way before it arrives to the intended recipient.
Non-repudiation - Ensuring that a message is truly originated from the sender.

Asymmetric encryption algorithm:
These types of encryption algorithms involve a pair of relative keys that encode and decode messages. One key is used to encrypt data into ciphertext while the other key decrypts it back into plaintext. Asymmetric algorithms are more commonly known as Public-key cryptography, first introduced in 1978 with RSA encryption. These schemes work by multiplying two large prime numbers to generate a larger number that is incredibly difficult to revert to the original form.

Asymmetric algorithms tend to be slower than their symmetric counterparts. Because of this, they aren't recommended for encrypting large amounts of data. The biggest advantage to such a scheme lies in the utilization of two keys. Hence the name, the public key can be made publicly available, enabling anyone to encrypt private messages. However, the message can only be decrypted by the party that owns the relative private key. This type of encryption algorithm also provides proof of origin to ensure to overall integrity of communications.

Symmetric Algorithms:
Symmetric algorithms use a single key to encrypt and decrypt data. These encryption algorithms typically work fast and are well suited for encrypting blocks of messages at once. The most known example is the DEA (Data Encryption Algorithm) which is specified within the DES (Data Encryption Standard). Triple DES is a more reliable version while AES (Advanced Encryption Standard) has become new the government standard. Other popular symmetric algorithms include the Japanese developed FEAL and the more recently developed U.S. scheme known as SKIPJACK.

Hash Algorithms:
Hash algorithms function by transforming data of arbitrary length into a smaller fixed length, more commonly known as a message digest. These types of algorithms are considered one-way functions. The generated output varies, making them very efficient when it comes to detecting alterations that might have been made to a message. Hash algorithms are often generated by the DES algorithm to encrypt online banking transactions and other communications where messages can't afford to be corrupted.

Key Management:
One significant issue with traditional symmetric algorithms is the requirement to distribute keys to multiple users. This generally involves establishing a system where the keys are manually distributed and accounted for, resulting in more overhead from and administrative and physical security aspect. Additionally, the keys remain vulnerable to unauthorized disclosure and potential abuse. Asymmetric encryption algorithms tend to be easier to employ and manage and ultimately more secure.

Here are some details about some of these encryption.

RSA:
In 1977, shortly after the idea of a public key system was proposed, three mathematicians, Ron Rivest, Adi Shamir and Len Adleman gave a concrete example of how such a method could be implemented. To honour them, the method was referred to as the RSA Scheme. The system uses a private and a public key. To start two large prime numbers are selected and then multiplied together; n=p*q.

If we let f(n) = (p-1) (q-1), and e>1 such that GCD(e, f(n))=1. Here e will have a fairly large probability of being co-prime to f(n), if n is large enough and e will be part of the encryption key. If we solve the Linear Diophantine equation; ed congruent 1 (mod f(n)), for d. The pair of integers (e, n) are the public key and (d, n) form the private key. Encryption of M can be accomplished by the following expression; Me = qn + C where 0<= C < n. Decryption would be the inverse of the encryption and could be expressed as; Cd congruent R (mod n) where 0<= R < n. RSA is the most popular method for public key encryption and digital signatures today. 

DES/3DES:
The Data Encryption Standard (DES) was developed and endorsed by the U.S. government in 1977 as an official standard and forms the basis not only for the Automatic Teller Machines (ATM) PIN authentication but a variant is also utilized in UNIX password encryption. DES is a block cipher with 64-bit block size that uses 56-bit keys. Due to recent advances in computer technology, some experts no longer consider DES secure against all attacks; since then Triple-DES (3DES) has emerged as a stronger method. Using standard DES encryption, Triple-DES encrypts data three times and uses a different key for at least one of the three passes giving it a cumulative key size of 112-168 bits.

BLOWFISH:
Blowfish is a symmetric block cipher just like DES or IDEA. It takes a variable-length key, from 32 to 448 bits, making it ideal for both domestic and exportable use. Bruce Schneier designed Blowfish in 1993 as a fast, free alternative to the then existing encryption algorithms. Since then Blowfish has been analyzed considerably, and is gaining acceptance as a strong encryption algorithm.

IDEA:
International Data Encryption Algorithm (IDEA) is an algorithm that was developed by Dr. X. Lai and Prof. J. Massey in Switzerland in the early 1990s to replace the DES standard. It uses the same key for encryption and decryption, like DES operating on 8 bytes at a time. Unlike DES though it uses a 128 bit key. This key length makes it impossible to break by simply trying every key, and no other means of attack is known. It is a fast algorighm, and has also been implemented in hardware chipsets, making it even faster.

SEAL:
Rogaway and Coppersmith designed the Software-optimized Encryption Algorithm (SEAL) in 1993. It is a Stream-Cipher, i.e., data to be encrypted is continuously encrypted. Stream Ciphers are much faster than block ciphers (Blowfish, IDEA, DES) but have a longer initialization phase during which a large set of tables is done using the Secure Hash Algorithm. SEAL uses a 160 bit key for encryption and is considered very safe.

RC4:
RC4 is a cipher invented by Ron Rivest, co-inventor of the RSA Scheme. It is used in a number of commercial systems like Lotus Notes and Netscape. It is a cipher with a key size of up to 2048 bits (256 bytes), which on the brief examination given it over the past year or so seems to be a relatively fast and strong cypher. It creates a stream of random bytes and 'XORing' those bytes with the text. It is useful in situations in which a new key can be chosen for each message.

AES (Rijndael):
The algorithm was invented by Joan Daemen and Vincent Rijmen. The National Institute of Standards and Technology (http://www.nist.gov) has recently selected the algorithm as an Advanced Encryption Standard (AES).

The cipher has a variable block length and key length. Authors of the algorithm currently specify how to use keys with a length of 128, 192, or 256 bits to encrypt blocks with a length of 128 bits.

BestCrypt uses Rijndael with a 256-bit key in LRW mode.

To get more information on the algorithm, visit the Rijndael Home Page: http://www.esat.kuleuven.ac.be/~rijmen/rijndael/.

GOST 28147-89:
The Government Standard of the USSR 28147-89, Cryptographic protection for Data Protection Systems, appears to have played the role in the former Soviet Union (not only in Russia) similar to that played by the US Data Encryption Standard (FIPS 46). When issued, GOST bore the minimal classification 'For Official Use,' but is now said to be widely available in software both in the former Soviet Union and elsewhere. The introduction to GOST 28147-89 contains an intriguing remark that the cryptographic transformation algorithm "does not put any limitations on the secrecy level of the protected information."

The GOST 28147-89 standard includes output feedback and cipher feedback modes of operation, both limited to 64-bit blocks, and a mode for producing message authentication codes. Additional information about the GOST 28147-89 algorithm is also available at the Jetico Web site: http://www.jetico.com/gost.htm
BestCrypt uses GOST 28147-89 with 256-bit key in LRW mode.

RC-6:
RC6 block cipher was designed by Ron Rivest in collaboration with Matt Robshaw, Ray Sidney, and Yiqun Lisa Yin from RSA Laboratories. RSA's RC6 encryption algorithm was selected among the other finalists to become the new federal Advanced Encryption Standard (AES). Visit RSA Laboratories WWW-site (http://www.rsasecurity.com/rsalabs/node.asp?id=2512) to get more information on the algorithm.
BestCrypt uses the RC6 with 256-bit key and 128-bit blocks in LRW mode.

SERPENT:
Serpent is a block cipher developed by Ross Anderson, Eli Biham and Lars Knudsen. Serpent can work with different combinations of key lengths. Serpent was also selected among other five finalists to become the new federal Advanced Encryption Standard (AES).
BestCrypt uses Serpent in LRW mode with a 256-bit key, 128-bits blocks and 32 rounds.
Additional information about the Serpent algorithm is also available on World-Wide-Web from: http://www.cl.cam.ac.uk/~rja14/serpent.html

TWOFISH:
The Twofish encryption algorithm was designed by Bruce Schneier, John Kelsey, Chris Hall, Niels Ferguson, David Wagner and Doug Whiting.
Twofish is a symmetric block cipher; a single key is used for encryption and decryption. Twofish has a block size of 128 bits and accepts keys of any length up to 256 bits.
The National Institute of Standards and Technology (NIST) investigated Twofish as one of the candidates for the replacement of the DES encryption algorithm. As the authors of the algorithm state, "we have spent over one thousand hours cryptanalyzing Twofish, and have found no attacks that go anywhere near breaking the full 16-round version of the cipher".
BestCrypt uses a full 16-round version of Twofish and a maximum possible 256-bit encryption key length in LRW mode.
Additional information about the Twofish algorithm is available also on the World-Wide-Web from: http://www.counterpane.com/twofish.html