title: How to optimize my mysql request to work with large database
body: <p>I'm working on a project with php/mysql(mariaDB) that generates statistics with some data as input. The system that filled the DB is an electric powerplant and specifically the alarm system.</p>
  
  <p>My DB has three fields: <code>DeviceTime</code>, <code>VariableName</code> and <code>alarmState</code></p>
  
  <p>The alarmState field can have 2 possible states: <code>Normal</code> when the alarm is going off, and <code>Active</code> when alarm is active.</p>
  
  <p>I want to make a statistic that counts between the time (DeviceTime) an alarm appears (Active State) and the time (DeviceTime) the alarm switches off (Normal state)</p>
  
  <p>Today I make a request that works fine, but only with a few data.
  When I test the request with all data (about 48k rows) the request takes too long and mysql crash after some time.</p>
  
  <p>This is my request that works with a small amount of data</p>
  
  <pre class="lang-sql prettyprint-override"><code>select k.deviceTime as stime, k.variableName as svar, k.alarmState as sstate, i.deviceTime, i.variableName, i.alarmState, timediff(i.deviceTime, k.deviceTime) as diff
  from imports k
  join imports i on i.variableName = k.variableName
                  and
                    i.deviceTime = (select t.deviceTime
                                    from imports t
                                    where t.variableName = k.variableName and
                                            t.deviceTime &gt; k.deviceTime and
                                            t.alarmState ='NORMAL'
                                    order by t.deviceTime limit 1
                    )
  
  where k.alarmState = 'ACTIVE'
  
  </code></pre>
  
  <p>Here is my data table:</p>
  
  <pre><code>id  deviceTime              variableName            alarmState
  1   2019-07-11T10:05:24.482 B1.d_07QFA11AN001XB08   ACTIVE
  2   2019-07-11T10:05:24.282 B1.d_07QFA11AN001XB08   NORMAL
  3   2019-07-11T10:05:15.409 G1.PTUR-38-T.228.52     ACTIVE
  4   2019-07-11T10:03:51.409 G1.PTUR-38-T.228.52     NORMAL
  5   2019-07-11T10:03:37.409 G1.PTUR-38-T.228.52     ACTIVE
  6   2019-07-11T10:03:09.409 G1.PTUR-38-T.228.52     NORMAL
  7   2019-07-11T10:02:55.409 G1.PTUR-38-T.228.52     ACTIVE
  8   2019-07-11T09:52:06.415 B1.d_07QFA11AN001XB08   ACTIVE
  9   2019-07-11T09:52:06.214 B1.d_07QFA11AN001XB08   NORMAL
  10  2019-07-11T09:51:06.403 B1.d_07QFA11AN001XB08   ACTIVE
  </code></pre>
  
  <p>Result with small amount of data:</p>
  
  <pre><code>stime                   svar                    sstate  deviceTime              variableName            alarmState  diff
  2019-07-11T09:52:06.415 B1.d_07QFA11AN001XB08   ACTIVE  2019-07-11T10:05:24.282 B1.d_07QFA11AN001XB08   NORMAL      00:13:17
  2019-07-11T10:03:37.409 G1.PTUR-38-T.228.52     ACTIVE  2019-07-11T10:03:51.409 G1.PTUR-38-T.228.52     NORMAL      00:00:14
  2019-07-11T10:02:55.409 G1.PTUR-38-T.228.52     ACTIVE  2019-07-11T10:03:09.409 G1.PTUR-38-T.228.52     NORMAL      00:00:14
  2019-07-11T09:51:06.403 B1.d_07QFA11AN001XB08   ACTIVE  2019-07-11T09:52:06.214 B1.d_07QFA11AN001XB08   NORMAL      00:00:59
  </code></pre>
  
  <p>It's exactly what I want for the result, but if anyone had an idea to optimize this request, or another method to build a request that can  return the time difference between the <code>alarmState</code> and the corresponding <code>variableName</code>.</p>
  
  <p><strong>Edit</strong>:
  My MariaDB Version is <code>10.4.6-MariaDB</code></p>
  
  <p>Here is the table structure</p>
  
  <pre class="lang-sql prettyprint-override"><code>create table imports
  (
      id           bigint unsigned auto_increment
          primary key,
      deviceTime   varchar(255) not null,
      variableName varchar(255) not null,
      alarmState   varchar(255) null,
      created_at   timestamp    null,
      updated_at   timestamp    null
  );
  
  </code></pre>
  
  <p>and the <code>Explain query</code></p>
  
  <pre class="lang-sql prettyprint-override"><code>id  select_type         table       type    possible_key    key     key_len     ref     rows        Extra
  1   PRIMARY             k           ALL     &lt;null&gt;          &lt;null&gt;  &lt;null&gt;      &lt;null&gt;  44679       Using where; Using temporary; Using filesort
  1   PRIMARY             i           ALL     &lt;null&gt;          &lt;null&gt;  &lt;null&gt;      &lt;null&gt;  44679       Using where; Using join buffer (flat, BNL join)
  2   DEPENDENT SUBQUERY  t           ALL     &lt;null&gt;          &lt;null&gt;  &lt;null&gt;      &lt;null&gt;  44679       Using where; Using filesort
  
  </code></pre>
  
  <p><strong>Edit2</strong>
  I changed type for my <code>deviceTime</code> column to <code>DATETIME</code>. I created the index like this</p>
  
  <pre class="lang-sql prettyprint-override"><code>create index imports_alarmstate_index
      on imports (alarmState);
  
  create index imports_devicetime_index
      on imports (deviceTime);
  
  create index imports_variablename_index
      on imports (variableName);
  
  </code></pre>
  
  <p>I modified my query to use <code>MIN()</code> instead of <code>mysql Order BY ... Limit 1</code>.
  Now my query look like</p>
  
  <pre class="lang-sql prettyprint-override"><code>select k.deviceTime     as stime,
         k.variableName   as svar,
         k.alarmState     as sstate,
         i.deviceTime,
         i.variableName,
         i.alarmState,
         timestampdiff (second, i.deviceTime, k.deviceTime) as diff
  from imports k
  join imports i on   i.variableName = k.variableName and
                      i.deviceTime = (select MIN(t.deviceTime)
                                      from imports t
                                      where   t.variableName = k.variableName and
                                              t.deviceTime &gt; k.deviceTime and
                                              t.alarmState ='NORMAL'
                                      )
  where k.alarmState &lt;&gt; 'NORMAL' 
  </code></pre>
  
  <p>I use <code>timestampdiff()</code> instead of <code>datediff()</code> because the timestamp format is more easy to order.
  My where condition <code>k.alarmState &lt;&gt; 'NORMAL'</code> has changed because sommetime the <code>alarmState</code> can take another state in particular condition, but this new state is like an <code>Active</code> state</p>
  
  <p>Here my new <code>EXPLAIN</code></p>
  
  <pre class="lang-sql prettyprint-override"><code>id  select_type         table   type    possible_keys                                                                   key                         key_len     ref                     rows    filtered    Extra
  1   PRIMARY             k       ALL     imports_variablename_index,imports_alarmstate_index                             &lt;null&gt;                      &lt;null&gt;      &lt;null&gt;                  45474   50          Using where
  1   PRIMARY             i       ref     imports_devicetime_index,imports_variablename_index                             imports_devicetime_index    5           func                    1       100         Using where
  2   DEPENDENT SUBQUERY  t       ref     imports_devicetime_index,imports_variablename_index,imports_alarmstate_index    imports_variablename_index  1022        Alarms.k.variableName   29      50          Using where
  
  </code></pre>
  
  <p>When I execute my query, I get </p>
  
  <pre><code>34567 rows retrieved starting from 1 in 3 m 26 s 135 ms (execution: 158 ms, fetching: 3 m 25 s 977 ms)
  </code></pre>
  
  <p>I think 3 minutes is a bit long no? any other optimisation or suggestion?</p>
  
  <p>Thank you !</p>