Solved

MySQL PHP Speed Improvement (Bad DB design?)

Posted on 2011-02-18
13
1,273 Views
Last Modified: 2012-05-11
I have been asked to look at the improving the "speed" performance of a PHP page. The page in question lists the 200 most recent orders. It was taking about 40 seconds to run the script, which is just plain too long.

What I have found so far:

1. The page defaults to 200 orders (I will change this to 50 or 100 when I get FTP access).

2. The structure has 2 important tables (orders and order_products). "orders" has a Primany Key. "order_products" has no keys at all.

3. The orders table had 23199 rows. I removed lots of old orders to reduce this to 9078. This improved load time to 15 to 20 seconds.

4. The table definitions are:

# Table "orders_products" DDL

CREATE TABLE `orders_products` (
  `orderId` int(11) NOT NULL default '0',
  `productOptionId` int(11) NOT NULL default '0',
  `colourId` int(11) NOT NULL default '0',
  `cost` float NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1


# Table "orders" DDL

CREATE TABLE `orders` (
  `orderId` int(11) NOT NULL auto_increment,
  `statusId` int(11) NOT NULL default '0',
  `deliveryId` int(11) NOT NULL default '0',
  `notes` text NOT NULL,
  `specialInstructions` text NOT NULL,
  `createdOn` datetime NOT NULL default '0000-00-00 00:00:00',
  `createdBy` varchar(100) NOT NULL default '',
  `modifiedOn` datetime NOT NULL default '0000-00-00 00:00:00',
  `modifiedBy` varchar(100) NOT NULL default '',
  `totalOrderAmount` float NOT NULL default '0',
  `deliveryMethodId` int(11) NOT NULL,
  PRIMARY KEY  (`orderId`)
) ENGINE=MyISAM AUTO_INCREMENT=25879 DEFAULT CHARSET=latin1

Open in new window


So, to the REAL question:

How can I improve speed, with minimal changes to the PHP code (there is tons of it). My best guess is that I need to get some sort of key on the order_products table.

GH
0
Comment
Question by:G_H
  • 6
  • 5
  • 2
13 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
Comment Utility
Look in your PHP script for any SQL concerning these tables. In particular pay attention to WHERE clauses and JOIN clauses and to a lesser extent ORDER BY / GROUP BY clauses.

Every column that is mentioned in a WHERE or in a JOIN's ON clause must have an index in the database. So as an example if you had SQL like this

SELECT * FROM orders WHERE statusId = 123

then statusId should have an index in table 'orders'. Similarly

SELECT * FROM orders
INNER JOIN order_products ON orders.id = order_products.id
WHERE statusId = 123

would imply that all 3 columns, orders.id, order_products.id and statusId need indexes.

Check your code as I have suggested and add indexes where needed. You might not need to change any PHP at all
0
 
LVL 11

Author Comment

by:G_H
Comment Utility
@bportlock

I still have no FTP access, I will look at the SQL SELECTS when I get it.

Would this be better:

# Table "orders_products_20110207" DDL

CREATE TABLE `orders_products_20110207` (
  `orderId` int(11) NOT NULL default '0',
  `productOptionId` int(11) NOT NULL default '0',
  `colourId` int(11) NOT NULL default '0',
  `cost` float NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0',
  KEY `orderId` (`orderId`),
  KEY `productOptionId` (`productOptionId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Open in new window


GH
0
 
LVL 11

Author Comment

by:G_H
Comment Utility
Sorry, re-posting, hoping to get everything in my mind into this box this time...

Because the fields orders_products.productOptionId and orders_products.orderId are not unique, should I be trying to index them individually, or as a collective (multi-column index)?

GH
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Whenever there is a performance problem in a PHP / MySQL web site, the issues are always found in the I/O subsystem and that is almost always the data base.  You can use EXPLAIN SELECT to get some vision into what your SELECT queries are doing.  You can use ALTER TABLE to add indexes as Brian suggested (and it appears from the  CREATE TABLE statements that the indexes are missing).  Those would be my first changes.  Next I would scan the code for the word, "SELECT" and make sure that you do not have a query doing something like SELECT * FROM... WHERE NOT IN (SELECT * FROM... WHERE NOT IN (... etc.  These kinds of queries cause table scan - all of the data has to be inspected multiple times to complete the query.  Table scans are a "bad dog" thing in data base access.

If you need to time the script or part of it, this little stopwatch class may be helpful.
<?php // RAY_oop_stopwatch.php
error_reporting(E_ALL);


// DEMONSTRATE A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+
// MAN PAGE http://us.php.net/manual/en/function.microtime.php


class StopWatch
{
    protected $a, $z;
    public function __construct()
    {
        $this->a = array();
        $this->z = array();
    }

    // A METHOD TO CAPTURE A START TIME
    public function start($name='TIMER')
    {
        $this->a[$name] = microtime(TRUE);
    }

    // A METHOD TO CAPTURE AN END TIME
    public function stop($name='ALL')
    {
        if ($name == 'ALL')
        {
            foreach ($this->a as $name => $start_time)
            {
                if (!isset($this->z[$name])) $this->z[$name] = microtime(TRUE);
            }
        }
        else
        {
            $this->z[$name] = microtime(TRUE);
        }
    }

    // A METHOD TO READ OUT THE TIMER(S)
    public function readout($m=1000, $eol=PHP_EOL)
    {
        $str = NULL;
        foreach ($this->a as $name => $start_time)
        {
            $str .= $name;
            if (!isset($this->z[$name]))
            {
                $str .= " IS STILL RUNNING";
            }
            else
            {
                $lapse_time = $this->z[$name] - $start_time;
                $lapse_msec = $lapse_time * $m;
                $lapse_echo = number_format($lapse_msec, 1);
                $str .= " $lapse_echo";
            }
            $str .= $eol;
        }
        return $str;
    }
}


// DEMONSTRATE THE USE -- INSTANTIATE THE STOPWATCH OBJECT
$sw  = new Stopwatch;

// SET STOPWATCH NAMES
$go = 'GOOGLE ONLY';
$gy = 'GOOGLE AND YAHOO!';
$yo = 'YAHOO! ONLY';

// START SOME TIMERS
$sw->start($go);
$sw->start($gy);

// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME
$page = 'http://google.com';
$html = file_get_contents($page);

// STOP ONE OF THE STOPWATCHES AND START THE OTHER
$sw->stop($go);
$sw->start($yo);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://yahoo.com';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT (TWO WILL BE INCOMPLETE)
echo nl2br($sw->readout());

// STOP ALL OF THE REMAINING STOPWATCHES
$sw->stop();

// REPORT THE STOPWATCHES CONTENT AGAIN
echo nl2br($sw->readout());

Open in new window

0
 
LVL 34

Expert Comment

by:Beverley Portlock
Comment Utility
"Because the fields orders_products.productOptionId and orders_products.orderId are not unique, should I be trying to index them individually, or as a collective (multi-column index)?"

Just index them as single indexes. Multi-column indexes have some restrictions that could cause you problems. See http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
0
 
LVL 11

Author Comment

by:G_H
Comment Utility
@bportlock

I was reading that as you posted!

Is the "KEY" in the structure above (Post #34925231) the same as "INDEX"?

Sorry, but I use Navicat to handle my MySQL databases, and that is what it has given me if I want to have an "Index" which is not a Primary Key...

GH
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Expert Comment

by:Beverley Portlock
Comment Utility
"Is the "KEY" in the structure above (Post #34925231) the same as "INDEX"?"

Yes.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
One last note, then I'll sign off on this Q.  Have a look at this query:

SELECT * FROM orders
INNER JOIN order_products ON orders.id = order_products.id
WHERE statusId = 123

Is statusId a unique column?  If so, you can add LIMIT 1 to the end of the query and MySQL will stop work when it finds the match.  Often this little change alone, adding a LIMIT, will make great improvements in performance.

And if you're looking for the first 200 elements of the data base, LIMIT 200 would seem to make sense.
0
 
LVL 11

Author Comment

by:G_H
Comment Utility
@Ray_Paseur

This is all built dynamically, and there are loads of bits like that, but very few are unique.

@bportlock

OK, My understanding has grown!

I have now finally got the FTP access, I can see the SELECT:

 
SELECT
	o.orderId,
	s.description,
	o.createdOn,
	CONCAT(c.title,' ',c.firstName,' ',c.lastName) as 'customerName',
	totalOrderAmount
FROM
	orders o
	INNER JOIN orders_status s ON s.statusId = o.statusId
	INNER JOIN orders_customers oc ON oc.orderId = o.orderId
	INNER JOIN customers c ON c.customerId = oc.customerId
GROUP BY
	o.orderId
ORDER BY
	o.createdOn DESC LIMIT 0, 50

Open in new window


I guess every item in the INNER JOIN's should really be indexed. Currently only those which are PK's are...
0
 
LVL 34

Expert Comment

by:Beverley Portlock
Comment Utility
Ensure that all these columns have indexes (keys)

      INNER JOIN orders_status s ON s.statusId = o.statusId
      INNER JOIN orders_customers oc ON oc.orderId = o.orderId
      INNER JOIN customers c ON c.customerId = oc.customerId

You should see a massive improvement in speed. If you don't get a big improvement then run the query with the word EXPLAIN in fron of it and see where the table scans are

http://dev.mysql.com/doc/refman/5.0/en/explain.html
0
 
LVL 11

Author Closing Comment

by:G_H
Comment Utility
@bportlock

Thank you for taking the time to teach me a bit more about MySQL!

@Ray_Paseur

Thanks for your hep too.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
Comment Utility
So, things are running better?
0
 
LVL 11

Author Comment

by:G_H
Comment Utility
I have not made the changes yet, but I am sure this is the issue.

I'll post again when the changes have been made and evaluated. I have got to wait for a "quiet time" to play with the live DB...

GH
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now