Need help with PHP and MYSQL!

Need Help!

[dannylewin]

Hi there,

I need to speed up this MYSQL statement and fast. Ive been given 72 hours to fix it or my hosting provider will suspend my database.

Could someone please tell me what is slowing down the query. Is it the calculations being performed in the query? Or is it because im running the same select statement twice to generate my pagination?

My host said i should probably move some of the logic over to PHP.

There were some things i wanted to do, and thought doing it in the query was the only way.

Visitors can select the currency to display on the site. currently im doing the conversion in the select statement. I know how to do it in PHP, but i offer a "order by" option on the search form and this includes price - low to high and price high - low.

If something was 500USD and another 400GBP and the visitor sets the sites currency to USD how can i order them correctly? GBP -> USD means that 400GBP will become 649USD so ordering by price low to high without the conversion in the select statement would put them out of order. Is there a better way of putting this statement together using all the search options i offering, but put some of it PHP side to speed up the query?

Id greatly appreciate any help you can offer. Ive spent HOURS creating this site and now it may go away for a while if i cant fix this issue!

Thanks in advance!

Danny

$diff = intval($_GET['difference']);

mysql_select_db($database_dresses, $dresses);
$query_dressSearch = "
SELECT dress_listing.dress_id, 
dress_listing.dress_title as listing_title, 
dress_images.image_file as main_image, 
age_ranges.age_range, 
COUNT(listing_stats.stat_id) as popularity,
dress_listing.dress_currency, 
((dress_listing.dress_price/rates_from.rate)*rates_to.rate) as price, 
((dress_listing.dress_old_price/rates_from.rate)*rates_to.rate) as old_price, 
rates_from.symbol, 
dress_listing.dress_reduced, 
dress_designers.designer_name,
dress_designers.designer_tidy_url,
dress_condition.dress_condition, 
dress_listing.dress_status, 
country_table.country_name,
dress_listing.dress_official,
dress_listing.dress_chest,
dress_listing.dress_waist,
dress_listing.dress_skirt,
dress_listing.dress_sleeve,
dress_listing.dress_full_length,
dress_listing.dress_tidy_url as listing_tidy_url
FROM dress_listing
LEFT JOIN exchange_rates rates_from ON (dress_listing.dress_currency = rates_from.currency) 
INNER JOIN exchange_rates rates_to ON (rates_to.currency = '".$_SESSION['cur']."')
LEFT JOIN dress_designers ON dress_listing.designer_id = dress_designers.designer_id 
LEFT JOIN dress_condition ON dress_listing.condition_id = dress_condition.condition_id 
LEFT JOIN age_ranges ON dress_listing.age_id = age_ranges.age_id 
LEFT JOIN dress_images ON (dress_listing.dress_id = dress_images.dress_id AND is_main = 1)
LEFT JOIN members_table ON dress_listing.member_id = members_table.member_id
LEFT JOIN country_table ON members_table.member_country = country_table.country_code
LEFT JOIN listing_stats ON (listing_stats.dress_id = dress_listing.dress_id AND listing_stats.stat_type='hit')
WHERE dress_listing.dress_status = 'A'
AND dress_designers.designer_tidy_url LIKE '%".safe($_GET['designer'])."' 
AND age_ranges.age_range LIKE '%".safe($_GET['ages'])."' 
AND dress_condition.dress_condition LIKE '".safe($_GET['condition'])."%' 
";

if ($_GET['type'] == "reduced-dresses"){
$query_dressSearch .= "
AND dress_listing.dress_reduced = '1'
";
} elseif ($_GET['type'] == "bargain"){
$query_dressSearch .= "
AND ((dress_listing.dress_old_price/rates_from.rate)*rates_to.rate) <= '$bargain_price'
";

}


// MEASUREMENT SEARCH

$chest = safe($_GET['chest']);
$waist = safe($_GET['waist']);
$skirt = safe($_GET['skirt']);
$sleeve = safe($_GET['sleeve']);
$total_length = safe($_GET['total_length']);

if($chest == ""){
$chest_min = 0;
$chest_max = 100;
} else {
$chest_min = ($chest-$diff);
$chest_max = ($chest+$diff);
}
if($waist == ""){
$waist_min = 0;
$waist_max = 100;
} else {
$waist_min = ($waist-$diff);
$waist_max = ($waist+$diff);
}
if($sleeve == ""){
$sleeve_min = 0;
$sleeve_max = 200;
} else {
$sleeve_min = ($sleeve-$diff);
$sleeve_max = ($sleeve+$diff);
}
if($skirt == ""){
$skirt_min = 0;
$skirt_max = 200;
} else {
$skirt_min = ($skirt-$diff);
$skirt_max = ($skirt+$diff);
}
if($total_length == ""){
$total_length_min = 0;
$total_length_max = 200;
} else {
$total_length_min = ($total_length-$diff);
$total_length_max = ($total_length+$diff);
}


