Jump to content


Photo

SLOOOOOW How do I make this quicker?


  • Please log in to reply
10 replies to this topic

#1 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 30 December 2005 - 04:39 PM

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`


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 December 2005 - 09:57 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 30 December 2005 - 11:28 PM

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) View Post[/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]


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 December 2005 - 06:05 AM

>30 minutes? There's no way you have those columns properly indexed, even with 3 tables -- 50K records is a joke for MySQL.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 31 December 2005 - 06:11 AM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#6 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 03 January 2006 - 02:49 AM

*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) View Post[/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]


#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 January 2006 - 09:13 AM

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.


Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 05 January 2006 - 04:33 PM

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) View Post[/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]


#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 January 2006 - 07:12 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 06 January 2006 - 11:54 AM

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) View Post[/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]


#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 January 2006 - 06:39 PM

[!--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...[/quote]

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users