Michael Osakede Michael Osakede - 2 months ago 5
MySQL Question

Increase Mysql/PHP Query Speed

I have 2 tables,one has 2 million and the other has 30 million records,
I need to compare the records on both tables but this is extremely slow.

can anyone offer suggestions on ways to increase the speed?

<?php
$con = mysql_connect("localhost","root","password");
mysql_select_db("DMBONE", $con);
$result = mysql_query("SELECT * FROM sucid where priority=''");
while($row = mysql_fetch_array($result))
{
$result1 = mysql_query("SELECT count(*) FROM bills_logic where month(tdate)=8 and x1=".$row[0]."");
if($row1 = mysql_fetch_array($result1))
{
if($row1[0]==0)
{
echo $row[0]." DEAD\r\n";
mysql_query("update sucid set priority='DEAD' where bid=".$row[0]."") or die(mysql_error());
}
else
{
echo $row[0]." ".$row1[0]."\r\n";
mysql_query("update sucid set priority='".$row1[0]."' where bid=".$row[0]."") or die(mysql_error());
}
}
}
?>


CREATE TABLE `sucid` (
`bid` varchar(500) NOT NULL,
`priority` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `bills_logic` (
`bid` int(11) NOT NULL AUTO_INCREMENT,
`num` varchar(500) NOT NULL,
`stat` varchar(500) NOT NULL,
`tdate` varchar(500) NOT NULL,
`x1` varchar(500) NOT NULL,
`amt` varchar(500) NOT NULL DEFAULT '30',
PRIMARY KEY (`bid`)
) ENGINE=InnoDB AUTO_INCREMENT=35214848 DEFAULT CHARSET=latin1


above are the create table statements for the tables.

Answer

You have found the world's slowest way of doing a join. You may be happier to do it the old-fashioned way (but just fashionably enough to use mysqli):

<?php    
  $mysqli = new mysqli("localhost","root","password","database");
  if ($mysqli->connect_errno) {
     printf("Connect failed: %s\n", $mysqli->connect_error);
     exit();
  }

  $sql = "update sucid
          left join (
            select count(*) as priority, x1 
            from bills_logic b
            where month(tdate)=8
            group by x1
          ) bg
          on bg.x1 = sucid.bid
          set sucid.priority = coalesce(bg.priority,'DEAD');" 

  if ($mysqli->query($sql) === TRUE) {
    printf("I'm done already. This was fast, wasn't it?\n");
  } 
  else {
    echo "Something went wrong: " . $mysqli->error . "\n";
    exit();
  }

?>

You might want to add an index on bills_logic.x1, though it will not help too much here.

And you should really fix your columns, e.g. tdate should not be a varchar(500). The update will fail completely if you have any row with an invalid date. Using correct datatypes prevents you from having invalid values. num, stat and amt sound like they could be int (or maybe decimal), x1 maybe too. And your priority-column would also work as int, if you replace DEAD with 0.

Comments