New answers tagged

0

You can try to rewrite it as: select * FROM followers_info finfo WHERE NOT EXISTS ( SELECT 1 FROM user_followers uf WHERE uf.follower_id = finfo.follower_id ) AND NOT EXISTS ( SELECT 1 FROM user_following fing WHERE fing.follower_id = finfo.follower_id ); or: select * FROM followers_info finfo WHERE NOT EXISTS ( SELECT 1 FROM ...


0

Thanks to @Akine I did this: SELECT DISTINCT Designation, Model, COUNT(CASE WHEN ((ei.IssueLocationID IS NULL) OR (ei.IssueLocationID IS NOT NULL AND ei.ReturnLocationID IS NOT NULL )) THEN e.EquipmentID ELSE NULL END) AS Total FROM Item i INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID INNER JOIN Equipment e ON e.ItemID = i....


0

According to the documentation: In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. Therefore, a more direct approach would be: INSERT INTO `table2`(`aid`, `count`) SELECT `aid`, COUNT(`aid`) AS `count` FROM `table1` GROUP BY `aid` ON DUPLICATE KEY UPDATE ...


0

Using lookup columns is definitely the way to go here. For all except the columns that are always present. Today I have 2x as much data nearly 100k records, and it only uses 54mb. Storing all those null used over 100mb for less than 1/2 the records that I have now. The lookup columns are integers so they only use 2 bytes each for each column. Still ...


0

KEY `parent_id_index` (`parent_id`), --> KEY (parent_id, REV, id) The order is critical. First it filters on parent_id =, then no a range of REV, and finally has id to make the index "covering".


0

I suspect the answer goes something like this... Approach 1: 800KB takes a lot of disk activity to write. This takes perhaps 1000ms. When you "wrote" a row in 31ms, that was because of most of the I/O being delayed until after acknowledging the action. When it took 718ms, the disk was busy writing other things, so it was slow at accepting the current ...


0

Since MAX will never increase due to the JOIN, you might try to filter using EXISTS instead: SELECT c.id AS cId, MAX(c.REV) AS cRev FROM Child_AUD c WHERE EXISTS ( SELECT 1 FROM Parent_AUD p WHERE p.id = c.parent_id AND p.REV >= c.REV ) GROUP BY c.id; An index like: CREATE INDEX ON Child_AUD (ID, PARENT_ID, REV); may help.


0

This will work in MySQL regardless of the character set. SELECT 'test' REGEXP BINARY 'TEST' AS RESULT; Putting 'BINARY' forces a binary comparison.


3

MySQL, in the absence of explicit data type information, tries to determine the appropriate data types for literal values based on the value limits. Since the scale of 18446744073709551616 exceeds the allowed range for UNSIGNED BIGINT, it must be a DECIMAL then, which has a much larger range. You can see this in action by creating a table from your SELECT: ...


0

Select into statements return one result, maybe you can use that like this: WHILE i < 50 DO INSERT INTO a_table(bla, bla, bla) SELECT (bla, bla,bla) FROM b_table LIMIT i,1; SET i = i + 1; END WHILE;


-3

You could use the MySQL NOW() function to get the current timestamp.


0

That is a gynormous upgrade. Plan A: Oracle recommends doing it in 4 steps 5.1 -> 5.5 -> 5.6 -> 5.7 -> 8.0. And, at each step, run mysql_upgrade. Plan B: Alternatively, you could dump the data and hope that the reload doesn't find some syntax errors. I fear that you may have already hosed the files too much to have a successful upgrade.


1

Try to use the index by an expression: CREATE INDEX idx_name ON alarm (gDateTime, (DATE(alarmDateTime))) If It will not help then add generated column and index which uses it: ALTER TABLE alarm ADD COLUMN alarmDate DATE AS (DATE(alarmDateTime)) /* VIRTUAL */ , ADD INDEX idx_name ON (gDateTime, alarmDate) Then use new column instead of expression in your ...


1

You're almost there. Your trigger should probably look like this: CREATE TRIGGER update_sire_name before UPDATE ON table_name for each row set new.sireName = (select name from table_name where regNo = new.sireRegNo) dbfiddle 1 Yes, there can be a where clause in a trigger. 2 Consider using numeric data types (e.g. INTEGER) where values are ...


1

date(alarm.alarmDateTime) MySQL cannot use indexes for any column 'hidden inside a function call'. Anyway, in Where alarm.gDateTime>'2019-08-01' group by ... The most you can get is the use of INDEX(gDateTime) to help with the filtering. Even so, if too large a percentage of the table is needed, that index will be ignored. OTOH, a "covering index" ...


0

SELECT LEFT(startTime, 13) AS 'start of Hour', COUNT(*) AS 'Total Jobs', SUM(TO_SECONDS(endTime) - TO_SECONDS(startTime) <= 300) AS '0to5m', SUM(TO_SECONDS(endTime) - TO_SECONDS(startTime) BETWEEN 301 AND 600) AS '5to10m', ... FROM tbl GROUP BY LEFT(startTime, 13) Optionally have WHERE jobID = 12. Problems: ...


0

EAV (Entity Attribute Value) schema may benefit from ..level=1. This is because of messy WHERE clauses that have lots of essentially the same clauses WHERE a1.e = 123 AND a1.attr = 'color' AND a1.val = 'red' AND a2.e = 123 AND a2.attr = 'shape' AND a2.val = 'round' AND a3.e = 123 AND a3.attr = 'size' AND a3.val = 'big' AND a4.e = 123 AND a4.attr = '...


0

What solved it for me was setting the min and max instance count higher than before (forcing aurora to scale up, for example from min 2 ACU to min 4 ACU), and after scaling restoring the scaling settings. I used 'Force scaling the capacity to the specified values when the timeout is reached' and 'Apply immediately'. It also solved another issue we had, the ...


0

Only matched records with matched patterns list: WITH phrases AS (SELECT 'Phrase containing "text1" substring' phrase UNION ALL SELECT 'Phrase containing "text2" substring' UNION ALL SELECT 'Phrase containing "text1" and "text2" substrings' UNION ALL SELECT 'Phrase not containing desured substrings'), patterns AS (SELECT ...


0

Typically I recommend materialising your resultset when this scenario has come up. Since queries of this nature are usually pretty broad and analytical (read: non-performant), this approach has the benefit of reducing editing requirements (do you want to add the line to both the select and where clauses for each evaluation?) snapshotting the underlying data ...


1

ENDIF; END$$ DELIMITER; --> END IF; END $$ DELIMITER ; END IF is 2 words. Have a space before the delimiter.


0

Take a look at slave_type_conversions parameter in your parameter group on the replica. There's an option to allow truncation due to data type difference between master and replica. Please note this is a static parameter, so it will require a reboot of the RDS instance to take effect. https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#...


0

Not enough information, but I will give some insight. How much RAM will you have? MyISAM will occupy 1/3 to 1/2 the disk space of the equivalent InnoDB table. If there will be writes to the table, MyISAM can suffer minor or major corruption in a crash. If the selects are only selecting one row via SELECT ... WHERE md5hash = '...', then either will work ...


0

You could let your app export/import csv files. If you build the default schema in your app/db then just export/import the data. Here is an example of exporting a csv in mysql. SELECT usernames, passwords FROM mytable INTO OUTFILE 'C:/passwordDB.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\...


0

you can use a subquery in your where clause. e.g. SELECT sheet, entry, approver FROM timesheet WHERE sheet = ( SELECT sheet FROM timesheet WHERE approver = 3 );


0

This will vary from DBMS to DBMS, but in general your data is already stored in one or more files. If using sqlite you will have a single file that, once your application is no longer making changes, you can just copy from place to place. With the likes of SQL Server you can backup the database to a single file (with the logically named command: BACKUP ...


0

I had a same problem, I fixed it by changing the tmpdir path. You can change it to whatever you like, just set the permission for that folder correctly ("write" for all or just for the mysql user).


0

I rewrote the logic such that the SELECT is run unconditionally, ie I got rid of the row_count() > 0 check and handle results in the client. The problem went away.


0

Assuming your implicit assumptions about the data hold, the following works using SQL Server - will need the odd tweak to work in MySQL, which you've also tagged your question with: -- set up test data CREATE TABLE Games ( Winner INT NOT NULL, Loser INT NOT NULL, ID INT PRIMARY KEY, CHECK (Winner <> Loser) ); INSERT INTO Games VALUES (...


1

Since you're reading every record in the table, the database will simply pull everything you want, wrap it [all] up and send it back across the network to your client machine. The more data you pull, the larger that payload is going to be and the slower it's going to travel. I frequently run this very simple query The database is static and the ...


2

If you know in advance that you're going to have at most a smallish (under 10) set of services and you don't need to add/remove/change them programmatically or often, I would use separate link tables for the services. Since the client needs to request a service to be added, it's probably a lot simpler to create a new link table and make code changes to ...


1

In similar situation I use: DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END; REPEAT UPDATE job_queue SET status = "WORKING", job_id = <arg_unique_job_id> WHERE status = "NEW" LIMIT 1; SELECT * FROM job_queue WHERE job_id = <arg_unique_job_id>; UNTIL row_count() < 1 END REPEAT; It's never gone wrong before. ...


0

deleted and seen get in the way of optimizations GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id) seems strange. Wouldn't this suffice: GROUP BY m.to_id, m.from_id? Since the user is either from_id or to_id, half the info is already known? What is m.id < 999999999 for? IN ( SELECT ... ) often optimizes poorly. You say "pagination", yet I ...


0

"Don't queue it, just do it". The overhead of the queuing mechanism threatens to dominate the work expended. And, when there is a hiccup, the queuing mechanism can become the bottleneck. status seems redundant; job_id IS NULL could be the clue of being "NEW". (A UNIQUE key treats NULLs as being different.)


0

The perhaps obvious solution would be to modify step 3: Don't just delete all rows from the active table. Instead, find a column you can use as an indicator of whether the rows are newer or older than the most recent row that will be copied to the archive table/database. This column can be e.g. an integer used for the primary key column, or if you don't ...


0

SELECT COUNT(*) > 0 `Does It Interfere?` FROM table WHERE @newBillNumber BETWEEN starting_bl_no AND ending_bl_no


-1

SET @num := 0; UPDATE tablename SET id = @num := (@num+1); ALTER tablename AUTO_INCREMENT = 1;


0

WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) rn FROM table2 WHERE esp='A' ) SELECT * FROM cte WHERE rn=1


