Home arrow PHP arrow Page 7 - Changing Table Structure in phpMyAdmin

Table Optimization: EXPLAIN a Query - PHP

This chapter explores editing table definitions and using special column types. When developing Web applications (or any application), requirements often change because of new or modified needs. Developers must accommodate these changes through judicious table-structure editing. This is chapter six of Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035).

TABLE OF CONTENTS:
  1. Changing Table Structure in phpMyAdmin
  2. Editing Field Attributes
  3. BLOB (Binary Large Object)
  4. ENUM and SET
  5. Index Management
  6. Multi-Field Indexes and Index Editing
  7. Table Optimization: EXPLAIN a Query
By: Marc Delisle
Rating: starstarstarstarstar / 21
September 28, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 `books`
WHERE author_id = 2 AND language = 'es'

We want to know which books written by author 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. We enter this query in the query box and click Go. Whether the query finds any results is not important right now.

phpMyAdmin

Let's look at the header: SQL-query: [Edit] [Explain SQL] [Create PHP Code]

We will now use the [Explain SQL] link to get information about which index (if any) has been used for this query:

phpMyAdmin

We can see that the EXPLAIN command has been passed to MySQL, telling us that the possible_keys used is author_language. Thus, we know that this index will be used for this type of query. If this index did not exist, the result would have been quite different:

phpMyAdmin

Here, the possible_keys (NULL) and the type (ALL) mean that no index would be used, and that all rows would need to be examined 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 results page and comparing with or without the index:

phpMyAdmin

However, the difference in time can be minimal if we only have limited test data compared to a real table in production.

Summary

In this chapter we covered:

  • How to add fields, including special field types like TEXT, BLOB, ENUM, and SET
  • How to upload binary data into a BLOB field
  • How to manage indexes (multi-field and full-text)
  • How to get feedback from MySQL about which indexes are used in a specific query

phpMyAdminThis chapter is from Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035). Check it out at your favorite bookstore today.

Buy this book now.



 
 
>>> More PHP Articles          >>> More By Marc Delisle
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: