MySQL Forums
Forum List  »  MyISAM

Re: Performance by Selections
Posted by: Peter Brawley
Date: March 04, 2019 11:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
626
February 19, 2019 09:35AM
355
February 19, 2019 12:02PM
351
February 21, 2019 06:58AM
298
February 21, 2019 01:13PM
296
February 28, 2019 03:56PM
298
February 28, 2019 05:10PM
311
March 04, 2019 10:14AM
Re: Performance by Selections
287
March 04, 2019 11:08AM
283
March 22, 2019 06:09AM
284
March 22, 2019 09:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.