Possible Optimisation of a MySQL Query

j.smith1981 · July 14, 2010

I want your opinion on the following code.

It was just mostly going off memory of what I have done before regarding SQL queries.

The basic point of this application though (just to make it ever so much clearer to yourselves), is the user enters the website.

It loads index.php (code listed below), the user is presented with a drop down text box, that is linked actually into the database itself, so after midnight (when no ones using it at work), it will update the database and show all results, for the user to select.

What it then does is show a text box search for printer models.

Once the user has entered a search term.

This then displays all the images with that result included.

Basically a custom search engine on printer images.

Here's the code anyhow:

Here's index.php, the processing file that the user goes to when visiting the page

<?php
require_once('dbconnect.php');

if(!isset($_GET['mfg'])) {

$mfg_selected = $_GET['mfg'];

$message = 'Please select a manufacturer below:';

$sql = "SELECT manufacturer.manufacturerid AS mfgID, manufacturer.name AS mfgName FROM manufacturer JOIN product ON manufacturer.manufacturerid = product.manufacturerid JOIN categorynames ON product.categoryid = categorynames.categoryid WHERE categorynames.name LIKE ('%printer%') GROUP BY manufacturer.manufacturerid ORDER BY manufacturer.name ASC";

$mfg_select = mysql_query($sql) or die("Please contact system administrator, quoting error: <br /><br />" . mysql_error());
}
//

if(isset($_GET['mfg']) || isset($_GET['printerSearch'])){

$printerSearch = $_GET['printerSearch'];
$mfg_selected = $_GET['mfg'];

$sql = "SELECT productimages.productid AS productImage, product.mfgpartno AS mfgpartno FROM productimages JOIN product ON productimages.productid = product.productid JOIN manufacturer ON product.manufacturerid = manufacturer.manufacturerid WHERE product.mfgpartno LIKE('%$printerSearch%') AND manufacturer.manufacturerid = '10328' AND productimages.type = 'Thumbnail'";

$image_result = mysql_query($sql) or die("Please contact system administrator, quoting error: <br /><br />" . mysql_error());
}

include 'form.php';

?>

Here is the form.php file, that is called after the process.php execution:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
</head>
<body>

<h1>Cartridge World - Printer Images</h1>

<?php echo "$message"; ?>

<form id="productImages" name="productImages" method="GET" action="<?php echo $_SERVER['PHP_SELF']; ?>">

<?php
if(!isset($_GET['mfg'])){
{
?>

<select id="mfg" name="mfg">

	<?php
	while($mfg_result = mysql_fetch_array($mfg_select))
	{{
	?>
	<option value="<?php echo $mfg_result[0] ?>"><?php echo $mfg_result[1] ?></option>
	<?php
	}}
	?>

</select>

<?php
}} else if(isset($_GET['mfg'])) {
{
?>
<input type="text" id="printerSearch" name="printerSearch" />
<br />
<input type="hidden" id="mfg" name="mfg" value="<?php echo $_GET['mfg']; ?>" />
<?php
}
}
?>

<br />
<?php
if(!isset($_GET['mfg'])) {
{
?>
<input type="submit" value="Select" />
<?php
}}
else if(isset($_GET['mfg'])){
{
?>

<input type="submit" value="Search" />

<?php
}
}
?>

</form>

<?php 
if(isset($_GET['mfg']) || isset($_GET['printerSearch'])) {
// Pass through any images now!
while($image_present = mysql_fetch_array($image_result)){
{
?>

<img src="http://content.etilize.com/Thumbnail/<?php echo $image_present[0]?>.jpg"><br />

<?php
}
}
}
?>
</body>
</html>

Its obviously a rather botched job though, as its only going off my own memory is there anyway of speeding this querying up at all?

Its just it takes an awful long time when entering the page for it to query and display all the manufacturers, as its quite a large database (over 20mb put it like that!), the sheer volume of information in there's incredible!

Thanks and I look forward to any replies as usual, I am sure someones got a really good idea for this,

Jeremy.

dreamwest · July 14, 2010

Use MATCH AGAINST instead you dont even need to index the field

j.smith1981 · July 14, 2010

So there's no need to redo my SQL and the form page is ok?

Mostly from memory too a few references I needed but mostly remembered stuff, its cool though.

Thanks ever so much for your help, I truely appreciate it, that goes for the wild card where statement aswell?

Thanks again,

Jeremy.

j.smith1981 · July 14, 2010

It seems though when I try and submit a mfg category on the form page.

