You can try to rewrite it as:
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
FROM followers_info finfo
WHERE NOT EXISTS (
SELECT 1 FROM ...
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....
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`
GROUP BY `aid`
ON DUPLICATE KEY UPDATE ...
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.
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 ...
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 (
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);
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:
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.
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 ...
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)
1 Yes, there can be a where clause in a trigger.
2 Consider using numeric data types (e.g. INTEGER) where values are ...
MySQL cannot use indexes for any column 'hidden inside a function call'.
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" ...
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',
GROUP BY LEFT(startTime, 13)
Optionally have WHERE jobID = 12.
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 = '...
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 ...
Only matched records with matched patterns list:
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 ...
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 ...
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.
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 ...
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.
INTO OUTFILE 'C:/passwordDB.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\...
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 ...
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
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 ...
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 ...
In similar situation I use:
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
SET status = "WORKING", job_id = <arg_unique_job_id>
WHERE status = "NEW"
WHERE job_id = <arg_unique_job_id>;
UNTIL row_count() < 1 END REPEAT;
It's never gone wrong before.
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 ...
"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.)
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 ...
Maybe that will work for you?
SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc Limit 1
SELECT * FROM table2 where id = (SELECT max(id) FROM table2 where esp = 'A' group by _name)
This should work
SELECT * FROM table2 where esp = 'A' group by _name,esp order by esp desc
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 ...
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?
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)),
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. ...
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');
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
FROM ( SELECT ...
FROM ( SELECT ...
) AS x
) AS y
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
if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size
innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / ...