Solved

MySQL PHP Speed Improvement (Bad DB design?)

Posted on 2011-02-18
13
1,278 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
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
 
LVL 108

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 108

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
calculated column 12 74
updating table data with inner join 9 24
Put radio button in my form but already has components from my database 4 42
Session timeout 5 13
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

943 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

10 Experts available now in Live!

Get 1:1 Help Now