dwqqhello guys, i want to ask if i can merge those queries into one. http://viper-7.com/iewa8f
ChulbulI have a table that contains: 22393603 Records. Query as shown in pastebin link is returning 91919 records in 90 seconds. http://pastebin.com/SceM3thJ . How to speed it up.
dwqqanyone here pls?
anthonymNope, we're all on holidays.
dwqqoh :/
dwqqwe work today :P
dwqqhttp://viper-7.com/iewa8f if anyone can help me to merge those 3 queries into 1
no_gravityIs there a way to simulate data? Something like "SELECT a FROM (3,1,2) ORDER BY a" and get:
anthonymdwqq something like this (untested, probably errors everywhere): SELECT `tasks`.`custid` as cid, `pelates`.`Επώνυμο` as LastName, pelates`.`Όνομα` as Name, sum(`poso`) as poso from `economics` where `custid`=:id and flag=0 - SELECT sum(`poso`) as poso from `economics` where `custid` IS IN(SELECT `tasks`.`custid` as cid inner join `pelates` on `pelates`.id=`tasks`.custid where
anthonym`sunolo`!=0) and flag=1
anthonymremove where `custid`=:id
anthonymbasically give the query the list of customer ids and use an IS IN
anthonymor IN
anthonymmaybe paste up a schema and I'll play with it and show you
no_gravityanthonym: Whom are you talking to?
dwqqanthonym: like this http://viper-7.com/x4ZCEf
dwqqwith union all but i need 2 columns
dwqqor the difference between them
delboy1978ukwhen i do show processlist; it only shows part of the query in the Info column. how can i see the full query?
salledelboy1978uk: You can't
salledelboy1978uk: Not in processlist
delboy1978uksalle: ok thanks. dont have permission to get logs or anything, so i just need to crank up my complaining
lhambleyGiven a table with `order_id, user_id, order_date` I'm trying to find the highest order_id for a given order date. order_date is a datetime with 1s precision. Problem is I have some orders where two orders for a given user_id and I need to ensure that it's the "max(order_date)" (incase of having > 2, tie-break by taking the largest order_id) –– is something like this possible without massive amounts of
lhambley backflips ?
sallelhambley: GROUP BY
lhambleyyou mean don't use MAX, but use GROUP BY and then so a sub-table query thing ?
sallelhambley: MAX() is aggregate function. Aggregate functions primary purpose is to be used with GROUP BY
sallelhambley: Try: SELECT user_id, MAX(order_id) FROM your_tbl GROUP BY user_id; and then think
lhambleysalle thanks, that's exactly what I'm doing: - my question is more in the direction of, incase `order_date` has two identical values for a given user_id (two orders in the same instant), how is that tie broken, is the pkey (order_id) used to take the youngest record ?
sallelhambley: What tie are you talking about?
lhambleyhow does MAX() behave when two values (for the same user_id, (group by)) have teh same exact value ?
lhambleye.g SELECT user_id, MAX(order_date) AS max_order_date FROM user_product GROUP BY user_id;
sallelhambley: Try it
lhambleyit looks to be random
sallelhambley: Pretty much yes
sallelhambley: If you CAN define that as random :)
lhambleyso that's my question, how can I do something like `MAX(order_date, order_id)` if … at all
lhambleye.g tie-break using a 2nd column
sallelhambley: When you have to choose the greatesf of 2 and 2 the greatest is 2. It is not random at all
sallelhambley: What would that mean>
sallelhambley: You can GROUP BY more than one column if that's what you want
lhambleyit would mean incase there are two rows [123, 456, <2016-12-30-13:13:00>], [789, 456 <2016-12-30-13:13:00>] (order_id, user_id, order_date) that I would treat the 2nd row as newest (bigger pkey) even though the dates I'm MAX()ing on are identicle
sallelhambley: So you want MAX(order_id) :)
sallelhambley: Wasn't that what I told you to do?
lhambleyhah, unfortunately not exactly, order_id in my real table is a nonsense hexed string
lhambleyso I have to SELECT * FROM orders INNER JOIN (SELECT user_id, MAX(order_date) AS max_order_date FROM orders GROUP BY user_id) ON ......
lhambleywhich is already a backflip more than I wanted to have to do here
lhambleybut fair enough :)
lhambleybut good point, about maybe just using the pkey :S
lhambleyresult numbers differ by ~1% so that looks pretty solid, actually
lhambleyhrm, sorry for making you take the long way round with that
Nightelfis it possible to have an inconsistent dump if i tried to dump a database while an application is using it ?
NightelfI mean, i have an application running all the time, when task x occures it executes a,b,c queries, so, if i executed a dump while query a in progress which means b and c haven't been executed yet, i would get a dump without b and c. which means, inconsistent data, am i correct?
untitledHi. How would you write this https://hastebin.com/ehubowayiq.sql using the old syntax?
salleuntitled: What old syntax?
salleNightelf: If the dump is taken without --single-transaction it can be inconsistent indeed
untitledthe one with no LEFT JOIN ON statements
untitledcommas instead
salleNightelf: That's if all your tables are using InnoDB engine
salleuntitled: You can't
salleuntitled: Comma join is inner join. It can't describe outer joins
untitledI'm just trying to get the latest value (latest date) grouped by and join a table into this mess
untitledthis is what I got so far, but it doesn't work properly
untitledthe table I'm joining to itself is portf_position(portfolio_id, security_id, amount, for_date)
untitledand the separate table I want to join is security with all the security data I want to display
NightelfYes salle, using single-transaction but i don't see how that would help in such situation. i will try to improve my above example, when task x is executed it executes a,b,c queries, a query takes 1 second to finish it's execution, b and c takes 100 ms to finish their execution. when the execution process is started and a is in progress state, i executed a mysql dump with --single-transaction, now all incoming changes (b and c in this example)
Nightelfwill not become part of the dump which would result in inconsistent dump, correct?
salleNightelf: Nope
salleNightelf: You take backup at particular point in time. With --single-transaction it is consistent regarding this particular point in time when it was taken
salleNightelf: What happens after the backup does not affect its consistency
salleNightelf: It seems you have problems understanding transaction isolation. The I from ACID definition
salleNightelf: Uncommited changes don't affect consistency of the data snapshot visible to other transactions
Nightelfsalle, i think there's a miss understanding, i get your point. also it's kind of contradicting with my example, please re-read it again
salleNightelf: Nope :)
Nightelfa should not exist without b and c. when the dump is created during a's execution b and c were not pushed to the execution process and will not exist in the dump
salleNightelf: Let's say you have 3 interleaved transactions. They finish at different times. Let's name these t1, t2, t3. Then you have backup taken at point in time between t1 and t2
salleNightelf: Say it is t1.2 at t1.2 the data is consistent and it sees all the changes comitted by tx1, but it knows nothing about t2 and t3 and more important it should not know anything about them
Nightelfsalle, yes, that's exactly what i meant
salleNightelf: Even if these two transactions started before the one which is comitted at t1.
salleNightelf: Well ... then you agree with me the backup snapshot is consistent
salleNightelf: Dump
salleNightelf: The dump gets committed changes only. Running transactions don't matter because it is not known whether they will commit or rollback.
salleNightelf: If the dump includes changes by active transactions it will be inconsistent by definition
salleNightelf: All of this again makes me believe you don't understand transaction isolation
Nightelfsalle, ah, everything is clear now!
salleNightelf: Consider following timeline: t1 -> tx1: START TRANSACTION; t2 -> tx2: START TRANSACTION; t3 -> tx1: INSERT INTO t (id) VALUES (123); t4 -> tx2: INSERT INTO t (id) VALUES (234); t5 -> tx2: COMMIT; t6 -> mysqldump --single-transaction; At this point the table t contains only id=234 because this is the only change which is comitted.
Nightelfright, one more question about binlogs, i don't really know much about them, but, is it possible to have an inconsistent data if i tried to restore a database using binlogs with --stop-datetime?
salleNightelf: That's indeed with the default isolation level and higher. If for some reason you use READ UNCOMITTED the dump will have both rows, but that's inconsistent because at t7 the tx1 might ROLLBACK instead of COMMIT and then you will have dump with row which never really existed in your database
salleNightelf: depends
salleNightelf: if you take dump with --master-data you will know from where you need to replay binlogs
salle!m Nightelf pitr
ubiquity_botsalle: Sorry, I have no idea about that manual entry.
salle!t Nightelf pitr
ubiquity_botNightelf: http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html
Nightelfsalle, thank you so much ;)
JosefDeHi. Can anyone explain me "branch-alias". I don't understand it
JosefDeWhat does that technically mean?: "dev-master": "1.0.x-dev"
salleJosefDe: Where do you see that?
JosefDesalle: Oh sorry. Wrong channel
styks1987does the query written to general log write after or before the query completes?
smerzto slow-log after query completes
styks1987smerz: ok, so general log is ‘as soon as it receives’ the query
smerzi dunno which general log you're referring to tbh
smerzi only know the slow log myself :)
styks1987smerz: ok, I am referring to the general_log in the config but I think I found my answer
snoyesstyks1987: it goes to the general log before it even totally parses the query
snoyesthere's some parsing first, so it can decide if it needs to redact a password
styks1987snoyes: super thanks
tsukasadtCould someone take a quick look at a select query I've written? It has an IF() that is only returning false and I've confirmed the condition is met to be true. :S
Naktibalda!t tsukasadt ask
ubiquity_bottsukasadt: You don't have to ask to ask, or state: "I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question. Also, please read: http://workaround.org/getting-help-on-irc
tsukasadtNaktibalda, https://hastebin.com/ogazatutuf.sql
Naktibaldatsukasadt: IS NOT NULL
tsukasadtKnew it'd be something stupid simple. ^^'
tsukasadtThank you. :)
Naktibalda!n me concat
ubiquity_botNaktibalda: See http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat
NaktibaldaCONCAT() returns NULL if any argument is NULL.
Naktibaldayou don't need that if
tsukasadtAh, thank you! I never would've suspected that. I just figured it'd err if it couldn't pair it. ^^'
tsukasadtNaktibalda, works perfectly. Thank you. :)
andydplease can someone help me debug this spatial query problem ?
snoyesandyd: I'd start with checking if that st_envelope(linestring(...)) returns a geometry or null.
andydi think it oes
andydmysql> select st_astext(st_envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))));
andyd| st_astext(st_envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2)))) |
andyd| POLYGON((36.80893472537028 0.745462319,38.25868727462972 0.745462319,38.25868727462972 2.194737681,36.80893472537028 2.194737681,36.80893472537028 0.745462319)) |
andyd1 row in set (0.00 sec)
andyd(i flippe long and lat before running)
andydwhen i flip it back, makes more sense
andydmysql> select st_astext(st_envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2))));
andyd| st_astext(st_envelope(linestring(point(@rlon1, @rlat1), point(@rlon2, @rlat2)))) |
andyd| POLYGON((0.5563002561458935 36.809173319,2.3838997438541063 36.809173319,2.3838997438541063 38.258448681,0.5563002561458935 38.258448681,0.5563002561458935 36.809173319)) |
passagedude, stop
passageuse a pastebin
andydthe 'shape' of this geo is odd, some of the values are the same. does that make sense ?
snoyesyes, because it's a closed loop
snoyesso the first and last points ought to be the same.
snoyesandyd: so then the next question, do you have any places within that range?
speer12341234Hey all
speer12341234how do I important specific rows based on a column in to Excel using MySQL for Excel? http://imgur.com/a/nQQzl
speer12341234In this case I want to important rows that are using the orderId '1001'
Xgcspeer12341234: That's probably a question for Excel, unless you wish to add a view in MySQL that can be queried directly. If you're asking an SQL question, see the WHERE clause.
Xgcspeer12341234: Maybe this is supported under the [advanced options] button.
snoyesI don't think it is
snoyespretty sure the only thing you can do is a LIMIT clause.
XgcHow about the [really advanced options]?
speer12341234Hmm, I think the views may work. I've never used views or procedures before but from my understanding views is a custom query of sorts, and I suppose I could have it accept orderId's?
snoyesI think it's time to use MySQL's SELECT INTO OUTFILE, or the CSV engine, or mysql -e "SELECT..." > file
snoyesyou could use a view, but you'd have to actually build the WHERE orderID = '1001' right into the view
speer12341234Not much in the advanced options: http://imgur.com/a/RCwy3
snoyessince otherwise you're right back where you started.
Xgcspeer12341234: or just use excel to filter it later.
speer12341234Xgc, I cant. There will be thousands of rows.
Xgcspeer12341234: Then address this outside excel. See above.
speer12341234CSV could work but im trying to find more efficent options. This is a database for a buyback site, so when ever I recieve an order I add it to my spreadsheet. Which method do you think is the most efficent Snoyes?
speer12341234ill research outfile
snoyesor Excel even lets you set up an ODBC data source, and then you can run arbitrary queries using excel as the client.
speer12341234I've read about ODBC but im using shared hosting and Cpanel and it would require a VPS to add the ODBC drivers to my server, want to avoid that
snoyesor you could use putty to set up port forwarding, and let excel and odbc think it's all local
speer12341234You are smart :)
speer12341234Im not quite sure what outfile is or mysql -e "SELECT..." > file, but I believe these are both exporting options from MySQL, (im using PhpMyAdmin as a front-end)
speer12341234Snoyes, do you think ODBC would suit my needs efficently?
snoyesI seem to recall having done something like that in the past when I wanted to use Excel to talk to a MySQL db running on a remote server to which I only had SSH access.
snoyesSetting up a tunnel with Putty isn't hard, and then you just pretend that it's all on localhost
snoyesso Workbench and Excel and the command line client and everything else all worked happily.
motaka2hello what is the right English term for for the table users_table in http://picpaste.com/unnamed0-mFH9TAcp.jpg ?
snoyesmotaka2: https://en.wikipedia.org/wiki/Associative_entity
abaldyglemotaka2 UH????
motaka2snoyes: let5 me check
abaldyglemotaka2 Its a table...probably with a link that is backwords
motaka2snoyes: Thank you very much
speer12341234hey Snoyes
speer12341234Trying to set a database tab like this: https://www.youtube.com/watch?v=P9cUYpXIKsU\
speer12341234https://www.youtube.com/watch?v=P9cUYpXIKsU **
speer12341234being told I dont have SSH connections on shared hosting, although I can make connections with MySQL for Excel..
thumbsa .... youtube link?
snoyesthat's entirely possible; if they've opened the mysql port to the net but not ssh.
snoyesI assume you can set up odbc to a remote data source; haven't tried.
snoyesyeah, the myodbc config has a place to put the TCP/IP server and port, so just use the same values you used for MySQL for Excel.
speer12341234thumbs, the video demonstrates what I am trying to acheieve
snoyesso you won't need the port tunneling thing
speer12341234in the video they are using a excel spreadsheet, I want it to my be remote database
snoyessee https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-wordexcel.html
snoyesyou'll have to set up a DSN first with https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration.html
snoyeswhich seems like a lot, but really isn't all that hard; it's just downloading the right driver and running through a wizard.
speer12341234thank you for the links, this is all new water for me
MartynKeigherHey all. quick Q... how difficult is it to change the root password of 3 MySQL sevrers that are all nodes within a Galera cluster?
MartynKeigheri have just set them up, so there is no DB in the cluster yet - just curious on how i would go about changing the password on them though.
_IChoseThisOne_Is parameterization anything more than just substituting a set of values for symbols within a prepared statement? I realize the process allows you to do operations on the inputs before substitution, like escaping values, but is there anything more that's implied by the term 'parameterization'?
IChoseThisOneIs parameterization anything more than just substituting a set of values for symbols within a prepared statement? I realize the process allows you to do operations on the inputs before substitution, like escaping values, but is there anything more that's implied by the term 'parameterization'?
XgcIChoseThisOne: You don't need to do any escaping. That's detail you no longer worry about with prepared statements. No injection. No worry. No chance for mistakes.
XgcIChoseThisOne: Little Robert'; DROP TABLE students; -- will finally get his name in database correctly.
thumbsXgc: https://beta.companieshouse.gov.uk/company/10542519
thumbsAven: hi
AvenI'm doing a mysqldump but for some reason, it keeps skipping the insert into in one of the tables
thumbsAven: what command did you run?
Avenafter I do the dump, it shows me the results and it doesn't show the INSERT for one of the tables
Avenmysqldump -p lfs_stg < dump.sql
Avenwhere lfs_stg is the db
thumbsas the root user?
Avenwoops I meant
Avenmysqldump -u root -p lfs_stg < dump.sql
thumbsalso, use > file
AvenI'm populating the database
Avenusing the the dump file
thumbsAven: you're using the wrong indirection.
thumbsAven: < is for input, not output.
Avenyes I'm doing input
XgcAven: To mysqldump ???
AvenI already have the dump.sql
AvenI want use the dump file to populate the database
thumbsAven: mysqldump is used to *export* data.
thumbs!t Aven import
ubiquity_botAven: mysql -u uname -p dbname < yourfile.sql
XgcAven: Read carefully. :)
Avenlol I see
speer12341234Is a connection using a MySQL Connector / ODBC secure?
thumbsspeer12341234: define "secure"
speer12341234I dont believe it is, under the advanced settings I see SSL but its not filled in so im assuming this connection I made is not secure
qwidjis it OK to put str_to_date() inside of a values (...) block ?
qwidji am getting a syntax error so was not sure
qwidjwhen inserting
BrownPanickanyone know why slow log would be logging queries that return in less than a hundredth of a second?
BrownPanickI have long-query-time = 2 in my.cnf
speer12341234is a ODBC connection from Excel to MySQL secure?
speer12341234MySQL being remote
Xgcspeer12341234: Over ssh? Sure.
speer12341234Not sure if its SSH
speer12341234How would I check?