| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Comments:
<0> i see..
<1> so you can do LIMIT 1000, then LIMIT 1000,1000, then LIMIT 1000,2000 etc.
<0> oh ok
<0> ah right i getyou
<1> check the SELECT statement
<1> on mysql manuel
<1> manual
<0> i'm running update though...
<2> fluffles: actually it's offset, count.
<1> i see a LIMIT on UPDATE page too
<0> ?
<0> ahok
<1> TodoInTX: yeah seeing now too
<0> not really useful
<0> doesn't say much about 1000,1000 style Limiting
<3> fluffles: 8000 rows is very small. Make sure you have the necessary indexes in place.
<0> 8000 rows is small. but the 8000 rows has 7mb of text in them
<3> akumanz: Sorry. That was to you.
<0> yeah so its not that small
<3> akumanz: It's tiny.
<0> :/
<1> well database size is relative i guess :)
<0> it has 2columns... ID & Text what should index be on anyway
<1> if you're used to huge forums then 4GB is not much i guess
<2> akumanz: both, regular btree on 'id' and fulltext on "Text".
<3> akumanz: Hopefully the ID the a primary key.
<0> hm k
<0> will set an index on ID in both tables
<0> and try the query with limit enabled
<0> hopefully server wont blow up again
<3> akumanz: Show the exact UPDATE statement you are running.
<3> akumanz: Use the pastebin.
<0> ok hold
<0> http://pastebin.com/547813
<0> ID indexed, Text fulltexted.. Should be better now ihope
<2> for this query the fulltext index isn't going to matter. But you will thank me when you try to query the against description.
<0> ?
<1> akumanz: why do you use table name everytime if only one table is involved?
<3> akumanz: This UPDATE actually succeeds? Doesn't look good.
<0> it doesnt succeed xgc. page dies
<3> akumanz: The UPDATE looks invalid.
<0> :/
<0> whats wrong with it
<1> WHERE `cubecartstore_inventory`.`tcode` = `cubecartstore_inventoryX2`.`tcode`
<3> akumanz: Not just slow. It can't run. You care referring to a second table that is not in the table list.
<1> you're referring to another table i guess
<1> :)
<3> You are...
<0> well then..
<0> how do i do what i'm trying to do
<3> akumanz: A JOIN.
<0> -_- thats what i had before
<0> hold
<3> akumanz: It's called a multitable update. You can JOIN tables in an UPDATE.
<0> http://pastebin.com/547820
<0> Look alright?
<1> looks good i guess
<0> gonna run it hold on. hope server doesnt die
<1> stroke it gently first, he'll be more cooperative :)
<0> I set LIMIT 1000 on the end.. Wheni run it next time will LIMIT 1000,1000 limit by 1k again and start at 1k?
<0> and also is the LIMIT on the actual records updated? or just records scanned :S
<1> its <offset>,<length>
<3> akumanz: You're trying to copy data around in your database? Are you eventually going to remove the X2 table/data?
<0> ok cool
<1> thus LIMIT 2000,1000 will yield 2000-3000
<0> Yes XGC correct
<1> LIMIT 4000,1000 yield 4000-5000
<0> are you suggesting I merge on tcode?
<3> akumanz: Don't limit this.
<0> ???
<0> what then. merge?
<3> akumanz: The index on ID will make a difference. That's probably enough.
<0> ok
<0> i'll run the query now
<0> affected 0 rows -_-'
<0> wtf
<3> You have a data issue.
<0> what you mean
<3> Do the corresponding select count(*), joining on ID, to see what you get.
<0> hey wtf that query i did didn't even have a join in it :/
<3> akumanz: It has a JOIN.
<0> ?
<0> no it doesnt look at it
<3> akumanz: It's an inner join.
<1> a comma is a join too
<0> oh.
<0> oh ok
<0> hm so how the heck do i select it from 2 tables
<3> akumanz: SELECT count(*) FROM t1 JOIN t2 ON t1.id=t2.id;
<0> yeah hold on
<0> 502 rows.. wtf
<0> there should be like 8000 rows :/
<3> akumanz: You only have 502 matching rows.
<0> that's not possible ...
<1> :))
<0> wait up
<3> akumanz: The engine doesn't generally lie.
<3> You've either screwed up the JOIN or you've already adjusted the data and removed some ids.
<1> unless equiped with SCO(r) Lying Machine(tm)
<0> nah
<0> it's probably right not sure why
<0> how can i search for * where tcode is not blank in table1
<0> not null?
<1> blank != null
<3> akumanz: field1 IS NOT NULL
<1> it may be an empty string
<0> oh IS
<3> akumanz: Right. An empty string is NOT NULL... except in Oracle.
<0> ok what about where tcode is Greater than 1 char
<3> akumanz: WHERE length(field1)>0
<0> 1289 matches
<0> i must have screwed up when i inserted the tcodes...
<0> but regardless you saw 502 matches.. so why did the query return 0
<0> dam this is getting lame
<3> akumanz: We only see what you write. We don't know the truth.
<0> you can't handle the truth
<3> "We can't handle the truth!"
<0> YOU CAN'T HANDLE THE TRUTH
<0> sigh
<0> time for plan C
<0> crap i dont even have a plan c yet
<4> hey
<4> how can I exclude certain data from a mysql query
<0> thats not even possible
<0> there MUST be more tcodes.
<4> like I want to exclude a couple fields from my query because its not needed
<0> dont put them in the query in the first place or dont use *
<4> well its a couple cells sorry
<0> more details then i dont know what you're on
<4> because the menu is dynamically generated from a database and my boss told me to exclude the entries from the menu but NOT to delete them from the actuall DB :\
<0> OHHHHHHHHHHHHHHHHH
<0> oy XGC i found out why theres only 1289 tcodes
<0> are you ready for the truth?
<3> I'm pretty sure I can't handle the truth.
<0> nah you can
<5> tinyint(1) is to be used for boolean datatypes, right?
<0> when i updated my product database this time, i was sposta add the tcodes. but I ran a script which only added NEW products
<0> so all the existing products didnt' get tcodes added..
<3> akumanz: Got it.
<0> ok hold on updating now
<3> newpers_: You can.
<0> YEAH
<0> Affected rows: 11747 (Query took 0.9154 sec)
<5> Xgc: is there a boolean data type?
<0> told you
<4> (akumanz): so think you can help me?
<0> yeah whats your problem..
<3> akumanz: The time isn't too bad either.
<4> i already told you
Return to
#mysql
or
Go to some related
logs:
proftpd rewrite
fglrx located with port 80
install glibc ubuntu
cubepanel filter
#gimp
how evil eat this kitten
xubuntu root password
#suse
openembedded makedevs
linux split Output file suffixes exhausted
|
|