Large Database is Loading Slowly. Any Tips!, Large MySQL Database but takes forever to load. Any Tips to Optimize? Options

[Dante Monaldo]

I have a MySQL database with 1 million+ rows in one table (1.3GB), but it takes forever to load. It is a price comparison website with a product in each row, but it takes forever (40secs+) to search the database and pull up the page. When a visitor searches for a product the page just shows a blank page loading until it shows the search results or shows the product.

I'm not sure if it is my hosting at BlueHost.com, or the database being to large. Take a look. (http://www.priceprospector.com/comparison_shopping.php)

Does anyone have any tips to speed up the database? I have already tried repairing and optimizing the database through phpMyAdmin.

I appreciate the help.

This post has been edited by Dante Monaldo: Jun 4 2011, 01:46 PM

[Brian Chandler]

It's possible that the SQL is written inefficiently -- can't help unless you show us. At this size you also need to look carefully at the indexing of the table. Beyond that you need an SQL guru, and they can be expensive (also if you don't know what you are doing, it's easy to pay lots of money to someone else who can talk well, but doesn't really know what they are doing either).

[Dante Monaldo]

Below is the code for search.php, but I think it may be a bit much. This is also only the tip of the iceberg, as it also takes unbelievably long to load the individual product page, which involves more code.

CODE

<?php

require_once('config.php');
require_once('common.php');

// get settings from database

$query = "SELECT * FROM config LIMIT 1;";
database_inquiry($query, $rows);
extract($rows[0]);

$search = custom_get('search');
$fromprice = custom_get('fromprice',0);
$toprice = custom_get('toprice',0);
$page = custom_get('page',1);

if (!is_numeric($page)) $page = 1;

// bound products if price ranges exist

$sqlparm = '';
$and = '';

if ($search != '')
   {
   if (stristr($search,'+') == false && stristr($search,'-') == false && stristr($search,'"') == false && stristr($search,'(') == false && stristr($search,'*') == false)
      {
      $sqlparm .= "match(name) AGAINST('$search')";
      }
   else
      {
      $sqlparm .= "match(name) AGAINST('$search' IN BOOLEAN MODE)";
      }
   $and = ' AND';
   }

if ($fromprice > 0)
   {
   $sqlparm .= "$and sale_price >= '$fromprice'";
   $and = ' AND';
   }

if ($toprice > 0)
   {
   $sqlparm .= "$and sale_price <= '$toprice'";
   $and = ' AND';
   }

if ($sqlparm == '')
   {
   $sqlparm .= "match(name) AGAINST('$search')";
   }
$sqlparm .= ' group by name';
if (stristr($search,'+') == true || stristr($search,'-') == true || stristr($search,'"') == true || stristr($search,'(') == true || stristr($search,'*') == true)
   {
   if ($product_order == 1)
      {
      $sqlparm .= ' ORDER BY sale_price ASC';
      }

   if ($product_order == 2)
      {
      $sqlparm .= ' ORDER BY sale_price DESC';
      }
   }

// get category lists
$category_list = array();
$query = "SELECT id, name FROM categories WHERE parent_id = '';";
database_inquiry($query, $rows);
$category_list = $rows;

// set database limits

$start = ($page-1)*$products_per_page;
$limits = " LIMIT $start,$products_per_page";

// create next/prev links

$query = "SELECT count(*) AS count from products WHERE $sqlparm;";
database_inquiry($query, $rows);
$product_count = $rows[0]['count'];

$total_pages = $product_count / $products_per_page;
if ($total_pages != intval($total_pages)) $total_pages++;
$total_pages = (int) $total_pages;
if ($total_pages < 1) $total_pages = 1;

// create page numbers

$from_page = $page-4;
$to_page = $page+4;
if ($from_page < 1){$from_page = 1;}
if ($to_page > $total_pages){$to_page = $total_pages;}

$search2 = stripslashes($search);
$search2 = urlencode(($search2));
$search2 = str_replace('"','"',$search2);

$page_array = '';
for ($i=$from_page;$i<=$to_page;$i++)
   {
   $page_array .= "<a href=\"search.php?search=$search2&page=$i&fromprice=$fromprice&toprice=$toprice\">$i </a>";
   }

$next_prev = '';
$prev_page = $page-1;
$next_page = $page+1;
if ($page > 1) $next_prev .= "<a href=\"search.php?search=$search2&page=$prev_page&fromprice=$fromprice&toprice=$toprice\"><<Prev</a> | "; else $next_prev .= "<<Prev | ";
$next_prev .= $page_array;
if ($page < $total_pages) $next_prev .= "<a href=\"search.php?search=$search2&page=$next_page&fromprice=$fromprice&toprice=$toprice\">| Next>></a>"; else $next_prev .= "| Next>>";

if ($total_pages == 1)
   {
   $next_prev = '';
   }

// Get merchant list

$query = "SELECT * from datafeeds;";
database_inquiry($query, $rows);
$affiliates = $rows;

// Get product list

$query = "SELECT * from products WHERE $sqlparm $limits;";
database_inquiry($query, $rows);
$product_list = format_product_list($rows,$products_per_line);

// Generate inner template

$tpl = & new Template();
$tpl->set('top_ad',$top_ad);
$tpl->set('bottom_ad',$bottom_ad);
$tpl->set('product_list',$product_list);
$tpl->set('next_prev',$next_prev);
$tpl->set('search',$search);
$main_content = $tpl->fetch('templates/default/search.tpl');

// Generate outer template

$tpl2 = & new Template();
$tpl2->set('main_content',$main_content);
$tpl2->set('site_name',$site_name);
$tpl2->set('site_slogan',$site_slogan);
$tpl2->set('template',$template);
$tpl2->set('title',$site_name.' :: '.$search);
$tpl2->set('description',$site_name.' '.$search);
$tpl2->set('keywords',$site_name.' '.$search);
generate_custom_menu($cust_menu, $cust_menu_items);
$tpl2->set('cust_menu',$cust_menu);
$tpl2->set('cust_menu_items',$cust_menu_items);
$tpl2->set('menu',generate_menu());
$tpl2->set('news_menu',generate_news_menu(10));
$x = $tpl2->fetch("templates/$template/master.tpl");

// Generate html page

echo $x;

?>
<?php @include('database.php'); ?>

I expect that I'll have to hire someone to go through my site and optimize it to the fullest, but do you know where I could find such a person?

And when you're talking about the indexing of the table, what exactly does that mean?

[Brian Chandler]

Below is the code for search.php, but I think it may be a bit much

Um, yes. It doesn't look immediately wrong -- but there is a lot. You could start by working out where the time is being used: in the php manual you should find a function for recording processor time, so you can measure how much each stage is taking, then you know what to concentrate on.

But it looks as though you are using match(), the generalised text search function, which is going to be *much* slower than a direct match with a column in more structured data. Where does your raw data come from? Does each item have an exact product code or whatever? If so you should be able to do most searches directly on the code, and only use full-text search when the user can't immediately find the item. Again, if the raw data is scraped from lots of websites this may not be practical, since there will be trivial variations. But working on this might be a way to be better than the competition.

I did notice something odd:

CODE

$search2 = stripslashes($search);
$search2 = urlencode(($search2));
$search2 = str_replace('"','"',$search2);

Why replace " by " ??
And I believe that stripslashes() really not the way to do [anything], but can't remember the details.

About the indexing: look with phpmyadmin, and you can see which columns are indexed (means searching is *fast*); unindexed columns are *much* slower.