logs archiveChat DB / Freenode / #mysql / 2015 / November / 22 / 1
jak2000
done
thanks
snoyes
cazorla19: just ask the question
BlaDe^: a full table scan does not inherently need to create a temporary table, and even if it needs a temp table it won't fill it with the entire row, just the fields needed.
BlaDe^
snoyes: I inherited a bit of a mess. THere's a query that does select x from foo left join bar on bar.x = foo.x where bar.x is null;
Xgc
jak2000: http://sqlfiddle.com/#!9/86c6d/9
mdev
I have millions of records, divided into 5 different cities, is it faster to put all under 1 table with type int indicating which city or to create 5 seperate tables with their own data
snoyes
BlaDe^: ok
BlaDe^
then it runs a loop to delete all of x from foo. problem is, there's 376m rows - it appears the disk controller caches it frequently
it averages 7 seconds but has taken up to 1000 seconds (and timed out)
i'm trying to figure out why the sh*t it is designed this way, but looking for any quick wins I could make
snoyes
and you've tried just making it delete with that join?
BlaDe^
you mean delete from foo where x in (select ...) ?
snoyes
I mean DELETE foo FROM foo LEFT JOIN bar ON...
BlaDe^
i haven't, do you think that'd make a big difference?
snoyes
it'd be an awful lot faster than doing 376m separate deletes
mdev
yeah try auto committing
BlaDe^
it does 1000 at a time, but also I don't think that rows get removed from bar too frequently
mdev
or manual
via transactions
lot less disk writes'
BlaDe^
it's the select I want to optimize tho -- the delete is fine. It's finding the rows to delete that's slow
Xgc
jak2000: http://sqlfiddle.com/#!9/86c6d/11
mdev
what i'd do with code is this, i'd add extra entry to the table indicating if they need to be deleted, i'd then write sql that searches and sets that entry if they match, then i'd write a seperate sql that DELETE from's where that entry matches
both using manual commits so that the update and the delete writes are dramatically lessened
it'd be blazin
BlaDe^
mdev: totally agree, I need to figure out the application side and why it is designed so poorly
snoyes
What's the advantage to touching each row twice?
BlaDe^
none
but touching 1000 rows for deletion isn't a big deal, it's fast
finding that 1000 rows takes on avg 7 seconds
snoyes
got an index on the join condition fields?
BlaDe^
yes sir
Drexl
what privilege should i give to someone just to be able to read stuff but not change/create etc ?
thumbs
Drexl: SELECT
Drexl
ty
glowdemon1
Hi.
I have an ID column with auto increment. I have two tables containing new and old IPs. I noticed that my new IPs started with the ID of 256 while the last IP was still at 77, what's causing this?
tl:dr my auto_increment column is sometimes jumping with a random number
Xgc
glowdemon1: show create table tblname\G
glowdemon1: Auto_increment does not jump randomly. It might not be increasing by 1, but it won't be random.
glowdemon1
It jumps randomly yes, sometimes by 2, sometimes by 8
Xgc
glowdemon1: You just aren't aware of every time it increases.
salle
glowdemon1: Auto_increment does not guarantee consequtive values
glowdemon1: If you are using InnoDB what you describe is normal behaviour
glowdemon1
http://www.hastebin.com/vogexapanu.sql if you still need this
It doesn't need to be accurate
Xgc
glowdemon1: You don't always see every time it increments. Those values aren't always represented in the database as inserted rows.
glowdemon1
But what if it jumps by 500k and I can't insert more rows because I'm over the smallint type?
salle
glowdemon1: It happens when you have more than one session inserting rows at the same time
glowdemon1
Just a exagerated example
I see
Naktibalda
glowdemon1: that would only happen if you had 500k rolled back inserts
thumbs
if 500k transactions fail, you have bigger problems
Naktibalda
or if someone did INSERT INTO t VALUES (500000)
glowdemon1
probably
Xgc
glowdemon1: Take a breath and describe the actual system, with all the connections that might touch this table.
glowdemon1
Well, it's supposed to serve as a visitor counter.
Xgc
glowdemon1: Also, it would be interesting to note: SELECT MAX(id) FROM group_stats_ips;
glowdemon1
I'm not relying on the auto_inc ofcourse, but I was scared that one day the number jumps so big I wont be able to insert more rows
salle
glowdemon1: That day you will have to ALTER to change the id to INT
Xgc
glowdemon1: Pick a reasonable type.
salle
glowdemon1: With such small table it wouldn't be a big deal
glowdemon1
I see
Thanks for the explanation
Bye
Drexl
can i put a wildcard name in a view so it works with any table?
salle
Drexl: No wildcards in table names
fattest
I'm getting this error "This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available" how do I fix this? http://i.imgur.com/7WvhimE.png
threnody
fattest: what is generating that error? doesn't sound like mysql
« prev