Slow results

Hi there



First off sorry if this is a long posting but I need to give all the facts
in the hope to get an answer. 2 Years ago I did a site for a client to track
online orders and run reports. One of the main reports is now going so slow
it's of no use to anybody.



I am the first to admit that I am not the best PHP and MySQL coder so I may
be missing something here or I may have just met the limit of MySQL, all
scripts and DBs are on a modem new dedicated server.



The 2 main tables are "orders" and "productslist" The orders table has
7000000 (yes that is 6 million) records and the productslist has 6000, all
search fields have been indexed, the whole DB have 25 tables



The users select the search criteria, summits the form and the below PHP
code it run , it can take upwards of 20 mins to return the results. So my
question is, is there anything wrong with my code or the way I and running
this, or have I just hit the limit of MySQL



Many thanks



Brian





$query = "SELECT p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
SUM(o.`del` ) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
SUM(o.`total_invoice_price`) AS totalinvoiceprice FROM productslist AS p
LEFT OUTER JOIN orders AS o ON ( p.prod_code = o.prod_code AND o.timestamp
>= $sdate AND o.timestamp <= $edate AND o.status <> "p" AND (status NOT IN
("u")) AND (status NOT IN ("n")) ) GROUP BY p.prod_code ORDER BY
p.prod_code";



$result = mysql_query($query,$db) or die ("Can't run getstore SQL ERROR: "
.. mysql_error () . "");

