I'm using mysql 5.5. My OS is Centos7. I have this simple structure
Create table myContent
(
TheId int PK
Content blob
);
The size of each blob is around 100KB. I need to optimize
select * from myContent limit N.
Usually, I put N as 50 so it is about 5MB. It runs around 1 minute to retrieve. My internet speed is 10mbps.
The query was just fine until this corona pandemic happens and my internet provider slows the internet down a lot. I then subscribed to faster speed to 20mbps and my query is still super slow (Limiting 5 rows (500KB) takes more than 1 minute). I can't change to another internet provider because it is the only provider I have in my area.
I checked my MySQL traffic and actually the traffic is slower than usual. There is no table lock as well. Are there any other things that I can do to MySQL?
EDIT: I think my explanation is not clear what my purpose is or what I'm trying to do. I tried to simply things so it won't get too complicated. I'm trying to create syncronization data between multiple local server using queue. All servers are located in different area and most of them is using the same internet provider. My blob is the result of compressed raw data. The blob is generated from each server that needs to be downloaded by another server. So here is my primary structure:
create table myQueue
(
myQueueId int NOT NULL, -- PK
serverId int DEFAULT NULL, -- this is the ID where the data is from
blobId int DEFAULT NULL, -- the id of my blob table (TheId)
timeupdate datetime, -- current time
PRIMARY KEY (myQueueId),
UNIQUE INDEX blobId (blobId),
INDEX timeupdate(timeupdate)
);
CREATE TABLE downloadedServer (
myQueueId int NOT NULL, -- PK
serverId int(11) NOT NULL, -- PK
serverIdDownloaded int(11) NOT NULL, -- PK
PRIMARY KEY (myQueueId, serverId, serverIdDownloaded)
);
CREATE TABLE myContent
(
TheId int NOT NULL, -- PK
Content blob, -- this is the result of compressed data that is generated from the app that needs to be downloaded by another server. I tried my best so this is the very least amount of size that I can do
PRIMARY KEY (TheId)
);
Everyday, the myQueue table is cleaned so it won't get bloated. My regular query in each server is as follows:
-- Check if there is any data that has to be downloaded
SELECT COUNT(*) FROM myQueue q
WHERE q.serverId != vServerId -- make sure it's not from the same server
AND NOT EXISTS (SELECT 1 FROM downloadedServer d WHERE
d.serverIdDownloaded = vServerId AND
d.myQueueId = q.myQueueId AND
d.serverId = q.serverId);
-- Then I download the content that has to be downloaded using this query.
-- This query gets very slow even I reduced the limit N since my internet provider slows the internet down. The server that subscribed different provider doesn't have this problem.
SELECT q.myQueueId, q.serverId, c.Content
FROM myQueue q
JOIN myContent c ON q.BlobId=c.TheId
WHERE q.serverId != vServerId -- make sure it's not from the same server
AND NOT EXISTS (SELECT 1 FROM downloadedServer d WHERE
d.serverIdDownloaded = vServerId AND
d.myQueueId = q.myQueueId AND
d.serverId = q.serverId)
ORDER BY q.timeupdate LIMIT N;
After it is downloaded, the blob is decompressed from the server which just downloaded. It happens every 15 minutes.
My query select * from myContent limit 5 is for testing purpose why downloading 500KB data takes a long time and I'm wondering how I can make it faster in such a slow connection.