Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.15
-
None
-
Centos 7.2
Invision Power Suite 4.1.12
Description
I use Invision Power Suite 4.1.12 board software which have a query that became very slow since the update to MariaDB 10.1.15.
I have the following table in the database:
+-----------------------+---------------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-----------------------+---------------------+------+-----+---------+----------------+
|
| index_class | varchar(255) | YES | MUL | NULL | |
|
| index_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
|
| index_object_id | int(10) unsigned | NO | | 0 | |
|
| index_item_id | int(10) unsigned | NO | | 0 | |
|
| index_container_id | int(10) unsigned | NO | | 0 | |
|
| index_content | mediumtext | NO | MUL | NULL | |
|
| index_permissions | text | NO | | NULL | |
|
| index_author | int(10) unsigned | NO | MUL | 0 | |
|
| index_title | varchar(255) | YES | MUL | NULL | |
|
| index_date_created | int(10) unsigned | NO | MUL | 0 | |
|
| index_date_updated | int(10) unsigned | YES | MUL | NULL | |
|
| index_hidden | tinyint(1) | NO | MUL | 0 | |
|
| index_item_index_id | bigint(20) unsigned | YES | | NULL | |
|
| index_item_author | mediumint(8) | YES | | NULL | |
|
| index_is_last_comment | tinyint(1) | NO | | 0 | |
|
+-----------------------+---------------------+------+-----+---------+----------------+
|
I have about 1.6 Millions row in the table.
Each time a user goes to a page that needs to run this query, it will examine all the rows, taking about 7 seconds to do it. With MariaDB 10.1.14 it only examines some of them, taking only a few milliseconds.
Here is the slow query log of the query:
# Query_time: 7.511627 Lock_time: 0.000029 Rows_sent: 25 Rows_examined: 1648220
|
# Rows_affected: 0
|
# Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
|
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: Yes
|
#
|
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
# explain: 1 SIMPLE main ref object,index_date_updated,index_hidden,item,container index_hidden 1 const 1 25.00 100.00 100.00 Using where; Using filesort
|
#
|
|
|
SET timestamp=1467465949;
|
/*IPS\Content\Search\Mysql\_Query::search:308*/ SELECT main.* FROM `ibf_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) ) AND ( ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) AND index_is_last_comment=1 ) OR ( index_class='IPS\\forums\\Topic\\Post' AND index_is_last_comment=1 ) OR ( ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment') ) AND index_is_last_comment=1 ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET(24,index_permissions) OR FIND_IN_SET('m39839',index_permissions) ) ) AND index_hidden=0 AND index_date_updated>1466861141 ORDER BY index_date_updated DESC LIMIT 0,25;
|
Now, if i remove the "AND index_hidden=0" from the query, it becomes fast again.
So i believe the problems its there and it only happens on the last MariaDB version.
Attachments
Issue Links
- relates to
-
-
- Closed
-