It seems though like all the products are showing, exactly what should I put in the if statement to stop the product images showing when I use the select box, can you see an error in my code?

I have tried using break; in some of the code but of course this wont execute below the line and causes an error in my code or doesnt appear at all, any help is greatly appreciated.

Jeremy.

Psycho · July 14, 2010

Here's my two cents:

First query (in readable format):

SELECT manufacturer.manufacturerid AS mfgID, manufacturer.name AS mfgName

FROM manufacturer
JOIN product
     ON manufacturer.manufacturerid = product.manufacturerid
JOIN categorynames
     ON product.categoryid = categorynames.categoryid

WHERE categorynames.name LIKE ('%printer%')

GROUP BY manufacturer.manufacturerid

ORDER BY manufacturer.name ASC

Are the category names very diverse - i.e. are they a fixed list of values or do they change constantly? If they are a fixed list, but you have several that have "printer" in the name, it might be more efficient to explicitly retrieve the records exatcly matching those values instead of using a LIKE statement that has to interpret every value.

WHERE categorynames.name IN ('Laser Printer', 'Color Printer', 'Duplex Printer')

j.smith1981 · July 15, 2010

Ah wonderful reply, thanks for that.

But my 2nd question (post rather, apologies), was why is it querying when I get say index.php?mfg=100 and I thought my code was meant to look for index.php?mfg=100&printerSearch=lc02bk

Ie when mfg and printerSearch $_GET variables are in the url, will that not work?

I appreciate your replies so far and I look forward to our next discussion.

Kind regards as always,

Jeremy.

Psycho · July 15, 2010

Your form has TWO fields named "mfg"

<select id="mfg" name="mfg">

<input type="hidden" id="mfg" name="mfg" value="<?php echo $_GET['mfg']; ?>" />

That might be causing the problem.

j.smith1981 · July 19, 2010

Here's my two cents:

First query (in readable format):
SELECT manufacturer.manufacturerid AS mfgID, manufacturer.name AS mfgName

FROM manufacturer
JOIN product
     ON manufacturer.manufacturerid = product.manufacturerid
JOIN categorynames
     ON product.categoryid = categorynames.categoryid

WHERE categorynames.name LIKE ('%printer%')

GROUP BY manufacturer.manufacturerid

ORDER BY manufacturer.name ASC
Are the category names very diverse - i.e. are they a fixed list of values or do they change constantly? If they are a fixed list, but you have several that have "printer" in the name, it might be more efficient to explicitly retrieve the records exatcly matching those values instead of using a LIKE statement that has to interpret every value.
WHERE categorynames.name IN ('Laser Printer', 'Color Printer', 'Duplex Printer')

The database changes every day, not sure exactly when it changes but as new manufacturers and things get added each day, also we dont usually get to know this, I thought this would be the best way to go.

Then search on models, going to see exactly how this reacts when I put the match part in the where clause.

Got it adding an index of fulltext on the part of the database the user queries and add in a 2nd on the select drop down see how that improves it maybe.

j.smith1981 · July 19, 2010

Use MATCH AGAINST instead you dont even need to index the field

I have added in an index field just to make it quicker anyway, thanks for that reply!

Its not doing the search instantly asif its not taking any effort to do so!

Thanks ever so much for your help though but now its been solved.

Going to see if I can speed up the rest and completely test it.

Thanks again,

Jez

j.smith1981 · July 20, 2010

Sorry to bump this once more but I am having a slight bit of a problem, probably something I am missing though.

But the 2nd search once the text box appears to query that manufacturer for images, you must put the whole code in ie the whole expression like for just a random example I have used: lc02 is what I put in, came up with nothing, then I put lc02bk and it worked like that using this code:

SELECT productimages.productid AS productImage, product.mfgpartno AS mfgpartno FROM productimages JOIN product ON productimages.productid = product.productid JOIN manufacturer ON product.manufacturerid = manufacturer.manufacturerid WHERE MATCH (product.mfgpartno) AGAINST ('$printerSearch') AND manufacturer.manufacturerid = '$mfg_selected' AND productimages.type = 'Thumbnail'

Does anyone have any other ideas to make it just use parts of that code inputted into the text box like lc02 would output all results with lc02 (i know its the least you can do when doing this type of search for full text index searching but should still work).

I look forward to your replies,

Jeremy.

Sign In

Possible Optimisation of a MySQL Query

Recommended Posts

j.smith1981

Link to comment

Share on other sites

dreamwest

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

j.smith1981

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information