SLOOOOOW How do I make this quicker?

[AV1611]

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`

[fenway]

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.

[AV1611]

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.

 

[fenway]

>30 minutes? There's no way you have those columns properly indexed, even with 3 tables -- 50K records is a joke for MySQL.

[ryanlwh]

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.

[AV1611]

*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.

 

[fenway]

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.

 

 

[AV1611]

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.

 

[fenway]

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.

[AV1611]

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.

 

[fenway]

[!--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.