We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

4

Checking directly for uniqueness of a 5000-character string is going to be pretty expensive, no matter how you implement it. I'd suggest computing a hash, storing that hash, and computing uniqueness on that hash. This is almost the same thing, but does introduce the opportunity for a hash collision, where two different values result in the same hash. The ...


4

What's difference between MySql IN and ANY operator? There is 2 differences between them. IN is an operator whereis ANY is not, it is a keyword and may be treated as a comparison operator modifier (the same for ALL and SOME). See Operators and Operator Precedence. IN is listed in both whereas ANY, ALL and SOME are not. IN may process an expression list ...


3

If you are using a older MySQL versions like 5.1 - 5.7 , you need to use MySQL user variables as these MySQL versions does not support window functions and or common table expressions .. I ported the answers of @Akina and @mustaccio and the fiddle of Akina to a MySQL 5.1+ working query. Warning as MySQL 5.1 to MySQL 5.7 does not use window functions and ...


3

It is unclear whether the method in your example is right or best for you, but if you need to query or return the same expression in many different queries, you can add a custom column to the table itself. In MySQL, this is called a generated column: ALTER TABLE MY_TABLE ADD COLUMN custom_column VARCHAR(50) AS ( SUBSTRING(column2, 4) ) ; This ...


3

"Covering" is the wrong term to start with. I'll get back to that in a minute. The optimal index for your queries can be summarized: The first columns in the index must be all the = columns in the WHERE, in -any_ order. The last columns in the index must be the ORDER BY columns in the same order, and either be all ASC or all DESC. (MySQL 8.0 has an ...


3

t is the alias for select t1.po_nomor , (sum(t1.jumlah)-coalesce(t4.terima,0)) as total from pengiriman_supply t1 inner join data_supplier t2 on t1.idsupplier = t2.id_supplier inner join data_barang t3 on t1.idbarang = t3.idbarang left join masuk t4 on t4.refrence = t1.po_nomor where t1.tanggal between date_sub(curdate(), interval 120 day) and curdate() ...


2

Nothing wrong with your query. I already tested in my mariadb database version 10.2.25 and it works Please check your mysql version. If you want to use mysql, please upgrade to mysql version 8 If you want to switch to mariadb, make sure version minimal 10.2


2

I call this "burning ids". I think it happens with all flavors of INSERT, including INSERT IGNORE, IODKU, REPLACE, etc. Here is a workaround: http://mysql.rjweb.org/doc.php/staging_table#normalization That describes how to use 2 SQLs to "normalize" a batch of names and get their ids in an efficient way, and without burning ids. If you have only a single ...


2

Test select: select macaddress, upper(replace(macaddress,':','')) as new_macaddress from Macs; Update: update macs set macaddress = upper(replace(macaddress,':','')); Fiddle Test data: create table macs ( macaddress varchar(20) ); insert into macs values('90CCAADD3341'); insert into macs values('90:3f:ff:11:22:33'); insert into macs values('33:44:...


2

I don't know about MySQL but Postgres never locks the whole table when running DML statements - regardless of the number of affected rows. Additionally writers never block readers, so even if you update all rows in a table, other transactions will still be able to SELECT from that table - obviously seeing the old values until your DML statement is ...


2

the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows Test: WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) summ FROM sourcetable ) SELECT AVG(price) FROM cte WHERE summ <= 0.1 * ( SELECT MAX(summ) FROM cte ) fiddle


2

Depending on the size of the table, this variant of Akina's solution might perform a little better by calculating both running total and full total of volume in one go: WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) running, SUM(volume) OVER (ORDER BY price ASC RANGE BETWEEN UNBOUNDED PRECEDING AND ...


2

You can use exists and not exists select ... from employee e where exists ( select * from dependent where ssn = e.ssn and relationship = 'Son' ) and not exists ( select * from dependent where ssn = e.ssn and relationship = 'Daughter' ) You can also achieve this with a left join select ... from employee e left join dependent d on e....


2

