|
|
Posted by SquidHead on 10/07/07 15:29
You better!!!!! lol
"Brian" <brianNOSPAM@nrwp.co.uk> wrote in message
news:hYUNi.24667$aN2.22792@newsfe2-gui.ntli.net...
> "Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
> news:1191574284.139144.113410@r29g2000hsg.googlegroups.com...
>> On 5 Oct, 09:18, Boris Stumm <st...@informatik.uni-kl.de> 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
>
>
Navigation:
[Reply to this message]
|