|
|
Posted by Captain Paralytic on 10/05/07 08:51
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.
Navigation:
[Reply to this message]
|