AV1611 Posted December 30, 2005 Share Posted December 30, 2005 STDS=5000 records NEWPRCSTD=50,000 records DTE=500 records. Would it be quicker to pare down NEWPRCSTD with DTE, then do a second query on the result with STDS? Or, some elaborate JOINS (Don't know how to write it...) update stds , newprcstd , dte Set stds.`Sales Unit Price`=newprcstd.PRICE_32, stds.Reference=newprcstd.REFRNC_32 where newprcstd.INVDTE_32=dte.DTE AND newprcstd.PRTNUM_32=dte.PRTNUM_32 AND newprcstd.PRTNUM_32=stds.`Part Number` Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/ Share on other sites More sharing options...
fenway Posted December 30, 2005 Share Posted December 30, 2005 That really depends on the order in which the parser decides to JOIN the tables (since it's a multi-table join implicitly). I guess you could check with EXPLAIN, though to be honest, I've never used that on a UPDATE statement before, but it should work. If the parser get it's "wrong" -- that is, it narrows the table down incorrectly -- you can always force the order with STRAIGHT_JOIN. Post there result of the EXPLAIN, or if for some reason that doesn't work, you can post the EXPLAIN of the corresponding SELECT query. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10494 Share on other sites More sharing options...
AV1611 Posted December 30, 2005 Author Share Posted December 30, 2005 I killed the query after 30 minutes... I queried the first table against the second, then the result of that against the third all in 15 minutes... Thanks anyways... Boss was breathing down my neck (It's end of year), didn't have time to 'speriment [!--quoteo(post=331655:date=Dec 30 2005, 04:57 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 30 2005, 04:57 PM) 331655[/snapback][/div][div class=\'quotemain\'][!--quotec--] That really depends on the order in which the parser decides to JOIN the tables (since it's a multi-table join implicitly). I guess you could check with EXPLAIN, though to be honest, I've never used that on a UPDATE statement before, but it should work. If the parser get it's "wrong" -- that is, it narrows the table down incorrectly -- you can always force the order with STRAIGHT_JOIN. Post there result of the EXPLAIN, or if for some reason that doesn't work, you can post the EXPLAIN of the corresponding SELECT query. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10496 Share on other sites More sharing options...
fenway Posted December 31, 2005 Share Posted December 31, 2005 >30 minutes? There's no way you have those columns properly indexed, even with 3 tables -- 50K records is a joke for MySQL. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10502 Share on other sites More sharing options...
ryanlwh Posted December 31, 2005 Share Posted December 31, 2005 there must be something wrong with your tables. are you storing a lot of data? i once copied a table to a new server in about 20 mins, and that table has 8 MILLION records. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10503 Share on other sites More sharing options...
AV1611 Posted January 3, 2006 Author Share Posted January 3, 2006 *BLUSH* Actually, I don't have any indexes applied to any of the tables... (Let the yelling begin...) [!--quoteo(post=331773:date=Dec 31 2005, 01:05 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 31 2005, 01:05 AM) 331773[/snapback][/div][div class=\'quotemain\'][!--quotec--] >30 minutes? There's no way you have those columns properly indexed, even with 3 tables -- 50K records is a joke for MySQL. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10538 Share on other sites More sharing options...
fenway Posted January 3, 2006 Share Posted January 3, 2006 Ouch...5000 records * 50,000 records * 500 records + temporary index on-the-fly = very slow indeed! I'd be interested to know how fast it runs once you've added the necessary indices. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10544 Share on other sites More sharing options...
AV1611 Posted January 5, 2006 Author Share Posted January 5, 2006 I must admit I'm not an expert when it comes to indexes. Do I create indexes with ways of making unique pairs by grouping fields? is that the strategy? More indexes doesn't always mean faster, right? am I right that the more matches a query has, the less effect the indexes have? Can you specify which indexes to use from within the query? [!--quoteo(post=332691:date=Jan 3 2006, 04:13 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 3 2006, 04:13 AM) 332691[/snapback][/div][div class=\'quotemain\'][!--quotec--] Ouch...5000 records * 50,000 records * 500 records + temporary index on-the-fly = very slow indeed! I'd be interested to know how fast it runs once you've added the necessary indices. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10628 Share on other sites More sharing options...
fenway Posted January 5, 2006 Share Posted January 5, 2006 The "strategy", at least at a first approximation, is to index any columns using in "locating" the records to be used in a query -- UID would be the obvious example, and is almos always the PRIMARY KEY, which has a unique index. In your case, all the columns used to join the tables (the ones in the ON clause that determine the JOIN condition) should be indexed -- newprcstd.INVDTE_32, dte.DTE, newprcstd.PRTNUM_32, dte.PRTNUM_32, and stds.`Part Number`. Not having seem your table design, it would be hard to suggest any others at this time, but I'm sure that there would be many. Too many indices can simply make adding records take more time -- since it was to update each index -- and can take up storage space as well, which almost makes the lookup take a little bit longer too (which is why sometimes people only index the first few characters of a column). However, without an index, the DB has to do a complete table scan, which is simply absurb for any table of reasonable length. I highly recommend that you read the [a href=\"http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html\" target=\"_blank\"]relevant refman page[/a], and then post any other questions you might have. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10639 Share on other sites More sharing options...
AV1611 Posted January 6, 2006 Author Share Posted January 6, 2006 Thank... I guess then it is important to remove indexes created for specific queries when they are no longer needed... Thank, I'll re-read the index stuff in the refman again... [!--quoteo(post=333622:date=Jan 5 2006, 02:12 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 5 2006, 02:12 PM) 333622[/snapback][/div][div class=\'quotemain\'][!--quotec--] The "strategy", at least at a first approximation, is to index any columns using in "locating" the records to be used in a query -- UID would be the obvious example, and is almos always the PRIMARY KEY, which has a unique index. In your case, all the columns used to join the tables (the ones in the ON clause that determine the JOIN condition) should be indexed -- newprcstd.INVDTE_32, dte.DTE, newprcstd.PRTNUM_32, dte.PRTNUM_32, and stds.`Part Number`. Not having seem your table design, it would be hard to suggest any others at this time, but I'm sure that there would be many. Too many indices can simply make adding records take more time -- since it was to update each index -- and can take up storage space as well, which almost makes the lookup take a little bit longer too (which is why sometimes people only index the first few characters of a column). However, without an index, the DB has to do a complete table scan, which is simply absurb for any table of reasonable length. I highly recommend that you read the [a href=\"http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html\" target=\"_blank\"]relevant refman page[/a], and then post any other questions you might have. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10673 Share on other sites More sharing options...
fenway Posted January 6, 2006 Share Posted January 6, 2006 [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]I guess then it is important to remove indexes created for specific queries when they are no longer needed... Not necessarily -- if the table is large (which presumably is why you have an index to begin with), then the overhead of re-indexing the table over and over again results in a greater performance hit that simply leaving the index intact. I would caution you against dropping any index unless you can strongly justify it (e.g. a FULLTEXT index just used a single, infrequent report). Most of the time, for a simple one-column index, it's worth having it -- just make sure you don't have any indicies that are _never_ used. Quote Link to comment https://forums.phpfreaks.com/topic/3123-slooooow-how-do-i-make-this-quicker/#findComment-10696 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.