if ($myrow = mysql_fetch_array($result)) {

print "

Total number of products ordered and delivered
between $realstartdate and $realenddate for $brand

Sorry there are no matches found on your
search, please try again

Re: Slow results

Brian wrote:

> The 2 main tables are "orders" and "productslist" The orders table has
> 7000000 (yes that is 6 million) records and the productslist has 6000, all
> search fields have been indexed, the whole DB have 25 tables
>
> The users select the search criteria, summits the form and the below PHP
> code it run , it can take upwards of 20 mins to return the results. So my
> question is, is there anything wrong with my code or the way I and running
> this, or have I just hit the limit of MySQL

You could skip the join, this means you won't get the product description, you
could query this separately (store each fetched in an array, that way you can
limit refetching a description you already fetched.)

--

//Aho

Re: Slow results

Brian wrote:

> The 2 main tables are "orders" and "productslist" The orders table has
> 7000000 (yes that is 6 million) records and the productslist has 6000, all
> search fields have been indexed, the whole DB have 25 tables

Does "search fields" include the "prod_code" field? try indexing this
in both tables. Anyways, what do you mean with "search fields"? According
to the query, you should try indexes on p.prod_code, o.prod_code,
o.timestamp and o.status.


> The users select the search criteria, summits the form and the below PHP
> code it run , it can take upwards of 20 mins to return the results. So my
> question is, is there anything wrong with my code or the way I and running
> this, or have I just hit the limit of MySQL

I do not know MySQL, but I can hardly believe you hit a limit of MySQL with
this. After all, it claims itself to be a fast DBMS.


> $query = "SELECT p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
> SUM(o.`del` ) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
> SUM(o.`total_invoice_price`) AS totalinvoiceprice FROM productslist AS p
> LEFT OUTER JOIN orders AS o ON ( p.prod_code = o.prod_code AND o.timestamp
> >= $sdate AND o.timestamp <= $edate AND o.status <> "p" AND (status NOT
> >IN
> ("u")) AND (status NOT IN ("n")) ) GROUP BY p.prod_code ORDER BY
> p.prod_code";

$query = "
SELECT
p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
SUM(o.`del`) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
SUM(o.`total_invoice_price`) AS totalinvoiceprice
FROM
productslist AS p LEFT OUTER JOIN
orders AS o
ON (p.prod_code = o.prod_code AND
o.timestamp >= $sdate AND
o.timestamp <= $edate AND
o.status not in ("p", "u", "n"))
GROUP BY
p.prod_code
ORDER BY
p.prod_code
";

I transformated the query to a bit more readable form. Consider doing
this next time (also with your php code) if you want answers to your
questions.

Re: Slow results

On 5 Oct, 09:18, Boris Stumm wrote:
> Brian wrote:
> > The 2 main tables are "orders" and "productslist" The orders table has
> > 7000000 (yes that is 6 million) records and the productslist has 6000, all
> > search fields have been indexed, the whole DB have 25 tables
>
> Does "search fields" include the "prod_code" field? try indexing this
> in both tables. Anyways, what do you mean with "search fields"? According
> to the query, you should try indexes on p.prod_code, o.prod_code,
> o.timestamp and o.status.
>
> > The users select the search criteria, summits the form and the below PHP
> > code it run , it can take upwards of 20 mins to return the results. So my
> > question is, is there anything wrong with my code or the way I and running
> > this, or have I just hit the limit of MySQL
>
> I do not know MySQL, but I can hardly believe you hit a limit of MySQL with
> this. After all, it claims itself to be a fast DBMS.
>
> > $query = "SELECT p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
> > SUM(o.`del` ) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
> > SUM(o.`total_invoice_price`) AS totalinvoiceprice FROM productslist AS p
> > LEFT OUTER JOIN orders AS o ON ( p.prod_code = o.prod_code AND o.timestamp
> > >= $sdate AND o.timestamp <= $edate AND o.status <> "p" AND (status NOT
> > >IN
> > ("u")) AND (status NOT IN ("n")) ) GROUP BY p.prod_code ORDER BY
> > p.prod_code";
>
> $query = "
> SELECT
> p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
> SUM(o.`del`) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
> SUM(o.`total_invoice_price`) AS totalinvoiceprice
> FROM
> productslist AS p LEFT OUTER JOIN
> orders AS o
> ON (p.prod_code = o.prod_code AND
> o.timestamp >= $sdate AND
> o.timestamp <= $edate AND
> o.status not in ("p", "u", "n"))
> GROUP BY
> p.prod_code
> ORDER BY
> p.prod_code
> ";
>
> I transformated the query to a bit more readable form. Consider doing
> this next time (also with your php code) if you want answers to your
> questions.

It always amazes me when I see queries written like the OP's posted
here. I often take one look and give up.

Re: Slow results

"Captain Paralytic" wrote in message
news:1191574284.139144.113410@r29g2000hsg.googlegroups.com.. .
> On 5 Oct, 09:18, Boris Stumm wrote:
>> Brian wrote:
>> > The 2 main tables are "orders" and "productslist" The orders table has
>> > 7000000 (yes that is 6 million) records and the productslist has 6000,
>> > all
>> > search fields have been indexed, the whole DB have 25 tables
>>
>> Does "search fields" include the "prod_code" field? try indexing this
>> in both tables. Anyways, what do you mean with "search fields"? According
>> to the query, you should try indexes on p.prod_code, o.prod_code,
>> o.timestamp and o.status.
>>
>> > The users select the search criteria, summits the form and the below
>> > PHP
>> > code it run , it can take upwards of 20 mins to return the results. So
>> > my
>> > question is, is there anything wrong with my code or the way I and
>> > running
>> > this, or have I just hit the limit of MySQL
>>
>> I do not know MySQL, but I can hardly believe you hit a limit of MySQL
>> with
>> this. After all, it claims itself to be a fast DBMS.
>>
>> > $query = "SELECT p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
>> > SUM(o.`del` ) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
>> > SUM(o.`total_invoice_price`) AS totalinvoiceprice FROM productslist AS
>> > p
>> > LEFT OUTER JOIN orders AS o ON ( p.prod_code = o.prod_code AND
>> > o.timestamp
>> > >= $sdate AND o.timestamp <= $edate AND o.status <> "p" AND (status
>> > NOT
>> > >IN
>> > ("u")) AND (status NOT IN ("n")) ) GROUP BY p.prod_code ORDER BY
>> > p.prod_code";
>>
>> $query = "
>> SELECT
>> p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
>> SUM(o.`del`) AS totaldel, SUM(o.`total_cost_price`) AS
>> totalcostprice,
>> SUM(o.`total_invoice_price`) AS totalinvoiceprice
>> FROM
>> productslist AS p LEFT OUTER JOIN
>> orders AS o
>> ON (p.prod_code = o.prod_code AND
>> o.timestamp >= $sdate AND
>> o.timestamp <= $edate AND
>> o.status not in ("p", "u", "n"))
>> GROUP BY
>> p.prod_code
>> ORDER BY
>> p.prod_code
>> ";
>>
>> I transformated the query to a bit more readable form. Consider doing
>> this next time (also with your php code) if you want answers to your
>> questions.
>
> It always amazes me when I see queries written like the OP's posted
> here. I often take one look and give up.


Thanks for your comments guys, sorry the query was so hard to read, will
try to remember that if i need to ever post again.

Thanks

Brian

Re: Slow results

You better!!!!! lol
"Brian" wrote in message
news:hYUNi.24667$aN2.22792@newsfe2-gui.ntli.net...
> "Captain Paralytic" wrote in message
> news:1191574284.139144.113410@r29g2000hsg.googlegroups.com.. .
>> On 5 Oct, 09:18, Boris Stumm wrote:
>>> Brian wrote:
>>> > The 2 main tables are "orders" and "productslist" The orders table has
>>> > 7000000 (yes that is 6 million) records and the productslist has 6000,
>>> > all
>>> > search fields have been indexed, the whole DB have 25 tables
>>>
>>> Does "search fields" include the "prod_code" field? try indexing this
>>> in both tables. Anyways, what do you mean with "search fields"?
>>> According
>>> to the query, you should try indexes on p.prod_code, o.prod_code,
>>> o.timestamp and o.status.
>>>
>>> > The users select the search criteria, summits the form and the below
>>> > PHP
>>> > code it run , it can take upwards of 20 mins to return the results. So
>>> > my
>>> > question is, is there anything wrong with my code or the way I and
>>> > running
>>> > this, or have I just hit the limit of MySQL
>>>
>>> I do not know MySQL, but I can hardly believe you hit a limit of MySQL
>>> with
>>> this. After all, it claims itself to be a fast DBMS.
>>>
>>> > $query = "SELECT p.prod_code, p.description, SUM(o.`ord` ) AS
>>> > totalor,
>>> > SUM(o.`del` ) AS totaldel, SUM(o.`total_cost_price`) AS
>>> > totalcostprice,
>>> > SUM(o.`total_invoice_price`) AS totalinvoiceprice FROM productslist AS
>>> > p
>>> > LEFT OUTER JOIN orders AS o ON ( p.prod_code = o.prod_code AND
>>> > o.timestamp
>>> > >= $sdate AND o.timestamp <= $edate AND o.status <> "p" AND (status
>>> > NOT
>>> > >IN
>>> > ("u")) AND (status NOT IN ("n")) ) GROUP BY p.prod_code ORDER BY
>>> > p.prod_code";
>>>
>>> $query = "
>>> SELECT
>>> p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
>>> SUM(o.`del`) AS totaldel, SUM(o.`total_cost_price`) AS
>>> totalcostprice,
>>> SUM(o.`total_invoice_price`) AS totalinvoiceprice
>>> FROM
>>> productslist AS p LEFT OUTER JOIN
>>> orders AS o
>>> ON (p.prod_code = o.prod_code AND
>>> o.timestamp >= $sdate AND
>>> o.timestamp <= $edate AND
>>> o.status not in ("p", "u", "n"))
>>> GROUP BY
>>> p.prod_code
>>> ORDER BY
>>> p.prod_code
>>> ";
>>>
>>> I transformated the query to a bit more readable form. Consider doing
>>> this next time (also with your php code) if you want answers to your
>>> questions.
>>
>> It always amazes me when I see queries written like the OP's posted
>> here. I often take one look and give up.
>
>
> Thanks for your comments guys, sorry the query was so hard to read, will
> try to remember that if i need to ever post again.
>
> Thanks
>
> Brian
>
>

Re: Slow results

SquidHead wrote:
> You better!!!!! lol
Please do not top post.

Re: Slow results

Post removed (X-No-Archive: yes)