2

I have a table that has an archive of daily weather conditions around the globe. There are around 150 million rows and the table is 1.7Gb excluding indexes.

The table is an archive so there are no writes to it. It is a MyISAM table.

Queries are showing up in my slow log as taking up to 45 seconds. These are well cached by the website, but this seems excessive.

CREATE TABLE `archive` (
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `condition` decimal(3,1) NOT NULL,
  `month` tinyint(4) NOT NULL DEFAULT '0',
  `day` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `archive`
  ADD KEY `longitude` (`longitude`),
  ADD KEY `month` (`month`);

I have indexes on longitude and month (as I usually run a monthly average for a location). Just to note - I can't have a primary index on just longitude and latitude as these are all repeated for each day and month.

Here is an example of one of the slow queries (actually the only query type):

SELECT AVG(condition) AS average, MIN(condition) AS min_temp, MAX(condition) AS max_temp FROM `archive` WHERE `latitude`=42.75 AND `longitude`=132.75 AND `month`=4;

Server info:

64Gb RAM
key_buffer_size= 256M

Any thoughts on how I can speed things up? I have considered breaking the table up but am not sure on the best way forward.

Thanks.

  • Is there any reason not to include latitude in the index (longitude, latitude, month)? – RMathis Apr 3 '17 at 20:44
  • The index was alread 2.3Gb so it seemed counter-productive to make it any bigger. I also figured that by indexing longitude that already massively reduce the number of rows to be searched. I'm willing to give it a try if it will help. – Chris Leather Apr 3 '17 at 22:09
  • 1
    Have you considered partitioning the table by month? – joanolo Apr 3 '17 at 22:27
  • 1
    I hear you about the massive index and making it larger - I don't know how extensive your dataset is. Longitude might be sufficient if large metropolitan areas are tracked but might be insufficient if smaller population centers are stored as well - you can query to see how effective the index change will be... select count(distinct longitude,month) from archive; select count(distinct longitude,latitude,month) from archive; If the numbers are similar then your assumption is correct. If the second query is considerably larger then consider the change. – RMathis Apr 3 '17 at 22:36
  • @RMathis I rebuilt the index on all fields and the queries are massively faster now. I did also convert lat/lon from floats to decimals. But I think you were right about the indexes. – Chris Leather Apr 4 '17 at 14:21
1

Too slow and there is a better index? No brainer. Add the index. But... If you already have INDEX(x) drop it as you add any index starting with x. (I'm thinking of longitude and RMathis' comment.

You are likely to have trouble with things like latitude=42.75. latitude is FLOAT; 42.75 is (I think) DOUBLE. In this case, 42.75 can be represented exactly in both FLOAT and DOUBLE. But 42.76 cannot, hence it may not find a match due to roundoff.

A pair of floats consumes 8 bytes. See this for other representation choices. For weather stations that are no closer than a mile, DECIMAL(4,2)/(5,2) would save space (5 bytes instead of 8) and avoid the potential roundoff problem.

You mentioned "one of the slow queries". To best serve you, let's see all the frequent queries, even those that are not slow now. Then we can suggest a set of indexes to best handle all of them.

(When discussing performance issues, please provide SHOW CREATE TABLE; there are several details missing from what you provided.)

How much RAM do you have? What is the value of key_buffer_size? (Recommend 20% of available RAM.) If you have a tiny RAM, then, I will impart some other advice.

  • Thanks @RickJames I've updated the question to include additional info on RAM / buffer_size and CREATE TABLE details. What you point out about floats / decimals looks really useful. And yes my data is all .25 and .75s... Regarding the indexes on lat and lon. Would this be a combined index on both or two separate indexes? I'm guessing combined. – Chris Leather Apr 4 '17 at 10:09
  • As suggested I converted lat / lon to decimals and created an index(lon,lat,month). The queries I tested are flying now. Down to thousandths of second as opposed to some taking 30 seconds before. – Chris Leather Apr 4 '17 at 14:18
  • That's called a 'composite' index; it is much better in many situations, as you have found out. – Rick James Apr 4 '17 at 16:00
  • 1
    With that much RAM, you may as well make the key_buffer_size at least as big as the .MYI file. – Rick James Apr 4 '17 at 16:06
0

Please note that MySQL can only use one index at a time. In your case, only one of the 'longitude' or 'month' will be used and then MySQL will have to "filter" the results based on the rest of fields in WHERE clause.

A table size < 2GB doesn't seem much to me.

Try creating an index on the three columns in your query (month, latitude, longitude) in that order. See how long your query then takes.

Also, as @rick-james pointed out, converting FLOATS to DECIMAL will help with precision/result set from queries. Google recommendation is to use DECIMAL(10,6) but you can decide if you need more fine-grained precision.

PS: Since you have no "year" value stored in the table, I assume max of one year's data in the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.