I've got a mysql database with about 4.2m rows in a particular table of interest. The data consists mostly of and id, latitude, longitude, as well as some other metadata.
Since really only a very small percentage (the most recent) of the data is relevant; most of the data is really just for record. It's up to 1.7gb in size and more recently I've been watching mysql spike in CPU usage up to 400%.
It goes without saying that it would be a good idea to clean out unused data, but if it shouldn't be affecting performance then for the time being I'd prefer to keep it and investigate more pressing issues (like this one).
I read through MySQL high CPU usage [closed] with some good suggestions, but before testing too many out on our production database. I'm curious if this, what seems to me, some what obvious solution is indeed an answer.
I remember reading that with InnoDB deleting rows won't necessarily increase performance but can't find where. Perhaps someone can confirm if this is true.
Here is the SHOW CREATE TABLE item:
CREATE TABLE `item` (
`encounter_id` varchar(50) NOT NULL,
`spawnpoint_id` varchar(255) NOT NULL,
`item_id` int(11) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`disappear_time` datetime NOT NULL,
PRIMARY KEY (`encounter_id`),
KEY `item_spawnpoint_id` (`spawnpoint_id`),
KEY `item_item_id` (`item_id`),
KEY `item_disappear_time` (`disappear_time`),
KEY `item_latitude_longitude` (`latitude`,`longitude`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
An example of a query I might run would be:
SELECT * FROM item WHERE latitude > 38 AND latitude < 37
There are a couple of things you could do to improve the performance. Deleting a heap of rows won't necessarily speed up your overall mysql performance (nothing noticeable anyway). Some things you could try are:
I suggest you read up on the following site, it's got a lot of tips that will help you:
http://www.tocker.ca/2013/10/24/improving-the-performance-of-large-tables-in-mysql.html