0

I am trying to enhance the search performance of a table based on whether certain columns contain values.

I have the following columns :

id | link | text | date | lat | long | type

If a record has a value in lat & long it can be shown on a map. It’s considered a “dot”

If a record has a value in date & lat & long it is considered an “event”

If a record has a value in type and text it is considered “news”

With over 1 million records in the table, what is the best way to index so I can quickly extract all “dots” or all “events” or all “news”?

To reiterate, I don’t care to index the actual values of these columns, only if there is ANY value in that column.

I tried creating an index of multiple columns but it used the values of the columns, and so many often unique values resulted in slow performance.

I considered using a “hash” based on which columns were not null, but I couldn’t extract those 3 variations beaded on the hash result.

Any advice appreciated.

2
  • EAV is a clumsy schema design. Adding a switch to handle different types makes it worse.
    – Rick James
    Jun 2, 2020 at 0:19
  • Depending on the cardinality of the "generated" column (see some of the answers), an index may be ignored by the Optimizer. All answers (so far) will still do a table scan.
    – Rick James
    Jun 24, 2020 at 17:50

3 Answers 3

2

Use binary (or some integer as binary) or SET generated encoding column. For example:

CREATE TABLE tablename 
( id SERIAL PRIMARY KEY,
  linkfield  VARCHAR(255),
  textfield TEXT,
  datefield DATE,
  lat DECIMAL(10,8),
  long DECIMAL(10,8),
  type ENUM('value1', 'value2', ... , 'valueN'),
  record_type_mask TINYINT UNSIGNED
                   AS ( (linkfield  IS NOT NULL) *  1 +
                        (textfield  IS NOT NULL) *  2 + 
                        (datefield  IS NOT NULL) *  4 + 
                        (lat + long IS NOT NULL) *  8 +
                        (type       IS NOT NULL) * 16  
                      )
);

then use either constant values.

For example, if you want to select

If a record has a value in date & lat & long it is considered an “event”

you will use

WHERE record_type_mask = 12

because the value 4 marks present lat and long, and the value 8 marks datefield.

If you need records which have datefield set you may use masking:

WHERE record_type_mask & 4
-- or 
WHERE record_type_mask IN (4,5,6,7,12,13,14,15,20,21,22,23,28,29,30,31)
5
  • ah yes, this what I meant by trying a "hash", but I couldn't quite get it. This looks like it might work I'll try
    – Leon
    May 31, 2020 at 10:09
  • The & 4 approach will do a table scan (or maybe an index scan). The IN approach might use an index.
    – Rick James
    Jun 2, 2020 at 0:22
  • The SET datatype can do similar stuff to the "mask" you have.
    – Rick James
    Jun 2, 2020 at 0:27
  • @RickJames Yes, I have said "binary ... or SET ... column".
    – Akina
    Jun 2, 2020 at 4:26
  • 1
    DECIMAL(10,8) has excessive precision. And it chops off nearly half the world -- by disallowing longitudes >= 100.
    – Rick James
    Jun 24, 2020 at 17:47
1

If you have an INDEX on a column (or an INDEX starting with that column), then this expression is very fast:

EXISTS ( SELECT 1 FROM tbl WHERE col IS NOT NULL )

Depending on what else you do with the million rows, it may be better to have a News table, a Dot table, etc.

1

First, I would consider normalising this table into several, since it contains different kinds of facts. You can create a union view if you need the "API" to stay the same.

If not possible for one reason or another you can use a generated column:

create table T 
( txt text
, dt date
, lat int
, lng int
, tp int
, t_type text as (case
           when txt is     null and lat is not null and lng is not null then 'DOT'
           when txt is not null and lat is not null and lng is not null then 'EVT'
           else 'NWS'
                  end) STORED
);

Now you can add an index with "t_type" as its first column:

CREATE INDEX X1 ON T (t_type, ...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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