[#MDEV-14570] Performance Tuning question

I am unable to make this query use an index. It doesn't take long on database but still the number of times it is executed on database makes me feel like it has to be tuned. I tried adding index to column or even tried to order by id but still it doesn't use index. Any help is appreciated.

select * from Question where active=1 and (id_TestGroup=0 or id_TestGroup is null) and (user_type=0 or user_type=2) and ((41200>=version_min or version_min is null or version_min=0 or version_min='') and (41200<=version_max or version_max is null or version_max=0 or version_max='')) and 1=1 order by paid desc,rand();

*************************** 1. row ***************************

       Table: Question

Create Table: CREATE TABLE `Question` (

  `id` int(10) NOT NULL AUTO_INCREMENT,

  `text` varchar(255) DEFAULT NULL,

  `id_TestGroup` int(10) DEFAULT NULL,

  `skippable` tinyint(1) DEFAULT NULL,

  `user_type` int(2) DEFAULT '0',

  `active` tinyint(1) DEFAULT '0',

  `response_target` int(10) DEFAULT NULL,

  `randomize_order` tinyint(1) DEFAULT '0',

  `id_DeviceMode` int(11) DEFAULT '0',

  `roadblockURL` varchar(255) DEFAULT NULL,

  `id_Population` int(11) DEFAULT NULL,

  `version_min` int(10) DEFAULT NULL,

  `version_max` int(10) DEFAULT NULL,

  `paid` tinyint(1) DEFAULT NULL,

  `autoPubDateStart` datetime DEFAULT NULL,

  `autoPubDateEnd` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8mb4

MariaDB [sa1]> show indexes from Question;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Question |          0 | PRIMARY  |            1 | id          | A         |         215 |     NULL | NULL   |      | BTREE      |         |               |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

MariaDB [sa1]> explain select * from Question where active=1 and (id_TestGroup=0 or id_TestGroup is null) and (user_type=0 or user_type=2) and ((41200>=version_min or version_min is null or version_min=0 or version_min='') and (41200<=version_max or version_max is null or version_max=0 or version_max='')) and 1=1 order by paid desc,rand();

+------+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |

+------+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

|    1 | SIMPLE      | Question | ALL  | NULL          | NULL | NULL    | NULL |  215 | Using where; Using temporary; Using filesort |

+------+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

1 row in set (0.00 sec)

[MDEV-14570] Performance Tuning question Created: 2017-12-04 Updated: 2017-12-14 Resolved: 2017-12-14
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	10.1.22
Fix Version/s:	N/A

[MDEV-14570] Performance Tuning question Created: 2017-12-04 Updated: 2017-12-14 Resolved: 2017-12-14