Efficient mysql queries - 【StackMirror】|java|mysql

-1

I'm trying to get a report down from the 20+ minute run time and I've tracked the bottleneck to this monster of a mysql query that uses 5 joins and 3 subqueries to return all the results in one data set. My big bottleneck so far seems to be: join the stock table on the payments table is the stock.id matches a payment.stock_id and the payment.type is this type and the payment.status is this status. I'm slowly breaking this apart into various queries and letting Java decide what records to keep and throw away but I'm wondering if there is an efficient way in mysql to get a couple of resultsets, perhaps as tables stored in memory and then query against those? Some of my result sets will be pretty big, a few hundred thousand entries in the worst cases.

Thanks in advance

java
mysql

2012-04-04 17:34
by coffeeNjava

Hundreds of thousands of rows is certainly not small, but it's really not that big, either. Regardless, you might want to post the actual query - Matt Ball 2012-04-04 17:37

i would suggest you tune the query. Use Explain to get an initial view on how the query is doing. You may identify need for a few indexes etc. Putting this filtering in application is often a bad idea in the long run - aishwarya 2012-04-04 17:40

And table definitions.. - Kieveli 2012-04-04 17:40

Building good indexes is your first line of defense. Using "explain" and optimizing your query can also be helpful. Finally, you can write a .bat task/cron script to periodically merge your raw data (from the normalized tables) into denormalized (temp) tables, and then generate your reports (or do other data mining activities) from the denormalized tables: http://dev.mysql.com/doc/refman/5.0/en/explain.htm - paulsm4 2012-04-04 17:40

@MДΓΓБДLL hundred of thousand is very small, I don't think DB really chock up until you are dealing with few tens of millions row. I deal with few million rows tables, in my local computer and its a 5 years old system, and have almost no issues. Just need to be indexed correctly. And have optimal running SQL - Churk 2012-04-04 17:43

You have some choices of create materialized view so that you can run this on a time interval, but you'll have basically a delay effect on the validity of your data. But sure is faster.

Second is index the joining columns.

Sub select are usually more expensive than a JOIN, so try to merge your subselect into a join instead.

There are a lot of SQL optimization book out there that is worth reading that deals with these questions. But without knowing your DB or schema or indexes and how the joins and subselects are. Its almost like catching a ball in the dark.

2012-04-04 17:40
by Churk

Thanks. The way things sit right now, I'm pretty much the only developer, I inherited the code, no one maintains the database and I doubt anyone ever will. Even though it's not a good long term solution, I'll probably just move the processing into the app because it seems to be doing better for the time being. My guidance from above is almost always "just put a band aid on it" - coffeeNjava 2012-04-04 19:12

Sorry to hear that, but putting bandaid on it usually means you are going to scratch your head later on again asking how to bandaid the bandaid. But its always comes down to cost to fix vs cost to ignore - Churk 2012-04-04 19:15