|
|
Posted by Brian on 10/06/07 23:34
"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]
|