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
-
Activity
Found this:
/* This flag can be used only in KEY::ext_key_flags */
|
#define HA_EXT_NOSAME 131072
|
maybe, this is the check that should have been used in 1859caf60b725f81f2ac6091eb44cb848a4a439a.
Committed a fix for this particular bug using HA_EXT_NOSAME: http://lists.askmonty.org/pipermail/commits/2016-July/009538.html
Branching off the part about the order of indexes into MDEV-10360.
@Sergei Petrunia i can confirm the issue is fixed with MariaDB 10.1.16.
Many thanks for the fast fixing.
Venâncio Ferreira, our pleasure. Thanks for the confirmation that the fix is working!
Ok, the reason behind key attributes depending on the order of
keys in the table is how add_first_key_parts variable is handled
inside TABLE_SHARE::init_from_binary_frm_image().
The function has a piece of logic starting with text
Do not extend the key that contains a componentdefined over the beginning of a field.That piece sets add_first_key_parts=0 and never resets it for the subsequent indexes.