Select became slow when &gt; 2 row data

Select became slow when > 2 row data

Anon

I found a big problem in Mysql... not sure which part i did wrong.

I need to select more 18 table at one time... and it works fine when i have only one row of data... but when i have two rows, i found that it become really slow when i select more than 13/14 table at one time.... here is the sample query... hope someone has the same experience share with me..

SELECT * FROM program, condition, condition_fn, condition_imp, condition_lpurpose, condition_mi, condition_occupancy, condition_pnty, condition_sassets, condition_semployment, condition_sincome, condition_units, condition_vassets, condition_vemployment, condition_ptype, condition_vincome, condition_state, state WHERE program_type='12' AND program_second = '2' AND program_disable = '0' AND condition.program_id = program.program_id AND condition_maxla >= '200000' AND condition_minla <= '200000' AND condition_maxltv >= '80' AND condition_maxcltv >= '80' AND condition_mincs <= '650' AND condition_disable = '0' AND condition_fn.condition_id = condition.condition_id AND condition_fn = '2' AND condition_imp.condition_id = condition.condition_id AND condition_imp = '1' AND condition_lpurpose.condition_id = condition.condition_id AND condition_lpurpose = '1' AND condition_mi.condition_id = condition.condition_id AND condition_mi = '2' AND condition_occupancy.condition_id = condition.condition_id AND condition_occupancy = '1' AND condition_pnty.condition_id = condition.condition_id AND condition_pnty = '0' AND condition_ptype.condition_id = condition.condition_id AND condition_ptype = '1' AND condition_sassets.condition_id = condition.condition_id AND condition_sassets = '1' AND condition_semployment.condition_id = condition.condition_id AND condition_semployment = '1' AND condition_sincome.condition_id = condition.condition_id AND condition_sincome = '1' AND condition_units.condition_id = condition.condition_id AND condition_units = '1' AND condition_vassets.condition_id = condition.condition_id AND condition_vassets = '2' AND condition_vemployment.condition_id = condition.condition_id AND condition_vemployment = '2' AND condition_vincome.condition_id = condition.condition_id AND condition_vincome = '2' ANd condition_state.condition_id = condition.condition_id AND condition_state.state_id = '005' AND condition_state.state_id = state.state_id AND state.state_disable = '0' GROUP BY program.program_id

fleymingmasc

Hmm... this would probably be a good time to use a TEMPORARY table (supported in MySQL version 3.23 and later). Use several INSERT statements to gradually build a result set.

