Optimising Script

[John Jupp]

Hi all.

We're still playing with our script and should iron out our newest bug hopefully very soon.

At present a user may look for a specific url by clicking on a merchant listing and a datafeed of that merchants products is displayed on that page set aside to promote the merchant.

What we need to iron out is where the script reads the compiled feed of all merchants for which we have datafeeds and displays all the tens of thousands of pages on the index page so that you may search by keyword.

It takes too long to upload the page because the script is searching the entire database. Suggestions please on optimising display and search results?

The test url is www.personalhomeshopping.co.uk

[futureweb]

Ha ha me too

test URL
http://www.yomobiles.co.uk/search.php

[dmorison]

What SQL are you using for that homepage search?

Specifically; what is your table structure and what indexes do you have defined; plus what SQL "LIMIT" parameters are you using. That should help us to help you optimise what you're doing.

There's no need for it to take that long, even once you've got millions of products in your database; a paged select should be more or less instant.

[futureweb]

mines quite basic just an experiment really - the main lag is te image func grabs - where possible (jpg /png) images and creates local thumbs on the fly if the local image exists it displays it else it creates it by copying the remote then thumbnailing it.

not using any limit and using only a single field to perfom the search. the db layout is identical to affiliatefutures tab file out put.
I would normally use a stored proceedure ASP and SQL but my php is a little slack and so the above is work in progress.

[Donk]

John asked me to reply

MySql 4.0.24
PHP 4.0.24

The relevant section of PHP code is :

$row=$ProdsPerPage*$page;

$query= "SELECT * FROM products";
for ($i=0;$i<$ProdsPerPage;$i++)
{
$query1 = $query." ORDER BY Name LIMIT ";
$query1 .=$row+$i;
$query1 .=", 1";
$result = mysql_query($query1) or die("Select Query failed : " . mysql_error());
$line = mysql_fetch_assoc($result);
// Html to dispslay comes here
}

I've found the problem: I was restructuring the database this afternoon and forgot to index the field "Name";

It works a bit quicker now!

bob

[dmorison]

Hi Donk;

Yes - indexing your name field would certainly help; but it looks from your code as if you're making $ProdsPerPage separate queries to your database instead of selecting all the products you want to display in one go.

If you want to display 10 products page; you should be making a query something like this:

SELECT * FROM products ORDER BY name LIMIT 0,10

You can then loop through the results using

PHP Code:

while($line = mysql_fetch_assoc($result))
{
  // html to display goes here
} 


Then, for each subsequent page; you simply increase the offset by ((Page-1)* ProdsPerPage). Using the variable names from your code; your script would look something like this:

PHP Code:

$offset = ($page-1)*$ProdsPerPage;
$query = "SELECT * FROM products ORDER BY name LIMIT $offset,$ProdsPerPage";
$result = mysql_query($query1) or die("Select Query failed : " . mysql_error());
while($line = mysql_fetch_assoc($result))
{
  // html to display goes here
} 


Hope this helps!

[Donk]

Thanks for the advice I've made the changes.

You can see the results here .

<Just a personal moan> :

If these companies want us to sell their products why do they supply such **** datafeeds ? On page 1 of the test.html are products from Harrods with no data in the name field. Indeed there are some with just a part number and a price. On 118golf there are duplicate records and CDJungle is just that!! over 50% of the images are missing and the price they supply is the RRP not their selling price.

<end moan>

(Note to John I've uploaded products.php to your "com" site you will have to propogate it to all your other sites.)

bob

[John Jupp]

hehehe. Cheers Bob and cheers too DMorison.

Bob. Bet you're glad you're doing this now. Welcome to the world of affiliate marketing. You're a damned good coder and it's good to have you on board.

Propogating.