Re: Performance by Selections

[Peter Brawley]

The query effectively turns your Left Join into an Inner Join with ...Group By l.ord_item... Writing Inner Join instead will allow some optimisation on the header side of the join, so that's strongly recommended.

You need covering indexes on both tables, probably (ord_id,ord_date,ord_store) on the header table, (ord_id,ord_item) on the items able but you'll need to experiment. (For such tasks on significant databases and queries, there always needs to be a test machine that has a recent copy of the data).

Swapping kills performance. Your machine is seriously under-resourced for the database load you describe. I'm sure queries like the ones you posted induce swapping. For a summary of recent mem use issue in the terminal ...

free -m
/proc/meminfo

... and post the results here.

key_buffer_size is the default low value but more info is needed before we can look at suggested changes.

There's a design problem not related o mem use: money is being tracked in float columns. Yikes. Floats always return rounding errors. Customers and accountants don't appreciate it. All those columns need o be changed to decimal.