Joins (whether explicit or implicit) take up a LOT of server resources. If you can keep your concurrent joins to a minimum, you and your server admin will be much happier (you're probably maxing out the cpu anytime you run the above query). Even though the use of temporary tables will be slow, it probably won't be as slow as the above query.

Also, check your indexes to make sure that any search or join criteria are indexed in every table.

zkent

That query is a MONSTER! I created on today that I tought for sure would bring the world to a standstill, but you've got me beat!

Besides carefully checking that your query is syntactically and logically correct, make sure your MOST RESTRICTIVE conditions are made up front to save the database effort.

As FleyMingmasc says, try breaking the query up in logical chunks, if possible, and dump the results to a temporary table. It could help isolate some possible logic errors if the queries are smaller and easier to deal with.

Zach

Anon

Thanks for reply.... Hey Fley... any side explain about the temp table huh? i usually create a temp table manually... for example.. tabl_a_tmp.... thne insert and delete it while process.

This is my second site i built for financial searching and opering web base software. the first one is a little simpler than the one i'm trying to build.

By the way, i think i should share something with you guys.

Witht eh problem i describe above, I I tried to indexes as many as possible in every single field that i used. This sovle the problem by selecting this large query from database with 2 rows of data. But when i goes to 3 rows... it died again.

and.. the worse part coming... after th search is processed, i'll see my IE keeps running without result. And i can't do any others query... especially inser or update, this might destroy the table that i'm trying to insert or update, and i need to delete the table. After i delete the particular table, i need to restart the computer... restart mysql doesn't work to help this problems... and it will kills the connection to mysql.. anyone can explain to me?

So i restart my linux for more than 10 times... which i runs named, sendmail... and other serivces with the same server..

I've stucked at this problem for two days.. no improvement made.

Really need some expert help.... and i think my knowledge about computer is too low... that's what the reason....

anyway... thanks both of you....

Anon

Hi,

 I think that you need to look at the database design in more detail.  Is there a reason that you have everything normalized to this level of effort?  The reason why I say this is that I have seen many developers build sites / applications to 3NF specifications for no good apparent reason.  

 You should build a normalized database model if you need that level of distinction/granularity in your information.  If you do not, denormalize some of the tables and allow for some repeating fields.  Database servers like mySQL can handle redundencies in data much easier than processing complex queries with n joins inline the way you displayed.

 Just my $.02

mwilson

Hey,

I agree with some of the ideas and comments posted here, but one thing I noticed which could be slowing down the server considerably (depending on server resources etc), is if you are querying roughly 18 tables at once, do you really need to return all the fields from all 18 tables in your query record set? this seems to me to be quite a strain as i'm sure that all these aren't necessary. If they are then follow the fine examples given by others posted to this thread with optimizing you're query and such.

Matt

fleymingmasc

Gevork is right... denormalization is the way to go here. You might want to consider upgrading to a DBMS that supports triggers, and use the triggers to enforce consistency with the denormalized fields. Let's say you have a table named "parent" that has a one-to-many relationship with a table named "child". "Parent" has a field whose value is important to all its related rows in "child", so you create a field in "child" that duplicates that data in "parent". Then you create a trigger on "parent" such that when this field is altered, it is altered in the "child" table as well.

This is how we've optimized a number of resource-hog queries where I work.

Anon

Thanks Gevork.. Here is the detail of this database.

I'm building a mortgage program search engine for a lender bank by myself... feel a little scared sometimes(if this program works?)... which is very tough to me. I need to make sure this database could handle the growth in the future.

We sell different type of PROGRAM. Each PROGRAM might have different CONDITION, which i need only one return... the PROGRAM_ID. It doesn't matter i select * or select only PROGRAM_ID, it still slow. Each time of search i need to make sure this PROGRAM return 0 or more than 0 in CONDITION. One PROGRAM normally could have 20-50 CONDITION.... and in each CONDITION i need to verified each field is met.

If every field was met, then the return is > 0, ELSE is 0. I tried to denormalize it at the very beginning... but the number of data growth into 30,000 rows for each CONDITION, which makes me consider to normalize it.

As my query shown, My first is to identify is that the right program to choose.. from PROGRAM_TYPE, is the program a second loan, and is this program being disabled.

THEN i move into CONDITION. I need to met the Loan Amount, Loan To Value, Combined Loan to value, Credit Scored AND is this CONDITION beign disabled.

If every of the CONDITION met, then i move into the third normal form... which is all the sub table under CONDITION...

I have tried to put all sub table under CONDITION into one table.. which we called it here CONDITION DETAIL... and I've tested the speed of select dropped a lot when rows of data goes to 50,000, which is only one or two condition data entered. Then i worried that MySQL might not be able to handle this much data... especially when it goes to 10 million... where every field is need for search.

Here is my plan for today... I'll try to denormalized some sub table under CONDITION, cut down the number of table into 8-10, then create some multi-field indexes...

PLEASE advised me all my friend.. and I could send you my ER-Daigram if you are interested on playing around this database with me.

Waiting to hear from you guys...

Anon

Hi Matt...

Could you explain somemore about the trigger stuff? I'm still confused... or do you have any web site could refer me to?

I've do some calculation. Each CONDITION will have maximum 93,063,168*2 rows of data if i combined all sub-table together. How should i handle this data if i'll have 20-50 condition each program, and we have totally 40-50 program?

Kent

Anon

I posted the message under Matt Wilson.. sorry about that..

Could you explain somemore about the trigger stuff? I'm still confused... or do you have any web site could refer me to?

Kent

Anon

Hi,

 Please send over schema information to me.  Either send in pure text form with an accompanying jpg file so that I can visually see it as well.

Regards