MySQL 5.6 – NULL Safe NOT LIKE Operation

mysql-5.6

I have InnoDB table with two columns (1) account(int) (2) comment(varchar).

I have index on (account,comment)

Now I want to run query like

select * from table where account = 1111 AND comment NOT LIKE 'abcd%';

How can i make the NOT LIKE null safe?

I mean, include all records whose comment not starts with 'abcd', even if its null value.

Best Answer

mysql > create table nsnl(a varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql > insert into nsnl values (null), ('asdf');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql > select * from nsnl;
+------+
| a    |
+------+
| NULL |
| asdf |
+------+
2 rows in set (0.00 sec)

mysql > select * from nsnl where a not like 'as%';
Empty set (0.00 sec)

mysql > select * from nsnl where a not like 'as%' or a is null;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

And that's how you can easily find that out yourself. Plus, when you do it yourself, you memorize it better. At least that works for me.

Related Solutions

Mysql Innodb as Key value store, performance

Joel, there is a lot of unknowns. If you post your my.cnf, queries, table definitions and queries, EXPLAIN plan output, then you might get more precise answers.

You say "disabled query caching and other caching" what 'other caching' are you referring to? What steps did you take to disable QC? Did you restart your MySQL server between tests to flatten any buffers? If no and the KVP table was smaller and fit into RAM then InnoDB might have been able to serve queries fitting a certain criteria from data in the innodb buffer pool.

You also refer to memcache. With MySQL 5.6 there is a memcache api. this means you can use memcache calls to access data stored in innodb. This will not be incidentally speeding up your queries. There is also another method of bypassing the SQL layer within MySQL called HandlerSocket. These are a couple of subjects that I would point you at should you continue to desire this type of access to your data with the added bonus of having MySQL persist the data to disk and also be crash safe through InnoDB's crash recovery process.

Mysql – query records to fetch by time stamp

Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60

SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;

or if TimeStamp is indexed, you can do

SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);

Give it a Try !!!

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why