Database efficiency

completeamateur · June 20, 2008

I'm worried about the number of queries that will be used on a single page load on the site I'm designing.

As an estimate there will be up to 200 per page load when displaying football results (per season).

Is this ridiculous or just me thinking it's ridiculous?

Regards.

PFMaBiSmAd · June 20, 2008

Each query involves making a transfer to and from the database server, where the database server might be busy servicing other requests, and your request might need to wait. Any time you can reduce the total number of queries, you will end up with a faster operating web page.

It is highly likely that your data is all very similar and can be SELECTED all at once and then displayed where it is needed on the page. Post your code to get help with ways that you can combine part or all of the queries into a few or even one query.

Also, poor table design can result in a need for more queries than necessary. Posting your table definitions would allow someone to determine if there are problems with their layout.

fenway · June 20, 2008

How did you make this estimate?

completeamateur · June 21, 2008

Roughly 60 results per season and 3 queries made per result...

I'm afraid my code will probably make you cringe, I should probably get some tuition in web design.

$query = "SELECT name FROM confederation WHERE (id = $confederationID) ORDER BY name";
$result = mysql_query("$query");
$confederation = mysql_fetch_array($result);

$query = "SELECT name FROM country WHERE (id = $countryID)";
$result = mysql_query("$query");
$country = mysql_fetch_array($result);

$query = "SELECT name,start FROM competition WHERE (id = $divisionID)";
$result = mysql_query("$query");
$division = mysql_fetch_array($result);

$query = "SELECT name FROM club WHERE (id = $clubID)";
$result = mysql_query("$query");
$club = mysql_fetch_array($result);

