When developing an application, requirements about the data structure often change because of new or modified needs. Developers must accommodate these changes through judicious table structure editing. This section explores the subject of changing the structure of tables. Specifically, it shows how to add a column to an existing table and how to edit the attributes of a column. We then build on these notions to introduce more specialized column types, and to explain their handling through phpMyAdmin. Finally, we cover the topic of index management.
Suppose that we need a new field to store a book's language and, by default, the books on which we keep data are written in English. We can call the field language, which will contain code composed of two characters (en by default).
In the Structure subpage of the Table view for the book table, we can find the Add field dialog. Here, we specify how many new fields we want, and where they will go.
The positions of the new fields in the table matter only from a developer's point of view. We usually group the fields logically, so that we can find them more easily in the list of fields. The exact position of the fields will not play a role in the intended results (the output from queries), as these results can be adjusted regardless of the table structure. Usually, the most important fields (including the keys) are located at the beginning of the table. However, it's a matter of personal preference.
We want to put the new field At End of Table. So we select the corresponding radio button and click on Go:
Other possible choices would be At Beginning of Table and After (where we would have to choose, from the drop-down menu, the field after which the new field must go).
We see the familiar panel for the new fields, repeated for the number of fields asked for. We fill it in. However, as we want to enter a default value this time, we perform the following two actions:
- Change the Default drop-down menu from None to As defined:
- Enter a default value, en
We then click on Save:
The previous panel appeared in vertical mode because the default for $cfg['DefaultPropDisplay'] is 3. This means that for three columns or less, the vertical mode is used, and for more than three, horizontal mode would automatically be selected. Here, we can use a number of our choice.
If we set $cfg['DefaultPropDisplay'] to'vertical', the panel to add new fields (along with the panel to edit a field's structure) will always be presented in vertical order. The advantages of working in vertical mode become obvious, especially when there are more choices for each field, as explained in Chapter 16,Transforming Data Using MIME.
On the Structure subpage, we can make further changes to our table. For this example, we set $cfg['PropertiesIconic'] to'both', to see the icons along with their text explanation:
This panel does not allow every possible change to fields. It specifically allows:
- Changing one field's structure, using the Change link on a specific field
- Removing a field, using the Drop operation
- Adding a field to an existing Primary key
- Setting a field as a non-unique Index or a Unique index
- Setting a Fulltext index (offered only if the field type allows it)
These are quick links that may be useful in some situations, but they do not replace the full index management panel. Both of these are explained in this chapter.
We can use the checkboxes to choose fields. Then, with the appropriate With selected icons, we can edit the fields or perform a multiple field deletion with Drop. The Check All / Uncheck All option permits us to easily select or deselect all checkboxes.
We will now explore how to use the TEXT field type, and the relevant configuration values to adjust for the best possible phpMyAdmin behavior. First, we add to the book table a TEXT field called description.
There are three parameters that control the layout of the text area that will be displayed in Insert or Edit mode for TEXT fields. First, the number of columns and rows for each field is defined by:
$cfg['TextareaCols'] = 40; $cfg['TextareaRows'] = 15;
This gives (by default) the following space within which to work on a TEXT field:
The settings impose only a visual limit on the text area, and a vertical scroll bar is created by the browser if necessary.
Note
Although MEDIUMTEXT, TEXT, and LONGTEXT columns can accommodate more than 32 KB of data, some browsers cannot always edit them with the text area—the mechanism offered by HTML. In fact, experimentation has convinced the phpMyAdmin development team to have the product display a warning message if the contents are larger than 32 KB. The message warns users that the contents may not be editable.
The last parameter has an impact for LONGTEXT fields only. The default value of TRUE for $cfg['LongtextDoubleTextarea'] doubles the available editing space.
BLOB fields are generally used to hold binary data (such as images and sounds), even though the MySQL documentation implies that TEXT fields could be used for this purpose. The MySQL 5.1 manual says:
In some cases, it may be desirable to store binary data such as media files in BLOB or TEXT columns.
However, there is another phrase:
BLOB columns are treated as binary strings (byte strings).
This phrase seems to indicate that binary data should really be stored in BLOB fields. Thus, phpMyAdmin's intention is to work with BLOB fields to hold all binary data.
We will see in Chapter 16, Transforming Data Using MIME that there are special mechanisms available to go further with BLOB fields, including being able to view some images directly from within phpMyAdmin. Furthermore, Chapter 17, Supporting MySQL 5.0 and 5.1 covers BLOB streaming support.
First, we add a BLOB field named cover_photo to our book table. If we now browse the table, we can see the field length information, [BLOB 0 B], for each BLOB field:
This is because the Show BLOB display option (do you remember the Options slider?) has no check mark by default. So it blocks the display of BLOB contents in Browse mode. This behavior is intentional. Usually, we cannot do anything with binary data that is represented in plain text.
If we edit one row, we see the Binary—do not edit warning and a Browse… button. The exact caption on this button depends on the browser used. Even though editing is not allowed, we can easily upload a text or binary file's contents into this BLOB column.
Let's choose an image file using the Browse—button for example, the logo_left.png file in a test copy of the phpMyAdmin/themes/original/img directory located on our client workstation. If we are seeing UNHEX in the function field, we should remove this choice as this is a shortcoming of some phpMyAdmin versions. We then click on Go.
We need to keep in mind some limits for the upload size. Firstly, the BLOB field size is limited to 64 KB, but in Chapter 16, Transforming Data Using MIME, we will change the type of this field to accommodate bigger images. Hence, phpMyAdmin reminds us of this limit with the Max: 64 KiB warning. Also, there could be limits inherent to PHP itself (see Chapter 7, Importing Data and Structure, for more details). We have now uploaded an image inside this field for a specific row:
If we put a check mark for the Show BLOB Contents display option, we now see the following in the BLOB field:
Note
To really see the image from within phpMyAdmin, refer to Chapter 16,Transforming Data Using MIME
The $cfg['ProtectBinary'] parameter controls what can be done when editing binary fields (BLOBs and any other field with the binary attribute). The default value blob blocks BLOB fields from being edited, but allows us to edit other fields marked as binary by MySQL. A value of all would block even binary fields from being edited. A value of FALSE would protect nothing, thus allowing us to edit all fields. If we try the last choice, we see the following in the Edit panel for this row:
The content of this BLOB field has been converted to hexadecimal form and the UNHEX function is selected by default. We probably don't want to edit this image data in hexadecimal form, but this is the best way of safely representing binary data on screen. The reason for this hexadecimal representation is that the Show binary contents as HEX display option (in Browse mode) is currently selected. But we did not select this option; it was checked because the $cfg['DisplayBinaryAsHex'] directive is TRUE by default.
Should we, instead, decide to not select this option, we would see the following pure binary data for this image:
There are chances that this is not our favorite image editor! In fact, data may be corrupted even if we save this row without touching the BLOB field. But the need to set $cfg['ProtectBinary'] to FALSE exists, as some users put text in their BLOB fields, and they need to be able to modify this text.
MySQL BLOB data types are actually similar to their corresponding TEXT data types. However, we should keep in mind that a BLOB has no character set, whereas a TEXT column has a character set that impacts sorting and comparison. This is why phpMyAdmin can be configured to allow the editing of BLOB fields.
Both the ENUM and SET field types are intended to represent a list of possible values. The difference is that the user can choose only one value from a defined list of values with ENUM, and more than one value with SET. With SET, all of the multiple values go into one cell; but multiple values do not imply the creation of more than one row of data.
We add a field named genre to the book table and define it as an ENUM. For now, we choose to put short codes in the value list and make one of them, F, into the default value, as shown in the following screenshot:
In the value list, we have to enclose each value within single quotes, unlike in the default value field. In our design, we know that these values stand for Fantasy, Child, and Novel. However, for now, we want to see the interface's behavior with short code. In the Insert panel, we now see a radio-box interface:
If we decide to have more self-descriptive code, we can go back to Structure mode and change the values definition for the genre field. We also have to change the default value to one of the possible values, to avoid getting an error message when trying to save this field structure's modification:
With the modified value list, the Insert panel now looks as follows:
Note that the radio buttons have been replaced by a drop-down list because the possible values are longer.
If we want more than one possible value selected, we have to change the field type to SET. The same value list may be used. However, using our browser's multiple value selector (Ctrl + click on a Windows or Linux desktop, Command + click on a Mac), we can select more than one value, as shown in the following screenshot:
We could use a normal character field to store date or time information. But DATE, DATETIME, and TIMESTAMP are more efficient for this purpose. MySQL checks the contents to ensure valid date and time information, and offers special functions to work with these fields.
As an added benefit, phpMyAdmin offers a calendar pop up for easy data entry.
We will start by adding a DATE field—date_published—to our book table. If we go into Insert mode, we should now see the new field where we can type a date. A Calendar icon is also available:
Clicking on this icon brings up a pop-up window, synchronized to this DATE field. If there is already a value in the field, the pop up is displayed accordingly. In our case, there is no value in the field, so the calendar shows the current date:
Small symbols on each side of the month and year headers permit easy scrolling through months and years. A simple click on the date we want transports it to our date_published field.
For a DATETIME or TIMESTAMP field, the pop up offers the ability to edit the time part.
Starting with MySQL 4.1.2, there are more options that can affect a TIMESTAMP column. Let's add to our book table, a column named stamp of type TIMESTAMP. In the Default drop-down menu, we could choose CURRENT_TIMESTAMP; but we won't for this exercise. However in the Attributes column, we choose on update CURRENT_TIMESTAMP:
MySQL 5.0.3 introduced true bit-field values. These take the same amount of space in the database as the number of bits in their definition. Let's say we have three pieces of information about each book, and each piece can only be true (1) or false (0):
- Book is hardcover
- Book contains a CD-ROM
- Book is available only in electronic format
We'll use a single BIT field to store these three pieces of information. Therefore, we add a field having a length of 3 (which means three bits) to the book table:
To construct and subsequently interpret the values we store in this field, we have to think in binary, respecting the position of each bit within the field. To indicate that a book is hardcover, does not contain a CD-ROM, and is available only in electronic format, we would use a value of 101.
phpMyAdmin handles BIT fields in a binary way. For example, if we edit one row and set a value of 101 in the some_bits column, the following query is sent at the time of saving:
UPDATE `marc_book`.`book` SET `some_bits` = b '101'
WHERE `book`.`isbn` = '1-234567-89-0' LIMIT 1;
The highlighted part of this query shows that the column really receives a binary value. At browse time, the exact field value (which in decimal equals 5—a meaningless value for our purposes) is redisplayed in its binary form, 101, which helps us to to interpret each discrete bit value.
Properly maintained indexes are crucial for data retrieval speed. phpMyAdmin has a number of index management options, which will be covered in this section.
We have already seen how the Structure panel offers a quick way to create an index on a single field, thanks to some quick links such as Primary, Index, and Unique. Under the field list, there is a section of the interface available for managing indexes:
This section has links to edit or delete every index. Here, the Field part lists only one field per index, and we can see that the whole field participates in the index. This is because there is no size information after each field name, contrary to what can be seen in our next example.
We will now add an index on the title. However, we want to restrict the length of this index to reduce the space used by the on-disk index structure. The Create an index on 1 columns option is appropriate. So we click on Go. On the next screen, we specify the index details, as shown here:
Here is how to fill in this panel:
- Index name: A name we invent, that describes the purpose of this index
- Index type: We can choose INDEX
- Field: We select the field that is used as the index, which is the title field
- Size: We enter 30 instead of 100 (which is the complete length of the field) to save space in the table's physical portion that holds index data
After saving this panel, we can confirm from the following screenshot that the index is created and does not cover the entire length of the title field:
In the next example, we assume that in a future application we will need to find the books written by a specific author in a specific language. It makes sense to expand our author_id index, adding the language field to it.
We click on the Edit link (the small pencil icon) on the line containing the author_id index; this shows the current state of this index. The interface has room to add another field to this index. We could use the Add to index 1 column(s) feature should we need to add more than one field. In the field selector, we select language. This time we do not have to enter a size, as the whole field will be used in the index. For better documentation, we change the Index name (author_language is appropriate):
We save this index modification. In the list of indexes, we can confirm our index modification:
This special type of index allows for full text searches. It's supported only on MyISAM tables for VARCHAR and TEXT fields. We can use the Fulltext quick link in the fields list, or go to the index management panel and choose Fulltext from the drop-down menu:
We add a FULLTEXT index on the description field, so that we are able to locate a book from words present in its description.
In this section, we want to get some information about the index that MySQL uses for a specific query, and the performance impact of not having defined an index.
Let's assume we want to use the following query:
SELECT * FROM `book` WHERE author_id = 2 AND language = 'es'
We want to know which books written by the author whose id is 2, are in the es language—our code for Spanish.
To enter this query, we use the SQL link from the database or the table menu, or the SQL query window (see Chapter 11, Entering SQL Commands). We enter this query in the query box and click on Go. Whether the query finds any results is not important right now.
Note
You could obtain the same query by following the explanations in Chapter 8, Searching Data to produce a search for author_id 2 and language es.
Let's look at the links: [Edit], [Explain SQL], [Create PHP Code], and [Refresh].
We will now use the [Explain SQL] link to get information about which index (if any) has been used for this query:
We can see that the EXPLAIN command has been passed to MySQL, telling us that the key used is author_language. Thus, we know that this index will be used for this type of query. If this index had not existed, the result would have been quite different:
Here, key (NULL) and the type (ALL) mean that no index would be used, and all rows would need to be examined in order to find the desired data. Depending on the total number of rows, this could have a serious impact on the performance. We can ascertain the exact impact by examining the query timing that phpMyAdmin displays on each result page (Query took x sec), and comparing it with or without the index. However, the difference in time can be minimal if we only have limited test data, compared to a real table in production. For more details about the EXPLAIN output format, please refer to http://dev.mysql.com/doc/refman/5.1/en/explain-output.html.
To help users maintain an optimal index strategy, phpMyAdmin tries to detect some common index problems. For example, let's access the book table and add an index on the isbn column. When we display this table's structure, we get a warning:
The intention here is to warn us about an inefficient index structure when considering the whole table. We don't need to have two indexes on the same column.