MySQL Query Fixing/Optimisation for my configuration table

Question

I got a mySQL table, that holds the configuration of my project, each configuration change creates a new entry, so that i have a history of all changes, and who changed it.

CREATE TABLE `configurations` (
  `name` varchar(255) NOT NULL,
  `value` text NOT NULL,
  `lastChange` datetime NOT NULL,
  `changedBy` bigint(32) NOT NULL,
  KEY `lastChange` (`lastChange`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `configurations` (`name`, `value`, `lastChange`, `changedBy`) VALUES
('activePageLimit', 'activePageLimit-old-value', '2016-01-06 12:25:05', 1096775260340178),
('activePageLimit', 'activePageLimit-new-value', '2016-01-06 12:27:57', 1096775260340178),
('customerLogo', 'customerLogo-old-value', '2016-02-06 00:00:00', 1096775260340178),
('customerLogo', 'customerLogo-new-value', '2016-01-07 00:00:00', 1096775260340178);

Right now i have a problem with my select query, that should return all names and their latest value (ordered by lastChange).

|            name |                     value |                lastChange |
|-----------------|---------------------------|---------------------------|
|    customerLogo |    customerLogo-new-value | January, 07 2016 00:00:00 |
| activePageLimit | activePageLimit-new-value | January, 06 2016 12:27:57 |

My current Query is:

SELECT `name`, `value`, `lastChange` 
FROM (
  SELECT `name`,  `value`, `lastChange` 
  FROM `configurations` 
  ORDER BY `lastChange` ASC
) AS  `c` 
GROUP BY `name` DESC

But unfortunately this does not always return the right values, and i don't like to use a subquery, there has to be a cleaner and faster way to do this.

I also created a SQL-Fiddle for you as a playground: http://sqlfiddle.com/#!9/f1dc9/1/0

Is there any other clever solution i missed?

This return the 2 old values in my example. Changing the order from `ASC` to `DESC` doesn't change a thing, since my MySQL does the `GROUP BY name` first and than sorts the result of the grouping. I need sorting before grouping. — Frederick Behrends, Jan 06 '16 at 14:08
Create two tables - `config_variables` and `config_values`. `config_variables` contains the `name`. The `config_values` contains values, the history of values and who changed them. Every time you update a value, you insert a new record into `config_values`. Using a trigger, you update `config_variables` and you insert the `id` of the latest relevant value in `config_values`. To make everything easy, create a view that joins the two tables so you can always pull the latest valid variable configuration value without doing too much work. — Mjh, Jan 06 '16 at 14:15

Gordon Linoff · Accepted Answer · 2016-01-06T14:57:03.490

Your method is documented to return indeterminate results (because you have columns in the select that are not in the group by).

Here are three alternatives. The first is standard SQL, using an explicit aggregation to get the most recent change.

SELECT c.*
FROM configurations c JOIN
     (SELECT `name`,  MAX(`lastChange`) as maxlc
      FROM `configurations` 
      GROUP BY name
     ) mc
     ON c.name = mc.name and c.lasthange = mc.maxlc ;

The second is also standard SQL, using not exists:

select c.*
from configurations c
where not exists (select 1
                  from configurations c2
                  where c2.name = c.name and c2.lastchange > c.lastchange
                 );

The third uses a hack which is available in MySQL (and it assumes that the value does not have any commas in this version and is not too long):

select name, max(lastchange),
       substring_index(group_concat(value order by lastchange desc), ',', 1) as value
from configurations
order by name;

Use this version carefully, because it is prone to error (for instance, the intermediate group_concat() result could exceed a MySQL parameter, which would then have to be re-set).

There are other methods -- such as using variables. But these three should be sufficient for you to consider your options.

mysqlrockstar · Answer 2 · 2016-01-06T14:21:36.240

0

If we want to avoid SUBQUERY the only other option is JOIN

SELECT cc.name, cc.value, cc.lastChange FROM configurations cc
JOIN (
  SELECT name,  value, lastChange 
  FROM configurations 
  ORDER BY lastChange ASC
) c on c.value = cc.value
GROUP BY cc.name DESC

edited Jan 06 '16 at 14:21

answered Jan 06 '16 at 14:09

mysqlrockstar

2,536
1
19
36

Subquery and JOIN are one and the same. – Mjh Jan 06 '16 at 14:20
For all the purposes, it's exactly the same, from the results produced to performance. The only performance difference is how lexer lexes it. We're talking microseconds here, if that. The majority of performance issues doesn't lie within the lexer but within I/O subsystem anyway. – Mjh Jan 06 '16 at 14:26
You need some good tutorial http://stackoverflow.com/questions/2577174/join-vs-sub-query – mysqlrockstar Jan 06 '16 at 14:27

score 0 · Answer 3 · answered Jan 06 '16 at 23:26

You have two requirements: a historical log, and a "state". Keep them in two different tables, in spite of that providing redundant information.

That is, have one table that faithfully records who changed what when.

Have another table that faithfully specifies the current state for the configuration.

Plan A: INSERT into the Log and UPDATE the `State whenever anything happens.

Plan B: UPDATE the State and use a TRIGGER to write to the Log.

MySQL Query Fixing/Optimisation for my configuration table

3 Answers3