# Issue

I have a table with close to a billion records, and need to query it with `HAVING`

. It's very slow (about 15 minutes on decent hardware). How to speed it up?

```
SELECT ((mean - 3.0E-4)/(stddev/sqrt(N))) as t, ttest.strategyid, mean, stddev, N,
kurtosis, strategies.strategyId
FROM ttest,strategies
WHERE ttest.strategyid=strategies.id AND dataset=3 AND patternclassid="1"
AND exitclassid="1" AND N>= 300 HAVING t>=1.8
```

I think the problem is `t`

cannot be indexed because it needs to be computed. I cannot add it as a column because the '3.0E-4' will vary per query.

Table:

```
create table ttest (
strategyid bigint,
patternclassid integer not null,
exitclassid integer not null,
dataset integer not null,
N integer,
mean double,
stddev double,
skewness double,
kurtosis double,
primary key (strategyid, dataset)
);
create index ti3 on ttest (mean);
create index ti4 on ttest (dataset,patternclassid,exitclassid,N);
create table strategies (
id bigint ,
strategyId varchar(500),
primary key(id),
unique key(strategyId)
);
```

`explain select..`

:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|

1 | SIMPLE | ttest | NULL | range | PRIMARY,ti4 | ti4 | 17 | NULL | 1910344 | 100.00 | Using index condition; Using MRR |

1 | SIMPLE | strategies | NULL | eq_ref | PRIMARY | PRIMARY | 8 | Jellyfish_test.ttest.strategyid | 1 | 100.00 | Using where |

# Solution

To be honest, I've never seen `HAVING`

being used like this; for 20+ years I've assumed it can only be used in `GROUP BY`

situations!

Anyway, IMHO you don't need it here, as Rick James points out, you can put it all in the `WHERE`

.
Rewriting it a bit I end up with:

```
SELECT ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) as t,
t.strategyid,
t.mean,
t.stddev,
t.N,
t.kurtosis,
s.strategyId
FROM ttest t,
JOIN strategies s
ON s.id = t.strategyid =
WHERE t.dataset=3
AND t.patternclassid="1"
AND t.exitclassid="1"
AND t.N>= 300
AND ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) >= 1.8
```

Most of that we can indeed foresee a reasonable index. The problem remains with the last calculation:

```
AND ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) >= 1.8
```

However, before we go to that: how many rows are there if you ignore this 'formula'? 100? 200? If so, indexing as foreseen in Rick James' answer should be sufficient IMHO.
If it's 1000's or many more than the question becomes: how much of those are thrown out by the formula? 1%? 50% 99%? If it's on the low side then again, indexing as proposed by Rick James will do. If however you only need to keep a few you may want to further optimize this and index accordingly.
From your explanation I understand that `3.0E-4`

is variable so we can't include it in the index.. so we'll need to extract the parts we can:

If my algebra isn't failing me you can play with the formula like this:

```
AND ((t.mean - 3.0E-4) / (t.stddev / sqrt(t.N))) >= 1.8
AND ((t.mean - 3.0E-4) ) >= 1.8 * (t.stddev / sqrt(t.N))
AND t.mean - 3.0E-4 >= (1.8 * (t.stddev / sqrt(t.N)))
AND - 3.0E-4 >= (1.8 * (t.stddev / sqrt(t.N))) - t.mean
```

So the query becomes:

```
SELECT ((t.mean - 3.0E-4)/(t.stddev/sqrt(t.N))) as t,
t.strategyid,
t.mean,
t.stddev,
t.N,
t.kurtosis,
s.strategyId
FROM ttest t,
JOIN strategies s
ON s.id = t.strategyid =
WHERE t.dataset=3
AND t.patternclassid="1"
AND t.exitclassid="1"
AND t.N>= 300
AND (1.8 * (t.stddev / sqrt(t.N))) - t.mean <= -3.0E-4
```

I'm not familiar with mysql but glancing the documentation it should be possible to include 'generated columns' in the index. So, we'll do exactly that with `(1.8 * (t.stddev / sqrt(t.N)) - t.mean)`

.

Your indexed fields thus become:

`dataset, paternclassid, exitclassid, N, (1.8 * (t.stddev / sqrt(t.N))) - t.mean)`

Note that the system will have to calculate this value for each and every row on insert (and possibly update) you do on the table. However, once there (and indexed) it should make the query quite a bit faster.

Answered By - deroby Answer Checked By - Terry (PHPFixing Volunteer)

## 0 Comments:

## Post a Comment

Note: Only a member of this blog may post a comment.