SELECT name, COUNT(item_id) FROM ( SELECT c.name, i.item_id FROM categories c LEFT JOIN item_category ic ON ic.category_id = c.category_id LEFT JOIN items i ON ic.item_id = i.item_id UNION ALL SELECT c.name, i.item_id FROM categories c LEFT JOIN subcategories sc ON sc.category_id = c.category_id LEFT ...


2

Use user-defined variable for to convert the date value from dd\mm\YYYY format to the date/datetime datatype value during import: LOAD DATA INFILE .... (field1, field2, ..., @date, ...) SET datefield = STR_TO_DATE(@date, '%d\/%m\/%Y');


2

Here's how to identify which 'bad' query to fix that will give you the most bang for the buck: The slowlog plus pt-query-digest. More details here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog I rarely need to go down the list more than 3 items to make a dramatic improvement. If you are weak on knowing what indexes to add, don'...


2

major Release Candidate General Availability 8.0 2017-09-21 8.0.3 2018-04-19 8.0.11 5.7 2015-04-08 5.7.7 2015-10-21 5.7.9 5.6 2012-09-29 5.6.7 2013-02-05 5.6.10 5.5 2010-09-13 5.5.6 2010-12-03 5.5.8 5.1 2007-09-24 5.1.22 2008-11-14 5.1.30 5.0 2005-09-22 5.0.13 (I don't have MariaDB's GA dates.) You can try to ...


2

Consider writing a script (in your favorite language) to read the dump file one line at a time and send that line to STDOUT, which will be piped into mysql. And add a tiny sleep in the loop that does that. To get fancier, you could fetch Seconds_behind_master to tweak the sleep up or down. (I think at least one of Percona's tools does this.)


1

Give this a try: BEGIN; SELECT * FROM products WHERE exported = 0 INTO OUTFILE "x/y/z" FOR UPDATE; UPDATE products SET exported = 1 WHERE exported = 0; COMMIT; And I don't think it matters what the isolation mode is. I am not sure of mixing INTO OUTPUT with FOR UPDATE; if it give you trouble, make it two SELECTs. Note that this does obviate your program ...


1

http://sqlfiddle.com/#!9/33b19d/3 ... as I said in the comments - use left join instead of inner join ... your diagram misses the mentor_types ... so I commented it out. of course you will get 2 lines for user 1886 For your question regarding all lines of user_mentors - no its impossible if you start building the select with the users table. And as ...


1

If, by "sharding", you mean having some data on one server; some on other servers. And you need to touch different servers in the same "transaction". Then you need "XA" in order to achieve ACID. It is complex and slow.


1

As for the ALTER... ALTER TABLE ... ALGORITHM=COPY; will do the alter, and make sure to also copy the table over, thereby effectively doing an OPTIMIZE in the process. TRUNCATE loses the data; do you really want to do that?


1

CREATE TABLE Table1_new LIKE Table1; INSERT INTO Table1_new SELECT * FROM Table1; ALERT TABLE Table1_new ADD COLUMN a; --slow because of big table is not optimal and has a problem. Better: CREATE TABLE Table1_new LIKE Table1; ALERT TABLE Table1_new ADD COLUMN a; -- fast because table empty INSERT INTO Table1_new SELECT *, NULL FROM Table1;...


1

Queries 2 and 3 read a subset of the rows already read by query 1 so you can easily combine them by using that as a base and moving the additional filters into CASE expressions. SELECT SUM(`Line_Total`) AS Total, SUM(`_Revenue`) AS Incr, SUM(CASE WHEN `Division` = 'Good' THEN `Channel` END) AS SKG, SUM(CASE WHEN `Division` = 'BAD' ...


1

You want AND d.tentative_date_of_return <= CURDATE() + INTERVAL 3 DAY


1

Try: SELECT a.aircraftid 'Aircraft ID', COUNT(s.serviceid) FROM aircraft a JOIN service s ON a.aircraftid = s.serviceid GROUP BY a.aircraftid HAVING COUNT(s.serviceid) > ( SELECT COUNT(serviceid) / COUNT(DISTINCT aircraftid) FROM service )


1

Your query looks like a different approach will be better than simply using more and more LEFT JOINs. Instead, move to using subqueries in the SELECT clause for most of these "added tables". You shouldn't need the DISTINCT anymore, either, if that was included just to avoid the explosion of rows from so many JOINs. SELECT c.`user_name`, e.`event_name`, e.`...


1

You just need to link back to the Product table and sort by some other criteria on there. You can ask the Full Text engine to rank the results, but that really just ranks how closely the result matches their search, which is not what you are looking for. I have a sample here using MS SQL Server. Since you didn't provide a schema, I put together ...


1

That depends. If the test database on the Master was never in use, then the slave won't care. As long as the slave is not a master to another server, then you are OK. If the slave has binary logging enabled, the database will just get created and dropped and a record of it would be in the binary logs of the slave. Besides a test database would not be in ...


1

How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0: SELECT t1.contact_id, t1.contact_group_id, t1.contact_email, COUNT(t2.contact_id) - 1 row_number FROM contacts t1 JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id AND t1.contact_id >= t2....


Only top voted, non community-wiki answers of a minimum length are eligible