echo ("
      <div class='navigator'>
        <a href='$server_url'>Home</a> »
        Clubs (<a href='$server_url/clubs/index.php?confederationID=$confederationID'>$confederation[name]</a> »
        <a href='$server_url/clubs/index.php?confederationID=$confederationID&countryID=$countryID'>$country[name]</a> »
        <a href='$server_url/clubs/index.php?confederationID=$confederationID&countryID=$countryID&divisionID=$divisionID'>$division[name]</a>) »
        $club[name]
      </div>
      <h1>$club[name] Results</h1>
      <p>
        <span style='float:right; padding:0px 3px; text-align: center; width:40px;'>Att [<a href=''>?</a>]</span>
        <span style='float:right; padding:0px 3px; text-align: center; width:175px;'>Venue</span>
        <span style='float:right; padding:0px 3px; text-align: center; width:40px;'>Score</span>
        <span style='float:right; padding:0px 3px; text-align: center; width:175px;'>Opposition</span>
        <span style='float:right; padding:0px 3px; text-align: center; width:40px;'>Type</span>
        <span style='display: block; padding:0px 3px; text-align: center; width:60px;'>Date</span>
      </p>");

$query = "SELECT * FROM result WHERE ((YEAR(datetime) = 2008 AND MONTH(datetime) < 07) OR (YEAR(datetime) = 2007 AND MONTH(datetime) > 07)) AND (home = $clubID) OR (away = $clubID) ORDER BY datetime";
//	((home = $clubID) OR (away = $clubID))
$result1 = mysql_query("$query");

$i = 1;
while ($match = mysql_fetch_array($result1)) {

//Obtain data
$tstamp = strtotime($match['datetime']);

$query = "SELECT name,acc FROM competition WHERE (id = $match[competition])";
$result = mysql_query("$query");
$competition = mysql_fetch_array($result);

if ($clubID == $match['home']) { $opposition = $match['away']; } else { $opposition = $match['home']; }
$query = "SELECT name FROM club WHERE (id = $opposition)";
$result = mysql_query("$query");
$opposition = mysql_fetch_array($result);

$query = "SELECT name FROM ground WHERE (id = $match[ground])";
$result = mysql_query("$query");
$ground = mysql_fetch_array($result);

if ( $i&1 ) {
	$oe = 'odd';
} else {
	$oe = 'even';
}

echo("
      <div class='$oe'>
        <span style='float:right; padding:0px 3px; text-align: center; width:40px;'><input type='checkbox' name='option1' value='Milk' style='margin: 2px 0px 0px 0px; padding: 0px;' /></span>
        <span style='float:right; padding:0px 3px; width:175px;'>$ground[name]</span>
        <span style='float:right; padding:0px 3px; text-align: center; width:40px;'>$match[hg]-$match[ag]</span>
        <span style='float:right; padding:0px 3px; width:175px;'>$opposition[name]</span>
        <span style='float:right; padding:0px 3px; text-align: center; width:40px;'><acronym title='$competition[name]'>$competition[acc]</acronym></span>
        <span style='display: block; padding:0px 3px; text-align: center; width:60px;'>" . date('d/m/y', $tstamp) . "</span>
      </div>
      ");
$i++;
}

}

completeamateur · June 22, 2008

My table definitions are:

-- 
-- Table structure for table `club`
-- 

CREATE TABLE `club` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `ground` tinytext,
  `division` smallint(5) unsigned default NULL,
  `country` tinyint(3) unsigned NOT NULL,
  `user` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `competition`
-- 

CREATE TABLE `competition` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `acc` tinytext NOT NULL,
  `country` tinyint(3) unsigned NOT NULL,
  `type` tinyint(2) unsigned NOT NULL,
  `tier` tinytext NOT NULL,
  `start` tinyint(2) unsigned zerofill NOT NULL,
  `user` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `comptype`
-- 

CREATE TABLE `comptype` (
  `id` tinyint(2) NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `confederation`
-- 

CREATE TABLE `confederation` (
  `id` tinyint(1) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `detail` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `country`
-- 

CREATE TABLE `country` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `confederation` tinyint(1) unsigned NOT NULL,
  `user` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `ground`
-- 

CREATE TABLE `ground` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` tinytext NOT NULL,
  `capacity` mediumint(6) unsigned NOT NULL,
  `address` tinytext NOT NULL,
  `postcode` tinytext NOT NULL,
  `ticketoffice` tinytext NOT NULL,
  `ticketprice` tinytext NOT NULL,
  `country` tinyint(3) unsigned NOT NULL,
  `user` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `log`
-- 

CREATE TABLE `log` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `user` smallint(5) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `record`
-- 

CREATE TABLE `record` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `user` mediumint( unsigned NOT NULL,
  `match` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `result`
-- 

CREATE TABLE `result` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `home` smallint(5) unsigned NOT NULL,
  `hg` tinyint(2) NOT NULL,
  `away` smallint(5) unsigned NOT NULL,
  `ag` tinyint(2) NOT NULL,
  `competition` smallint(5) unsigned NOT NULL,
  `ground` smallint(5) unsigned NOT NULL,
  `datetime` datetime NOT NULL,
  `user` mediumint( unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `user`
-- 

CREATE TABLE `user` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  `forename` varchar(20) default NULL,
  `surname` varchar(20) default NULL,
  `password` varchar(32) NOT NULL,
  `salt` varchar(3) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

fenway · June 22, 2008

Roughly 60 results per season and 3 queries made per result...

So you should be able to get all of this back from a single query by joining the relevant tables...

completeamateur · June 22, 2008

But that will lead to a LOT of repeated data? I thought efficient database design segregated duplicate data to individual tables, the tables being linked by indexes known as 'foreign keys'?

EDIT: Perhaps the "result" table could be optimised by splitting:

home, away, competition, ground

into:

hometeamname, homeID

awayteamname, awayID

competition, compID

ground, groundID

???

fenway · June 23, 2008

I mean joining them in the query, not in the actual tables...

completeamateur · June 27, 2008

Thanks for your replies fenway, I'll do some research on querying multiple tables at once.

Regards.

br0ken · June 27, 2008

I agree with fenway. You should read up on Normalisation. This will lead to better table design and with a clever JOIN/SELECT statement you could achieve your desires in one query.

fenway · June 27, 2008

There's a whole sticky dedicated to this topic.

Sign In

Database efficiency

Recommended Posts

completeamateur

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

completeamateur

Link to comment

Share on other sites

completeamateur

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

completeamateur

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

completeamateur

Link to comment

Share on other sites

br0ken

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information