iaj_Hi folks! Is there some way of "fallback" in MySQL eg. if SELECT foo FROM bar where … yields nothing return SELECT default FROM bar ?
jkavalikiaj_, you could try with UNION, but that won't be nice. Better to not do it that way.
flyingis it possible to restart mysql master or will I break the replica?
jbrueheflying: If you restart the master, the slave will lose connection and automatically try to set it up again. This should work automatically. If you prefer the soft way, simply issue "stop slave", then restart the master, then issue "start slave".
flyingokok
flyingif I need to change from ROW to STATEMENTS based replication?
flyingis it the same steps to do?
flying*step
iskorptixupdate tradepoints set replace(address, 'https://www.linkedin.com/company-beta/','') where type = 'LinkedIn';
iskorptixwhat is wrong with this query ?
iskorptixnevermind!
jbrueheflying: Yes, should also work without intervention. The format is set on the master, the slave just accepts it. But why do you want to change in that direction? IMO, row format is the safer choice.
salleflying: you can change binlog format without restarting the server with SET GLOBAL
salleflying: It will not affect the format for the active connections though
flyingand the binlog automatically will be changed to STATEMENT format?
sigdkhello, how can I see the encoding of my DB?
sigdkthe character-set that is being used by a table for instance
madseabearhello
madseabearwhat is faster ? LIMIT n OFFSET x or WHERE id > x LIMIT n ?
sallemadseabear: Without ORDER BY your question does not make sense
sallemadseabear: You don't know wich rows you will get each time
sallesigdk: There is default character set for each database, each table and then each column can have its own character set which can differ from table, database and server default
sallesigdk: So you better clarify what are you asking about
sigdksalle, does the column character set override the one of table?
sallesigdk: Of course it does
sigdksalle, how can I see those variables? table and column character set?
sallesigdk: SHOW CREATE TABLE or query Information_schema
sigdksalle, thanks
madseabearsalle: ok then what is faster SELECT * FROM table ORDER BY id LIMIT n OFFSET x or SELECT * FROM table WHERE id > x ORDER BY id LIMIT n ?
salle!t madseabear tias
ubiquity_botmadseabear: Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok
madseabeari tried it and i got the second being faster
madseabearbut then why is everyone suggests using the first one
sallemadseabear: You should know it is faster :)
sallemadseabear: Who is everyone?
madseabearwell i've been searching for bulk selects and i've found only one result with the WHERE clause
madseabearand kinda every paginator i've seen does the limit offset
sallemadseabear: I doubt you will find anyone here in #mysql who would suggest that the first one is faster
sallemadseabear: Well you select different things with first and second query :)
madseabearok then, is there a case in which first one is indeed faster ?
sallemadseabear: It is not about being faster, but about selecting exactly what you need
madseabearactually i just noticed
madseabearit should be SELECT * FROM table WHERE id > x LIMIT n
madseabearwhat i want to do is like i said bulk select
sallemadseabear: You have two queries which select different rows and you are asking which one is faster. Does such question really make sense to you?
madseabeareventually i need the whole table
madseabearsalle: i've corrected the query
sallemadseabear: Once again you are talking about two queries which select different rows and you are asking which one is faster.
madseabeari must be stupid then
madseabearSELECT * FROM table ORDER BY id LIMIT n OFFSET x -> select the next n rows ordered by id starting with x+1 row
madseabearSELECT * FROM table WHERE id > x LIMIT n -> select n rows where id is greater than x
sallemadseabear: Exactly
madseabearhow are they not the same
Xgcmadseabear: There's no guarantee that the id sequence is without holes.
sallemadseabear: SELECT MAX(id), COUNT(*) FROM your_table; :)
sallemadseabear: Here is an example: http://sqlfiddle.com/#!9/c4637d/1
sallemadseabear: You will get same result only if there are no gaps in the sequence of ids which is special case. In general case you are selecting different rows
madseabearyes, you're right
madseabeari forgot to specify
madseabearwhen i do these bulk selects, i save the last id between sets
madseabearso in your example i know i have to use WHERE id > 7
madseabearmy mistake using n and x
madseabearso i start with SELECT * FROM table ORDER BY id LIMIT n OFFSET 0 or FROM table WHERE id > 0 LIMIT n
madseabearthen after each query i save the last id of the result and replace 0 in second query
madseabearand of course increase offset by limit
madseabeardoes that make any sense now ?
madseabearsalle: so it would be like this http://sqlfiddle.com/#!9/c4637d/6
madseabearand even there i can see the where is faster
sallemadseabear: Isn't it logical? Think about it
sallemadseabear: The bigger the OFFSET the slower LIMIT .. OFFSET will be considering indeed that the column is indexed
madseabearwell i know it's logical from my point of view at least, i was just trying to understand why is everyone using the limit offset
verywisemanhow can i restrict access to "a specific mysql user" for "a specific system user" ? , for example , there are 2 mysql users : my1 & my2 , and there are 2 system users : sys1 & sys2 , I want to limit sys1 to only login to my1 user also limit sys2 to only login to my2 user
pilahello, would it be correct to add an index over these fields (history.artnr,history.verkaeufer,items.preis_aenderung,history.created_at) for this query: http://pastebin.com/UymcrAm3
vilvahi
vilvawhat is the practical resource limitation why it is not advisable to make a database per user design, since from data isolation point of view that approach would be quite natural
jkavalikpila, sounds quite good if your version can use index condition pushdown (shows like "using index condition" in the EXPLAIN)
pilamhh
jkavalikpila, if not then one of the columns is redundant, because preis_aenderung is used as not_equal and created_at for ordering (so range scan)
jkavalikpila, actually swap the last two columns
pilai just wonder how to create an index over 2 tables
jkavalik= and order by first, then the preis_aenderung can be used as last for ICP with inequality check, now preis_aenderung is before created_at so it "breaks" optimal use for ordering
jkavalikah
jkavaliksorry, did not notice these were from two tables
jkavalikyou cannot
pilajkavalik thats my explain http://pastebin.com/BpqNkEyk
jkavalikpila, two indexes - history (verkaeufer, created_at) and items(artnr, preis_aenderung)
jkavalikyou need/can have ONE index per row in explain
pilamy table items has 15 indexes in total, is this normal?
thumbspila: it depends
thumbspila: do you know what a composite index is, and how it is matched from the left?
pilanope :/
thumbspila: read about that then.
jkavalik!t pila index order
ubiquity_botpila: According to our chadmaynard (don't say his name), indexing order is: join, where, sorts, group by
jkavalik!t pila indexing 101
ubiquity_botpila: https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/
pilathx
MarcoPauHi, I copied one of my blogs onto another hosting with another domain but, althou I changed the wp_options table (siteurl and home) it's still going to the old website. What am I missing? thanks!
archivistMarcoPau, how about that this is not a blog support place
MarcoPauarchivist: I am hacking in the database. wrong channel for this kind of support?
salleMarcoPau: We speak SQL here. If you say you have such and such tables and some SQL query does this, but you expect that then maybe we could help.
jkavalikMarcoPau, if you do not know HOW to change some table content, then we can probably help; if you do not know WHAT you need to change, then thats WP problem, not (My)SQL
MarcoPauI guess I will ask the WP community then. I doubt there was any problem in changing the wp_options table
MarcoPauthanks all the same. have a good day
pilahey i want to add levenshtein function to my mysql server, but when i run the query i get an error for each line of the query https://falseisnotnull.wordpress.com/2013/05/18/levenshtein-and-levenshtein_ratio-functions-for-mysql/
snoyesdid you change the delimiter?
pilano i just copied it
snoyesYou need to change the delimiter so the parser knows that you're not done yet when it sees the first ';'
pilaok worked
snoyesSee https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html
pilamhh
pilabut the function still is not added
snoyesfunctions belong to a database; they are not global to a server. Did you set a default database firsT?
thumbsa/50
mfilipehey guys! do you know what is wrong here? docker exec mysql sh -c 'exec mysql -uroot -p"pass1" -e "CREATE USER 'docker_aggregator'@'%' IDENTIFIED BY 'pass2'"'
mfilipei know it is a docker command but the problem is with the mysql commmand
thumbsmfilipe: what's the error?
mfilipeERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% IDENTIFIED BY pass2' at line 1
cidicso timezones are often listed as GMT/UTC +/- Xhours but things like daylight savings may shift the offset so when you are listing a dropdown of timezones with offsets do you use the current offset that takes into account daylight savings or the base offset? also php seems to not always give the correct offset for timezones
thumbsmfilipe: sounds like your shell is munging the %
introspectrmfilipe, escape the % character
snoyesor the '
mfilipelet me see...
mfilipedocker exec mysql sh -c 'exec mysql -uroot -p"change123" -e "CREATE USER 'docker_aggregator'@'\%' IDENTIFIED BY 'mud3r123'"'
mfilipesame error
snoyescidic: php issues should go to ##php
cidicthis is mostly a timezone question. I already asked in php.
Woetmfilipe: you are using single quotes in the -c parameter, you cant use them again inside the query without escaping
thumbscidic: so you want to know how to handle TZ offsets with sql?
Woetmfilipe: you should try the query first in MySQL directly. if that works, its not a #mysql question.
thumbsWoet: oh, good catch
cidicthumbs: see the first question I asked
mfilipeWoet, thanks man, it worked directly in mysql
mfilipei will try #bash
mfilipethumbs, introspectr, snoyes: thanks guys
aeiouI have a query stuck in sending data (375 seconds): https://hastebin.com/gurosagixo.sql
aeiouThere are many of these happening concurrently. I also have output from InnoDB status - but not sure how to interpret it correctly
snoyesaeiou: pastebin the SHOW CREATE TABLE for sales_flat_order and sales_flat_order_address
thumbscidic: what data type are you using?
snoyesand sales_flat_order_grid
cidicthumbs: is it better to handle tz offsets with php or slq?
sisveaeiou, "sending data" seems badly named, it involves disk access and probably large reads. https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
cidicsql?
aeiousnoyes: https://hastebin.com/nunequkoza.sql
sisveaeiou, i'm _guessing_ that you're having several large table scans going, and your machine is low on iops
aeiousisve: but the `where` condition lists the primary key for the row and the joins are done with the PK too?
sisveaeiou, sorry, I just quickly glanced at your query. I'm guessing an EXPLAIN on that statement would clarify what indexes it would use. And as snoyes mentioned, seeing the table structure of those tables would help.
aeiousisve: table structure added to the above
aeiounow with explain too: https://hastebin.com/omuwileguh.sql
Woetaeiou: i think you need a few more columns
Woetit doesnt quite fill my screen
thumbscidic: it depends. Sometimes I store the GMT date and the TZ offset in another column
thumbserr
aeiouWoet no problem, I will add that to my ‘todo’ list
cidicthumbs: by tz offset you mean numerical offset?
thumbsyes
thumbshold on, I'm a bit distracted.
sisveaeiou, could there be some other query that is blocking this?
sisveI'm not sure if the state would show if the query is blocked by another query.
snoyesaeiou: indexes are in good order. Minor note - billing_address_id and shipping_address_id are int, matched to an entity_id which is an int unsigned.
sisveaeiou, ignore what i said about blocking; it seems there are separate states for "waiting for lock_type lock"
aeiouLet me paste the output from ‘SHOW FULL PROCESSLIST’ and’SHOW ENGINE INNODB STATUS’
cidicthumbs: but the offsets change with things like DST
thumbscidic: GMT times mostly solve that problem.
cidicthumbs: how so?
cidicoh you mean just displaying GMT times and not tz adjusted times
thumbsgah, UTC. I have to focus
thumbscidic: sorry, let me wrap up my other things. I clearly can't focus here.
cidicnp
aeiouDocument exceeds maximum length. -_-
aeiousnoyes: sisve I presume all of the ‘show engine innodb status’ should be shared, not selectively?
sisveaeiou, probably.
aeiouok. This is the innodb monitor, not including the —TRANSACTIONS— part which I will upload separately https://hastebin.com/utopunoxer.sql
aeiousnoyes: sisve: transactions: https://hastebin.com/osugemecik.md
inoasIs this (above line 19) the correct way to sort a group by? https://gist.github.com/inoas/cc407f476e4da92cf27a354faccdd7c5#file-sort-inside-group-sql-L19
inoaslike the sorting should happen before grouping
snoyesaeiou: ah, maybe turn off the query cache
snoyesinvalidating the query cache can be really expensive
aeiouAnd you can see that is likely the issue from what I’ve pasted?
snoyeslook how many of them are waiting for query cache lock
snoyesand every single insert, update, or delete has to go through the cache and invalidate any entries for that table, even if it's clearly about different rows.
aeiousnoyes: https://hastebin.com/abucigotul.sql
aeiouthis is what I was most worried about
aeiouit doesn’t seem to be waiting for query cache lock?
snoyesStill, I'd turn off the query cache and set the size to 0 and see what happens.
snoyesIf you don't like the results, you can always turn it back on.
aeiouWill that not negatively impact read performance?
aeiouMy concern is this happens during high capacity launches (like 2000 customers on the website)
aeiouNot much room for error!
snoyeswell, let's see SHOW GLOBAL STATUS LIKE 'Qcache';
snoyesSHOW GLOBAL STATUS LIKE 'Qcache%';
aeiousnoyes: https://hastebin.com/kohacurelo.rb
snoyesSo on average, for each query put into the cache, it's only served from the cache twice.
aeiouThat’s surprisingly low.
snoyesYou've inserted 10 million queries, but only 23 thousand are still in there
sisve... or 10 million queries did never hit, and one query was hit 20 million times
snoyesor the 23 thousand in the cache were each hit 1000 times.
snoyesSo, I still think you should just turn it off.
snoyesIf you have good reason to believe that you have a couple of very expensive queries against tables which never change, then you can later turn it back to DEMAND instead of ON and use SQL_CACHE in just those few queries.
aeiouOk so this will require some testing for sure, perhaps we can spin up a replica environment to give it a test.
aeioustruggling to understand this output fully. https://hastebin.com/abucigotul.sql does this mean the query is running but all 4 tables it is trying to use are locked?
cidicaeiou: I believe so
snoyesthat query would acquire some locks, most of which are shared read locks. If you enable the lock monitor, or examine the lock structures in performance_schema, you can see more detail.
aeiouI wish I understood this more, is this learnable from the documentation?
aeiousnoyes: all of the transactions that say ‘waiting for query cache lock’ say “not started” - can that really be impacting the queries that say ‘sending data'?
snoyes'sending data' doesn't necessarily mean sending it back to the client. It includes sending it between internal structures.
snoyesI don't expect that the ones which say "waiting for lock" are impacting the ones saying "sending data", but rather that both are affected by the same thing
aeiouSo for none of the queries that are waiting for query cache locks I can’t see any of them relating to sales_flat_order(grid/address)
salleaeiou: Disable query cache and you will not see that "waiting" again
sallesnoyes: It would be great if 'sending data' is renamed to something more informative or at least less confusing like 'doing something'
snoyesYes, lots of instrumentation and better names would be great.
snoyesIt would be great if a syntax error didn't instruct you do "consult the manual"
snoyesIt would be great if a problem with SSL would tell you _anything_ of use.
aeiousnoyes: salle: so I will defintely go and try that in a non-production environment, I’m just struggling to connect what I’m seeing with the error that is occuring. If this is query cache related (potentially) then I don’t understand how all the ‘waiting for query cache’ are like 3 second queries (tops) but the ‘sending data’ for the insert into the grid is ACTIVE 368 sec inserting
ZoderUckHello
ZoderUckHello i need a simple left join with terms, http://pastebin.com/wGM2fqw4
salleZoderUck: What would "with terms" mean? :)
salleZoderUck: Better use derived table instead of that dependant subquery in WHERE clause
salle!t ZoderUck derived table
ubiquity_botZoderUck: SELECT a.name, b.name, IFNULL(b.cnt, 0) AS cnt FROM tblname AS a LEFT JOIN (SELECT name, COUNT(*) AS cnt FROM tblname2 GROUP BY name) AS b ON a.name = b.name;
delboy1978uk1ping ZoderUck
ZoderUckow hi :)
ZoderUckpong your self
ZoderUcka derived table,
ZoderUcklet me inspect, what ubiquity bot says
delboy1978uk1ok im looking
ZoderUckdelboy, it had to be a sijpmle left join, it works with relation table
ZoderUckif i remove themax date
aeiousnoyes: I pasted a shorterned version of that first paste. I was limited to characters. This is the full version: https://paste.fedoraproject.org/paste/JmSWUxvn4LvUVAgcrhcFJF5M1UNdIGYhyRLivL9gydE= from line 2760 onwards
aeioudo you still think it is the query cache after looking at the above?
delboy1978uk1can you put some "" around the MAX select brackets?
delboy1978uk1see if that helps
ZoderUck" quotes ?
delboy1978uk1well a date is a string, so yeah
ZoderUcki ow ok. yes.
ZoderUckow yes ok.
salleZoderUck: .. FROM wp_posts as p INNER JOIN (SELECT post_parent, MAX(post_date) AS md GROUP BY post_parent) AS tmp ON p.post_parent = tmp.post_parent AND p.post_date = md LEFT JOIN wp_term_relationships ....
ZoderUckDelboy, it did run yes Thanks, but zero rows
ZoderUckis that good ?
delboy1978uk1salle to the rescue ZoderUck :-D
ZoderUckow ok, wait salle :) let me try yours
delboy1978uk1I have no idea what's in your DB, so cant help all that much :P
ZoderUckaha ok :) cool it is a typical wordpress database
snoyesaeiou: I think it's a good possibility, and it's a simple thing to try. I don't care to dig through the InnoDB lock code enough to prove it.
ZoderUcksalle: aha, but something is not correct, syntax error at line 9, http://pastebin.com/zJx72ZqV
ZoderUckwhy is sql so hard, why\
aeiousnoyes: You’re right it is simple to turn on/off, but doing that on production is quite risky (if it goes wrong during a high capacity launch we’re talking real money), so it first requires setting up test environments - which makes it not so simple after all… any way to twist your arm to prove it? :-)
snoyesaeiou: buy a support contract with Oracle?
aeiousnoyes: how many figures are they?
sallesnoyes: :)
salleZoderUck: What error exactly?
delboy1978uk1ZoderUck: closing bracket right at the end
ZoderUckthe error is uuh let me see wait :)
ZoderUcki use Heidi sql client :) it is a windows program
salleZoderUck: I rarely paste here something which can be copy/pasted and work :)
salleZoderUck: You have to think about it a little
salleZoderUck: Hint: FROM ...
ZoderUckSQL Erro 1064, you have made an error in your SQL syntax, check the manual and make sure you compy with the MariDB sandard version, for the right syntax to use near '' at line 10
hdonhi all :) what does "ci" mean at the end of each collation in mysql?
snoyesaeiou: https://www.mysql.com/tcosavings/
snoyeshdon: case insensitive
ZoderUcki realy dont know what MariaDB is
thumbshdon: case insensitive.
ZoderUckI have create user Merry
delboy1978uk1its mysql for hippies or something
ZoderUckaha it is the Mysql version right? or the storage standard used by my Mysql server
ZoderUcki use xampp
delboy1978uk1xampp sucks
delboy1978uk1dont use xampp
ZoderUcko
delboy1978uk1use a vagrant vm, or a docker vm
ZoderUcklol
aeiousnoyes: 1 figure too many!
ZoderUckdelboy let me guess you are fan of nginx ?
aeiousnoyes: could have likely swung 4 figures with the customer!
delboy1978uk1i use apache, but have used nginx too
hdonahhh thanks snoyes , thumbs
ZoderUckoow ok, nothing wrong with xampp . realy
delboy1978uk1yes there is
delboy1978uk1are you on windows?
snoyesaeiou: everything in life is negotiable. Call sales.
ZoderUckso i need to add FROM at the end of the sql statement ?
salleZoderUck: https://mariadb.com/products/why-mariadb
delboy1978uk1ZoderUck: install virtualbox, and vagrant. then go here puphpet.com
salleZoderUck: You SELECT <something> FROM <some tables> :)
delboy1978uk1is this because Oracle bought over MySQL?
delboy1978uk1that everyone now uses MariaDB?
salledelboy1978uk1: It didn't actually. Oracle bought Sun after Sun bought MySQL :)
delboy1978uk1ah. lol
salledelboy1978uk1: "everyone" is overstatement
ZoderUcksalle oow okayy :) lol
delboy1978uk1yes i know, but more and more places are now using it over mysql proper
salleZoderUck: I didn't bother to read all of your query to see what table you are selecting from in that subquery
ZoderUckok, i have now Every direved table must have its own alias, wait i paste it
ZoderUckhttp://pastebin.com/uFBjrz5Q
ZoderUcko no magic answer
salledelboy1978uk1: I know and I am happy about that, but I don't think it is appropriate to talk about it here in #mysql
salleZoderUck: SELECT .. FROM (SELECT ...) AS something
ZoderUckuh, inner join ( new select here )
salleZoderUck: Yep. You can use derived table instead of base table in table reference part of query
salleZoderUck: The result of SELECT is always a table so it can be used as such
ZoderUckuhhmn salle, uhm
ZoderUck:) can make it work. glad it is not my professional job.
ZoderUckwhat is the solution :)
ZoderUckrealy sorry, i ask straight for te solution
ZoderUcki realy need to convert a wordpress db it is non profit and thes guys are rought to me already
ZoderUckcome on guys , please otherwise they will hire an expensive professional
ZoderUckcould be a chance for me to shiinne as a pro
snoyesyou refer to 'tmp' on line 12, but where is 'tmp' defined?
snoyesit should be on line 11 after the closing parenthesis
snoyesp.s. what organization is this?
aeiouIs there a way to see which queries are being most frequently ‘hit’ in the cache in mysql?
ZoderUckwho me ?
snoyesaeiou: you can run a query and determine if it was served by the cache, but there isn't a way to examine what queries are in the cache (it doesn't actually store the query itself, just a hash of the query and some information about what tables it used)
aeiousnoyes: ah ok. I’m wondering if there is a way I can leave the cache on for the entire website, just disable it for the isolated part causing an issue (basically selects to sales_flat_order and sales_flat_order_grid/address)
bewbsdb dump speed wouldn't be affected by cpu limitations right?
bewbsthat's a disk thing
bewbssince it's not doing anything complex
bewbsi'm running a backup utility and it's taking pretty long to dump everything
aradapilot@bewbs well, cpu does get used for that, but it's more common to be bound on disk. are you doing a logical or physical backup? physical is much faster, though logical is more customizable
aradapilotmost common tools for each would be mysqldump for logical and xtrabackup for physical
aeiousnoyes: presuming that the reason the query is slow is infact it waiting for query cache. Would I see the same benefit by adding SQL_NO_CACHE to queries related to sales_flat_order(_grid/address)? i.e if i don’t cache those queries but query caching is still on will it still improve performance (because there are no queries to flush out of the cache on change)?
snoyesaeiou: it may help some.
snoyeswrites still have to invalidate the cache
ZoderUcki have a left join question
aeiousnoyes: thanks
ZoderUckhello somebody thanks
ZoderUckhttp://pastebin.com/uFBjrz5Q
snoyesZoderUck: I already answered that.
snoyesthat's also not a left join, that's an inner join. But that's irrelevant.
ZoderUckwhat
ZoderUckow
ZoderUckships i still not solved it. http://pastebin.com/iykpfLsu
snoyesconsider what "p.post_date = md" is supposed to mean
ZoderUcksnoyes, yes haha.
ZoderUckaha i see. you are right
ZoderUckshould be a field
ZoderUckview.field_x = view2.field_b
ZoderUckships i cant make it work
ZoderUckany of you do want to help ?
mustmodifyIf I have tables A and B, and A references B, and I want to select all entries where A doesn't have a B, or has a B, or where there is a B and no A, can I do that with one outer join? Or do I need a union?
mustmodifySeems like I'll need a union.
ZoderUckI have table B and A and i dont want a union
snoyesmustmodify: you will, because MySQL does not have FULL OUTER JOIN.
mustmodifysnoyes: good, just wanted to make sure I wasn't missing something. Thanks.
hdonhi all :) with explain, why might possible_keys be null while key is not null?
snoyesa covering index that must be fully scanned
hdonoh, hmm... that isn't very intuitive to me... can you offer any insight? should i have been able to guess this?
snoyeshttps://dev.mysql.com/doc/refman/5.7/en/explain-output.html says, "The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table."
snoyesThen a few sentences later, "It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan
snoyesSo, there is no index which can help narrow down the number of rows to consider. However, there is an index which contains all the columns of interest, and it's faster to read that than to read the actual table itself.
snoyesand I see there is a grammatical error on that page
hdonohhh thanks snoyes
hdonhuh... wow it really does slow down my query to read the table and not just the index
pilaHello i want to make a toplist for 2 categories with 1 query like: left side is top 10 articles from seller #1 and right side is top 10 articles from seller #2, how can i create a performant query for this task?
pilait needs to be orderable
passage!t pila about group top n
ubiquity_botpila: To find the top N per group check out -> http://thenoyes.com/littlenoise/?p=36
hdoni'm trying to estimate query time by using LIMIT and extrapolating linearly. in my case i believe this is sound. except that it looks like various i/o caches are hurting me. any advice?
mustmodifyif a subquery results in no rows, will that cause the whole row to be excluded?
sallemustmodify: What do you mean?
sallemustmodify: What kind of subquery?
sallehdon: Extrapolating linearly? Why linearly?
mustmodifyI'd be inclined to do a full example and it's probably not worth it at this point, I can just go the long way around faster than putting together the example.
mustmodify:(
sallehdon: And what kind of query? GROUP BY query with LIMIT nn is unlikely to depend much on the nn number
mustmodifyBut I appreciate your willingness.
mustmodifyI kind of realized as I was typing it that it wasn't the most clear question.
sallemustmodify: It it is query in WHERE clause it depends on expression in which it is used. If it is derived table it depens on whether it is used in inner or outer join. If it is dependant subquery it depends ... etc.
sallemustmodify: WHERE x IN (SELECT ..) will obviously return empty set if subquery returns empty set, but SELECT x, (SELECT ..) AS y FROM ..; is totally different story
mustmodifyAh, I see.
mustmodifyYeah, it was the second.
mustmodifyI had a ton of outer joins and I figured grouping would be painful, so I was going for a subquery.
mustmodifyBut it turned out grouping was easy (assuming the data is right. :) )
mfilipei'm getting error with this command: docker exec mysql sh -c 'exec mysql -uroot -p"change123" -e "CREATE USER $'docker_aggregator'@'%' IDENTIFIED BY $'mud4r123'"'. error: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@% IDENTIFIED BY' at line 1
mfilipedo you know what i'm doing wrong?
thumbsmfilipe: did you escape the single quotes yet?
mfilipethis is the raw command... what i'm running on the terminal
hdonsalle, no grouping
mfilipewhich change do you suggest?
thumbsmfilipe: also, $'docker_aggregator' looks wrong
hdonhttp://ix.io/oNn -- i must be really stupid because i'm missing something here
hdonwhy is my type index and not eq_ref?
hdoni've got a unique index on that column. i've reduced my problem to only two tables and a total of three columns, only two actually involved in the join.
aradapilottype mismatch
aradapilotalso, no primary key on foo
hdonaradapilot, please forgive my ignorance, but why do i want a primary key on foo? i want to get every row in that table.
hdonohhh... the type mismatch is on the character encoding?
hdonthanks so much aradapilot :)
carcrashhello, I am trying to import a table from a CSV file. I have it all working properly except for the date columns. I am trying to get MySQL to recognize the date format MM/DD/YYYY
carcrashdetails here:
carcrashhttps://hastebin.com/rupamoluye.sql
carcrashanyone know how I can achieve this?
Xgccarcrash: If the input needs to be adjusted slightly, use the SET clause for that.
carcrash Xgc thanks. I just found an article about that. reading now :)
carcrashugh. just found out i am stuck on mysql v 5.1 ---- released a *decade* ago.
carcrashthis makes things interesting...
FishPencilAre there any guidelines as to when to create a new table for values vs hard coding them? Something like the race of a person. Should that be an integer id for the "races" table, or should it be a string that contains the actual text for the race?
carcrashSo, what am I doing wrong here? just trying to get the date selected from that format: http://i.imgur.com/aG4rIeS.png
Xgccarcrash: You can test that in a simple SELECT DATE_FORMAT(...);
snoyesyou don't want date_format there, you want str_to_date
Xgccarcrash: SELECT DATE_FORMAT(NOW(), ...);
snoyesFishPencil: any time there's a chance you might spell it wrong, it should be a separate table.
Xgccarcrash: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
carcrashsnoyes, Youre right. Thanks
passagecarcrash: date_format() takes a valid mysql datetime input. str_to_date() changes stringliteral into valid mysql datetime format
carcrashsorry guys, I had the wrong function
passagecarcrash: in the future, please don't paste images. use hastebin and post text that can be copy and pasted
passagecarcrash: easier for helpers
snoyesFishPencil: in theory you could make it an enum instead of a separate table, but that's often more hassle than it's worth, especially for something that might change when Norwegian-Americans decide they are their own race.
imachuchuquick question, is there an easy way to tell from looking at a client if it's master is using row or statement based replication if the client is running in mixed mode? (besides shutting down the client, switching it to row based, starting it and seeing if it breaks)
FishPencilSo that sounds like a "yes, that should be another table". Are there any guidelines for this? Something like "When you have two or more options you should be using a table to associate to"
jqHow can I bulk update all my db's to set everything to have a default value of NULL?
snoyes!t me normalization
ubiquity_botsnoyes: http://mysqldump.azundris.com/archives/20-Nermalisation.html and http://goo.gl/2X5B4 and some here http://www.keithjbrown.co.uk/vworks/mysql/
snoyesFishPencil: ^
snoyesimachuchu: by "looking at a client" you mean "looking at a slave"?
snoyesyou can use mysqlbinlog to examine the relay logs (or the slave's binlogs, if it has log-slave-updates enabled) to see whether it contains statements or rows.
imachuchusnoyes: yeah, sorry
snoyesor, from within a client connection, https://dev.mysql.com/doc/refman/5.7/en/show-relaylog-events.html
imachuchuok, I'll take a look at the relay logs to see what it says
imachuchuI'll go try all three. Thank you snoyes!
FishPencilsnoyes: I feel like you could go really overboard with this. I also have information like the persons address state, should there be a "states" table?
FishPencilsnoyes: I read your links, and a lot of time they normalize because they have to, not because they could
snoyesFishPencil: That's a good question. I would just use the state abbreviations, but you might have a different opinion based on if you need to support other countries
snoyesor if you think Puerto Rico will become a state soon
educr0wHello, I have a table with id+name+value, in name I could only have 2 names, is like a history, name1+value and name2+value, I want to get the lastone of both, something like "select * from table where name=name1 orderby id desc" and the same with name2, is possible to do it in just one sql query?
educr0wI forgot limit 1
passage!t educr0w about groupwise max
ubiquity_boteducr0w: http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html
educr0wthank you passage :)
passageyw
ExternalGHey, is there anyway to log queries coming in from something like a php script? Here is my script http://pastebin.com/PwvegYZ1
passageExternalG: general query log
passage!m ExternalG server logs
ubiquity_botExternalG: See http://dev.mysql.com/doc/refman/5.7/en/server-logs.html
XgcExternalG: Didn't I tell you about the documentation in #sql?
passageheh
passagebusted
ExternalGno you didnt
ExternalGyou just said "see query logs" or something
ExternalGafter telling me to do what I asked how to do
XgcExternalG: : Not true. If you google for the exact string I gave you, you get: https://dev.mysql.com/doc/refman/5.7/en/query-log.html
XgcExternalG: Have you ever tried google?
ExternalGSorry, dont automatically associate google with "see:
ExternalGI assumed it some sort of pretense to a link or command you were gonna enter
XgcExternalG: If you don't know about a subject, try searching the documentation. If you don't know where to find the documentation, try a search engine.
XgcExternalG: I wasn't trying to make at difficult at all. That exact search string does exactly what you asked.
passagegreat, we spend a million man-hours and a trillion dollars to share all of human knowledge, and someone chooses not to avail themselves
passageACTION goes to google those invented numbers
Xgc"Xgc> ExternalG: See mysql enable query logs"
ExternalGI have multiple my. ini files using xammp, including small, medium, large and huge
ExternalGWhat one do I use, bigger is better right? ;p?
passageExternalG: the default system server values have generally been too small, yes. but anytime you change those you need to monitor swap usage to see if you've overstepped your system
passageExternalG: only you know the system you're running
ExternalGi have no idea what ur trying to say, sorry
passageExternalG: I'm not 'trying' to say anything. I'm talking about system configuration. if that's a mystery to you, you need to do some reading
Hunter79Anyone willing to help me with a PHP to mysql script for payment via pp? MSG me
thumbsHunter79: for help with php, ask ##php
Hunter79I need more help on the sql side, msg me
thumbsHunter79: don't do that. Ask your sql questions here.
Hunter79Not looking to ask questions, looking to pay someone to do it for me
thumbsHunter79: then you have the wrong channel. Ask a freelance channel.
passageACTION tries to think of a worse experience than writing php -> paypal api. draws a blank, even after having done it.
konradosMorning. This is about terminology. Say we have this scheme: table Users, then tables "Posts" and "Whatever" with FKs to Users, so they are directly related to Users. And we have another scheme - table Users, then table Posts (with FK pointing to 'Users') and then Comments, with FK to Posts. Same number of tables. But ... now I'm looking for the right term, the "complexity" is different, i.e. in the first case it's the "horizontal"
konradoscomplexity and in the second one, it's "vertical"? Or maybe, it's about the "depth" of relations? How to name it? The second scheme is more.... what?