Solved

MySQL PHP Speed Improvement (Bad DB design?)

Posted on 2011-02-18
13
1,307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 34924824
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
ID: 34925231
@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
ID: 34925250
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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 34925267
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
ID: 34925348
"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
ID: 34925432
@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
 
LVL 34

Expert Comment

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

Yes.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 34926134
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
ID: 34926310
@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
ID: 34926726
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
ID: 34926931
@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
ID: 34927094
So, things are running better?
0
 
LVL 11

Author Comment

by:G_H
ID: 34927741
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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 dynamically set the form action using jQuery.

624 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