if($_GET['match'] == '1'){

$query_dressSearch .= "
AND (dress_listing.dress_chest between '$chest_min' AND '$chest_max' 
AND dress_listing.dress_waist between '$waist_min' AND '$waist_max'
AND dress_listing.dress_sleeve between '$sleeve_min' AND '$sleeve_max'
AND dress_listing.dress_skirt between '$skirt_min' AND '$skirt_max'
AND dress_listing.dress_full_length between '$total_length_min' AND '$total_length_max'

)
";
} else {
$query_dressSearch .= "
AND (dress_listing.dress_chest between '$chest_min' AND '$chest_max' 
OR dress_listing.dress_waist between '$waist_min' AND '$waist_max'
OR dress_listing.dress_sleeve between '$sleeve_min' AND '$sleeve_max'
OR dress_listing.dress_skirt between '$skirt_min' AND '$skirt_max'
OR dress_listing.dress_full_length between '$total_length_min' AND '$total_length_max'
)
";
}
// END MEASUREMENT SEARCH

// SEARCH IMAGE ONLY
if ($_GET['img_only'] == "1") {
$query_dressSearch .= "
AND dress_images.dress_id IS NOT NULL
";
}
//END IMAGE ONLY

// MIN - MAX PRICE SEARCH
$minPrice = number_format($_GET['minPrice'],'2','.','');
$maxPrice = number_format($_GET['maxPrice'],'2','.','');

if ($_GET['minPrice'] != ""){
$price_min = safe($minPrice);
} else {
$price_min = 0;
}
if ($_GET['maxPrice'] != ""){
$price_max = safe($maxPrice);
} else {
$price_max = 5000;
}

if(($_GET['minPrice'] != "" && $_GET['minPrice'] != "") && ($_GET['minPrice'] < $_GET['minPrice'])){
$query_dressSearch .= "
AND ((dress_listing.dress_price/rates_from.rate)*rates_to.rate) between '$price_min' AND '$price_max'
";
} elseif (($_GET['maxPrice'] != "" && $_GET['maxPrice'] != "") && ($_GET['maxPrice'] > $_GET['maxPrice'])) {
$query_dressSearch .= "
AND ((dress_listing.dress_price/rates_from.rate)*rates_to.rate) between '0' AND '5000'
";
} else {
$query_dressSearch .= "
AND ((dress_listing.dress_price/rates_from.rate)*rates_to.rate) between '$price_min' AND '$price_max'
";
}
// END MIN - MAX PRICE SEARCH

$currentPage = $_SERVER["PHP_SELF"];

if (isset($_GET['page'])) {
 $page = $_GET['page'];
} else {
 $page = 1;
} // if
mysql_select_db($database_dresses, $dresses);
$query = "".$query_dressSearch." GROUP BY dress_listing.dress_id ";
$result = mysql_query($query, $dresses) or die(mysql_error());
$numrows = mysql_num_rows($result);


$rows_per_page = 10;
$lastpage = ceil($numrows/$rows_per_page);
$lpm1 = $lastpage - 1;

$page = (int)$page;
if ($page > $lastpage) {
 $page = $lastpage;
} // if
if ($page < 1) {
 $page = 1;
} // if

$limit = 'LIMIT ' .($page - 1) * $rows_per_page .',' .$rows_per_page;

$search = "".$query." ".$order." ".$limit."";
$dressSearch = mysql_query($search, $dresses) or die(mysql_error());
$row_dressSearch = mysql_fetch_assoc($dressSearch);
$totalRows_dressSearch = mysql_num_rows($dressSearch);

[brotherhood of LAN]

Welcome to the forums dannylewin,

Obviously that is quite a complex query with lots of joins.

Have you tried to use the EXPLAIN MySQL command to see where indexes are being used and where they are not? Indexes in the right place can greatly speed up queries when applied in the right place.

Also consider that you may want to cache queries via MySQL's cache or on disk.

If you are able to edit your my.ini file, upping the available memory for caching would help a lot (providing your host doesn't complain about that too).

AND dress_designers.designer_tidy_url LIKE '%".safe($_GET['designer'])."'
AND age_ranges.age_range LIKE '%".safe($_GET['ages'])."'
AND dress_condition.dress_condition LIKE '".safe($_GET['condition'])."%'

I would suspect that this is a slower part of the query because all the joins do not use the fuzzy comparison of LIKE %

If more indexes or toggling MySQL system variables is not an option, you'd want to isolate the slowest part of the query and consider splitting it into two queries, perhaps by using a temporary disk based table to store values to be used in the 2nd query.

[dannylewin]

Hi, Thanks for your reply!

My host said that there are currently 10Mil records per query! I just dont know how that is happening...

I ran the query in MYSQL admin and it took 8 seconds.

I removed:

COUNT(listing_stats.stat_id) as popularity,

and

LEFT JOIN listing_stats ON (listing_stats.dress_id = dress_listing.dress_id AND listing_stats.stat_type='hit')

and it dropped to 0.0139... so guessing that was maybe causing problems?

Maybe ill just add a hit counter in my main table and +1 for each hit instead of inserting a new record in the stats table?

[brotherhood of LAN]

host said that there are currently 10Mil records per query!

Try running the query but with EXPLAIN at the start of it, it will tell you how many records MySQL has to 'look at' for each table.

10M records is a lot, if you don't have that many results in your tables then it could be the result of a badly formed JOIN.

[dannylewin]

Hey,

I did run EXPLAIN (didnt know about it, until my host suggested it).

There were excessive rows being pulled. Its probably just bad DB design on my part (still learning).

Now there are only 1 rows per join and my query time has dropped to 0.0003

Think im heading in the right direction...

I think the 10Mil records was because A) i wasnt using indexes and B) i was joining indevidual stats and using COUNT(). Removed that and its helped allot.

[brotherhood of LAN]

Good stuff, that should keep them happy.