logs archiveChat DB / Freenode / #mysql / 2015 / October / 10 / 1
riceandbeans
does mysql support the return keyword?
thumbs
riceandbeans: simple enough to test
riceandbeans
not exactly, but I'm going to try
thumbs
sure, it is.
riceandbeans
not with my application
mgriffin
riceandbeans: https://dev.mysql.com/doc/refman/5.0/en/return.html dont use the app to test
riceandbeans
well, I have to test and make sure the app logic works
mgriffin
yes, but that is not what you were asking
anyone using pt-stalk with galera? i set the writer server to just collect on Threads_running and the reader's to collect on global status wsrep_local_recv_queue > x
any other approaches people are using?
CaptTofu
motaka2 I _wrote_ a book of 850 pages and still have problems with SQL at times :)
thumbs
CaptTofu: you wrote a book?
mgriffin
thumbs: guessing 978-0470414644 978-0470563120
thumbs
ah, I didn't know that CaptTofu knew about httpd
CaptTofu
thumbs: two.
great experience, couldn't do it now with kids and work and remain sane
Neobenedict
hi folks. I have a table with 2 million rows of numbers that are 'ranges' for country codes. I need a fast way to find a country code from this table. this is the current query and it takes 0.48 seconds, can it be sped up?
SELECT code FROM geoip_country WHERE StartIP <= 2420920400 AND EndIP >= 2420920400
thumbs
Neobenedict: do you have an index on (StartIP, EndIP) ?
CaptTofu: so you were using 2.2 back then, I presume?
Neobenedict
thumbs: http://puu.sh/kEzFm/2b71bb4eec.png
thumbs
Neobenedict: why a screenshot
Neobenedict
because it is showing the indexes in that table
why not? :P
thumbs
Neobenedict: use SHOW CREATE TABLE instead
Xgc
Neobenedict: A simple yes or no would do.
thumbs
Neobenedict: also, show the EXPLAIN output for that query.
Neobenedict
thumbs: this is the EXPLAIN http://puu.sh/kEzKO/164d79fde9.png
thumbs
all right, the screenshots are getting tiresome. Use a paste site like pastie.org or sprunge.us
Neobenedict
what is wrong with screenshots?
thumbs
Neobenedict: I can't copy and paste from them.
Neobenedict
http://pastebin.com/mw7L3hVd
thumbs
CaptTofu: ouch, your books are expensive.
Neobenedict
thumbs: do you need the SHOW CREATE TABLE?
thumbs
!t Neobenedict idfk
ubiquity_bot
Neobenedict: Please paste your query, the EXPLAIN select.., and the relevant SHOW CREATE TABLE/s in one pastebin with the sql formatted so it is readable
Neobenedict
what do you mean by show create table?
desc table?
thumbs
Neobenedict: nope. SHOW CREATE TABLE tbl\G
Neobenedict: don't use DESC TABLE
Neobenedict
ok
thumbs
!t us paste
ubiquity_bot
#mysql: http://pastie.org or http://sqlfiddle.com/
Neobenedict
http://pastie.org/private/mgslh73p9kbwuijln2ia thumbs
thumbs
Neobenedict: ah, two range conditions. That could be a problem.
Neobenedict: how many rows does that query return?
Neobenedict
it only needs to return the first it finds. in this case, 1
I don't think it should ever return more than 1
thumbs
Neobenedict: you have no LIMIT in your sql, so it could return more than one.
Neobenedict
well, I can add that, but that won't reduce the time, right?
thumbs
Neobenedict: it's not fully using the index because of the two range conditions.
Neobenedict
okay, what I can I do about it?
thumbs
Neobenedict: out of curiosity, run EXPLAIN SELECT COUNT(*) FROM geoip_country WHERE StartIP <= 2420920400; EXPLAIN SELECT COUNT(*) FROM geoip_country WHERE EndIP >= 2420920400;
Neobenedict
https://www.pastee.org/26pjr
...oops, i've gone from pastie to pastee
thumbs
it should be O
OK
ok, this is better "using index"
Neobenedict
still taking a long time to run though.
SELECT COUNT(*) FROM geoip_country WHERE EndIP >= 2420920400 = 0.37 s
thumbs
that's fast for that many rows.
Neobenedict
SELECT COUNT(*) FROM geoip_country WHERE StartIP <= 2420920400; = 0.0003 seconds
MrAmmon
ok, I hate having to ask this question, but other than adjusting table_open_cache and table_definition_cache plus table_open_cache_instances what do you tune to avoid innodb data dictionary locks when you find yourself with more than 1 million tables in your database?
or 2 million&
thumbs
Neobenedict: one approach worth considering would be using a self-join, and using one range condition per table in the WHERE clause.
Neobenedict
thumbs: why is the StartIP query almost instant but the EndIP query takes 0.37 seconds
thumbs
Neobenedict: you need an auto-increment pk to join on, however.
Neobenedict: because of the number of scanned rows.
Neobenedict
so, it's finding it right at the start of the table
or starting from the start of the table
...any way to make it start from the end of the table for the EndIP query?
if that's the problem
thumbs
you're asking for *any* number of rows before a value, and after a value. Hence, two scans will need to take place.
Neobenedict
SELECT * FROM geoip_country WHERE EndIP >= 1 is almost instant like the startip
thumbs
Neobenedict: you should try the self-join approach I suggested - all you need to do is to add an auto-increment column, and index it.
Neobenedict
can you give me an example?
thumbs
Neobenedict: example of what?
Neobenedict
the self-join approach
thumbs
Neobenedict: FROM tbl AS a JOIN tbl AS b ON a.id = b.id
Neobenedict
so what is using b?
b.endip >= num
?
and a.startip
thumbs
Neobenedict: you would use the <= check on a, and >= on b.
Neobenedict: the problem with two range conditions on the same table in the WHERE clause is that the second won't use an index.
Neobenedict: another approach is to reorder the WHERE clause to do the most expensive range check first.
Neobenedict
so I need an ID column which is just autoincrement based upon
startip?
thumbs
thumbs
what?
Neobenedict
read up two lines
thumbs
Neobenedict: I already covered that part.
Neobenedict
did you?
thumbs
option 1) add a column to your table, use a self-join and split the WHERE logic between the two or options 2) re-order the WHERE clause to perform the most expensive range scan first
Neobenedict: I did.
Neobenedict: option 2 is faster to test.
Neobenedict: you'd need a separate index on (EndIP) for the second option
Neobenedict
thumbs: thanks for your help. ended up reading http://www.psce.com/blog/2012/06/01/implementing-efficient-geo-ip-location-system-in-mysql/ and discovering that endIP isn't needed at all
threnody
thumbs: "How We Partitioned Airbnbs Main Database in Two Weeks"
thumbs: "...one of our brilliant engineers proposed the intriguing idea of leveraging MySQL replication to do the hard part of guaranteeing data consistency..."
1 2 3 4 5 next »