[MDEV-26617] Index can't used on table, and successfully after re create using other schema with same structure Created: 2021-09-16  Updated: 2021-09-16

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.4.20
Fix Version/s: None

Type: Bug Priority: Major
Reporter: febriyant Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: index
Environment:

CentOS 7


 Description   

we have 2 database with same structure
On this case database A can't used index when I run select query with specific where clause

but on database B with same query index used smoothly

I solved this issue with this step

  • Backup Table on Database A
  • Backup Data only on Database A
  • Drop table on Database A
  • create table on Database A using create script from Database B
  • import data

after that table on Database A running using index 

CREATE TABLE `tgospfeed` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `company_id` varchar(50) DEFAULT '',
 
  `company_code` varchar(50) DEFAULT '',
 
  `owner` varchar(50) DEFAULT '',
 
  `receiver` varchar(50) DEFAULT '',
 
  `type` varchar(255) DEFAULT NULL,
 
  `content` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 
  `picture` varchar(255) DEFAULT NULL,
 
  `flag` varchar(50) DEFAULT 'INBOX',
 
  `sent_notif` int(11) DEFAULT 0,
 
  `created_by` varchar(50) DEFAULT NULL,
 
  `created_date` datetime DEFAULT current_timestamp(),
 
  `modified_by` varchar(50) DEFAULT NULL,
 
  `modified_date` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  PRIMARY KEY (`id`),
 
  KEY `idx_tgospfeed_company_id` (`company_id`),
 
  KEY `idx_tgospfeed_company_code` (`company_code`),
 
  KEY `idx_tgospfeed_receiver` (`receiver`),
 
  KEY `idx_tgospfeed_flag` (`flag`)
 
) ENGINE=InnoDB AUTO_INCREMENT=12916 DEFAULT CHARSET=utf8mb4


Generated at Thu Feb 08 09:46:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.