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