0

If you want the 'last' A: SELECT * FROM table2 where esp = 'A' ORDER BY id DESC LIMIT 1; If you need something more complicated, see "groupwise-max".


-1

Maybe that will work for you? SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc Limit 1 OR SELECT * FROM table2 where id = (SELECT max(id) FROM table2 where esp = 'A' group by _name) EDIT This should work SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc


0

Looks like this issue: https://jira.mariadb.org/browse/MDEV-20247 Body must be 30 characters.


3

Create a separate table named account_owner with columns account_id and user_id. Have account_id be the primary key and both account_id and user_id should reference their respective parent tables. Since only one record can be entered per account_id then you ensure there is always at most one owner. Alternatively, just add a owner_user_id column in the ...


0

Shrinking the table should have been done before you started. You have several large columns; what do they contain? Can they be put somewhere else? Are they repeated for the same device? (etc) DOUBLE for lat/lng -- Overkill, unless you are tracking microbes. See http://mysql.rjweb.org/doc.php/latlng#representation_choices Why are there 3 timestamps? For ...


3

In MySQL multi-column unique constraints are implemented such that they allow multiple null values. You can make use of this by using nulls to represent the non-owners: create table account_membership ( account_id int not null references accounts (id), user_id int not null references users (id), is_owner boolean null check (is_owner in (1, null)), ...


0

I was able accomplish this with the following query: SELECT u.username, SUM(score) as monthlyScore FROM ( SELECT id FROM `games` WHERE MONTH(gameTime) = 8 ) g INNER JOIN `predictions` p on g.id = p.gameId INNER JOIN `users` u on p.userId = u.id GROUP BY userId ORDER BY `monthlyScore` DESC I'm not sure how this ranks in terms of efficiency. ...


1

MAMP seems to handle the root username differently than WAMP. I had to include the root password as 'root' in my PHP script, instead of leaving it blank, as such: $link = mysqli_connect('localhost', 'root', 'root');


0

Use 0 instead of null in the sum? SUM(CASE MONTH(date_added) WHEN 7 THEN T2.qty ELSE 0 END) AS 'July',


0

If you want the string 'null', you need to turn NULL into that string: COALESCE(expression, 'null')


0

SELECT ... FROM ( getting all gameIDs that are in the given month ) AS x JOIN ... ON ... See how you can use a "derived table" to do the first step of your chain? It is a simple SELECT gameIDs FROM ... WHERE .... You may end up with SELECT ... FROM ( SELECT ... FROM ( SELECT ... ) AS x ) AS y or ...


1

You should leave the innodb_buffer_pool_chunk_size "as is". Last year, Percona published some nice formulas to figure out the best chunk size determine_best_chunk_size{   if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size   then     innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / ...


Top 50 recent answers are included