Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.1.22
-
None
-
Production
Description
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) |