MySQL PHP Speed Improvement (Bad DB design?)

@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

Select allOpen in new window

GH

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

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());

Select allOpen in new window

"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

@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

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

Yes.

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.

@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

Select allOpen in new window

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

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

@bportlock

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

@Ray_Paseur

Thanks for your hep too.

So, things are running better?

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