gshilpa

Insert Statement Take too long to insert - MYSQL

Apr 14th, 2018
127
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 10.15 KB | None | 0 0
  1. CREATE TABLE `patient_last_examined` (
  2.  `patient_last_examined_id` int(12) NOT NULL AUTO_INCREMENT,
  3.  `patient_id` int(12) NOT NULL,
  4.  `operator_id` int(12) NOT NULL,
  5.  `section_name` varchar(255) COLLATE latin1_general_ci NOT NULL,
  6.  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
  7. UPDATE CURRENT_TIMESTAMP,
  8.  `status` enum('0','1') COLLATE latin1_general_ci NOT NULL,
  9.  `save_or_review` int(11) NOT NULL COMMENT '1 = save, 2 = Reviewed',
  10.  `section_complete` int(1) NOT NULL COMMENT '1 = section associated with
  11. complete reviewed,0=section not associated with complete reviewed',
  12.  `section_complete_id` int(11) NOT NULL COMMENT 'it store complete section pk
  13. id',
  14.  `formid` int(11) NOT NULL DEFAULT '0',
  15.  `src_server` int(10) DEFAULT NULL,
  16.  `parent_id` int(10) DEFAULT NULL,
  17.  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  18.  PRIMARY KEY (`patient_last_examined_id`),
  19.  KEY `section_name` (`section_name`),
  20.  KEY `patient_id` (`patient_id`)
  21. ) ENGINE=MyISAM AUTO_INCREMENT=177622 DEFAULT CHARSET=latin1
  22. COLLATE=latin1_general_ci
  23.  
  24.  
  25.  
  26.  
  27. SHOW INDEX FROM patient_last_examined;
  28. Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
  29. patient_last_examined   0   PRIMARY 1   patient_last_examined_id    A   177621  NULL    NULL        BTREE        
  30. patient_last_examined   1   section_name    1   section_name    A   59207   NULL    NULL        BTREE        
  31. patient_last_examined   1   patient_id  1   patient_id  A   25374   NULL    NULL        BTREE
  32.  
  33.  
  34.  
  35.  
  36. Aborted_clients 0
  37. Aborted_connects 1
  38. Binlog_cache_disk_use 0
  39. Binlog_cache_use 0
  40. Binlog_stmt_cache_disk_use 0
  41. Binlog_stmt_cache_use 0
  42. Bytes_received 23502509
  43. Bytes_sent 39704702
  44. Com_admin_commands 162
  45. Com_assign_to_keycache 0
  46. Com_alter_db 0
  47. Com_alter_db_upgrade 0
  48. Com_alter_event 0
  49. Com_alter_function 0
  50. Com_alter_instance 0
  51. Com_alter_procedure 0
  52. Com_alter_server 0
  53. Com_alter_table 0
  54. Com_alter_tablespace 0
  55. Com_alter_user 0
  56. Com_analyze 0
  57. Com_begin 0
  58. Com_binlog 0
  59. Com_call_procedure 0
  60. Com_change_db 110
  61. Com_change_master 0
  62. Com_change_repl_filter 0
  63. Com_check 0
  64. Com_checksum 0
  65. Com_commit 0
  66. Com_create_db 0
  67. Com_create_event 0
  68. Com_create_function 0
  69. Com_create_index 0
  70. Com_create_procedure 0
  71. Com_create_server 0
  72. Com_create_table 5
  73. Com_create_trigger 0
  74. Com_create_udf 0
  75. Com_create_user 0
  76. Com_create_view 0
  77. Com_dealloc_sql 0
  78. Com_delete 1
  79. Com_delete_multi 0
  80. Com_do 0
  81. Com_drop_db 0
  82. Com_drop_event 0
  83. Com_drop_function 0
  84. Com_drop_index 0
  85. Com_drop_procedure 0
  86. Com_drop_server 0
  87. Com_drop_table 1
  88. Com_drop_trigger 0
  89. Com_drop_user 0
  90. Com_drop_view 0
  91. Com_empty_query 0
  92. Com_execute_sql 0
  93. Com_explain_other 0
  94. Com_flush 0
  95. Com_get_diagnostics 0
  96. Com_grant 0
  97. Com_ha_close 0
  98. Com_ha_open 0
  99. Com_ha_read 0
  100. Com_help 0
  101. Com_insert 11649
  102. Com_insert_select 11
  103. Com_install_plugin 0
  104. Com_kill 0
  105. Com_load 0
  106. Com_lock_tables 0
  107. Com_optimize 0
  108. Com_preload_keys 0
  109. Com_prepare_sql 0
  110. Com_purge 0
  111. Com_purge_before_date 0
  112. Com_release_savepoint 0
  113. Com_rename_table 0
  114. Com_rename_user 0
  115. Com_repair 0
  116. Com_replace 0
  117. Com_replace_select 0
  118. Com_reset 0
  119. Com_resignal 0
  120. Com_revoke 0
  121. Com_revoke_all 0
  122. Com_rollback 0
  123. Com_rollback_to_savepoint 0
  124. Com_savepoint 0
  125. Com_select 33946
  126. Com_set_option 135
  127. Com_signal 0
  128. Com_show_binlog_events 0
  129. Com_show_binlogs 1
  130. Com_show_charsets 1
  131. Com_show_collations 1
  132. Com_show_create_db 0
  133. Com_show_create_event 0
  134. Com_show_create_func 1
  135. Com_show_create_proc 0
  136. Variable_name Value
  137. Com_show_create_table 4
  138. Com_show_create_trigger 0
  139. Com_show_databases 2
  140. Com_show_engine_logs 0
  141. Com_show_engine_mutex 0
  142. Com_show_engine_status 0
  143. Com_show_events 0
  144. Com_show_errors 0
  145. Com_show_fields 46
  146. Com_show_function_code 0
  147. Com_show_function_status 0
  148. Com_show_grants 1
  149. Com_show_keys 7
  150. Com_show_master_status 0
  151. Com_show_open_tables 0
  152. Com_show_plugins 0
  153. Com_show_privileges 0
  154. Com_show_procedure_code 0
  155. Com_show_procedure_status 0
  156. Com_show_processlist 0
  157. Com_show_profile 0
  158. Com_show_profiles 0
  159. Com_show_relaylog_events 0
  160. Com_show_slave_hosts 0
  161. Com_show_slave_status 0
  162. Com_show_status 12
  163. Com_show_storage_engines 0
  164. Com_show_table_status 35
  165. Com_show_tables 52
  166. Com_show_triggers 0
  167. Com_show_variables 20
  168. Com_show_warnings 0
  169. Com_show_create_user 0
  170. Com_shutdown 0
  171. Com_slave_start 0
  172. Com_slave_stop 0
  173. Com_group_replication_start 0
  174. Com_group_replication_stop 0
  175. Com_stmt_execute 0
  176. Com_stmt_close 0
  177. Com_stmt_fetch 0
  178. Com_stmt_prepare 0
  179. Com_stmt_reset 0
  180. Com_stmt_send_long_data 0
  181. Com_truncate 11
  182. Com_uninstall_plugin 0
  183. Com_unlock_tables 0
  184. Com_update 8082
  185. Com_update_multi 0
  186. Com_xa_commit 0
  187. Com_xa_end 0
  188. Com_xa_prepare 0
  189. Com_xa_recover 0
  190. Com_xa_rollback 0
  191. Com_xa_start 0
  192. Com_stmt_reprepare 0
  193. Connection_errors_accept 0
  194. Connection_errors_internal 0
  195. Connection_errors_max_connections 0
  196. Connection_errors_peer_address 0
  197. Connection_errors_select 0
  198. Connection_errors_tcpwrap 0
  199. Connections 5132
  200. Created_tmp_disk_tables 60
  201. Created_tmp_files 6
  202. Created_tmp_tables 5164
  203. Delayed_errors 0
  204. Delayed_insert_threads 0
  205. Delayed_writes 0
  206. Flush_commands 1
  207. Handler_commit 124
  208. Handler_delete 2341
  209. Handler_discover 0
  210. Handler_external_lock 120510
  211. Handler_mrr_init 0
  212. Handler_prepare 0
  213. Handler_read_first 4970
  214. Handler_read_key 64672
  215. Handler_read_last 1696
  216. Handler_read_next 750631
  217. Handler_read_prev 74207
  218. Handler_read_rnd 10355
  219. Handler_read_rnd_next 498809547
  220. Handler_rollback 0
  221. Handler_savepoint 0
  222. Handler_savepoint_rollback 0
  223. Handler_update 8033
  224. Handler_write 49110
  225. Innodb_buffer_pool_dump_status Dumping of buffer pool not started
  226. Innodb_buffer_pool_load_status Buffer pool(s) load completed at 180413 23:00:54
  227. Innodb_buffer_pool_resize_status
  228. Innodb_buffer_pool_pages_data 24213
  229. Innodb_buffer_pool_bytes_data 396705792
  230. Innodb_buffer_pool_pages_dirty 0
  231. Innodb_buffer_pool_bytes_dirty 0
  232. Innodb_buffer_pool_pages_flushed 164
  233. Innodb_buffer_pool_pages_free 303244
  234. Innodb_buffer_pool_pages_misc 203
  235. Innodb_buffer_pool_pages_total 327660
  236. Innodb_buffer_pool_read_ahead_rnd 0
  237. Variable_name Value
  238. Innodb_buffer_pool_read_ahead 0
  239. Innodb_buffer_pool_read_ahead_evicted 0
  240. Innodb_buffer_pool_read_requests 529906
  241. Innodb_buffer_pool_reads 24176
  242. Innodb_buffer_pool_wait_free 0
  243. Innodb_buffer_pool_write_requests 52630
  244. Innodb_data_fsyncs 26
  245. Innodb_data_pending_fsyncs 0
  246. Innodb_data_pending_reads 0
  247. Innodb_data_pending_writes 0
  248. Innodb_data_read 396202496
  249. Innodb_data_reads 24262
  250. Innodb_data_writes 195
  251. Innodb_data_written 6221824
  252. Innodb_dblwr_pages_written 130
  253. Innodb_dblwr_writes 3
  254. Innodb_log_waits 0
  255. Innodb_log_write_requests 3769
  256. Innodb_log_writes 10
  257. Innodb_os_log_fsyncs 15
  258. Innodb_os_log_pending_fsyncs 0
  259. Innodb_os_log_pending_writes 0
  260. Innodb_os_log_written 1402368
  261. Innodb_page_size 16384
  262. Innodb_pages_created 36
  263. Innodb_pages_read 24177
  264. Innodb_pages_written 164
  265. Innodb_row_lock_current_waits 0
  266. Innodb_row_lock_time 0
  267. Innodb_row_lock_time_avg 0
  268. Innodb_row_lock_time_max 0
  269. Innodb_row_lock_waits 0
  270. Innodb_rows_deleted 2341
  271. Innodb_rows_inserted 0
  272. Innodb_rows_read 322420
  273. Innodb_rows_updated 0
  274. Innodb_num_open_files 76
  275. Innodb_truncated_status_writes 0
  276. Innodb_available_undo_logs 128
  277. Key_blocks_not_flushed 0
  278. Key_blocks_unused 843530
  279. Key_blocks_used 13838
  280. Key_read_requests 1043706
  281. Key_reads 13435
  282. Key_write_requests 60933
  283. Key_writes 60772
  284. Locked_connects 0
  285. Max_execution_time_exceeded 0
  286. Max_execution_time_set 0
  287. Max_execution_time_set_failed 0
  288. Max_used_connections 12
  289. Max_used_connections_time 2018-04-14 00:44:24
  290. Not_flushed_delayed_rows 0
  291. Ongoing_anonymous_transaction_count 0
  292. Open_files 2106
  293. Open_streams 0
  294. Open_table_definitions 1905
  295. Open_tables 1870
  296. Opened_files 11861
  297. Opened_table_definitions 1927
  298. Opened_tables 2768
  299. Performance_schema_accounts_lost 0
  300. Performance_schema_cond_classes_lost 0
  301. Performance_schema_cond_instances_lost 0
  302. Performance_schema_digest_lost 0
  303. Performance_schema_file_classes_lost 0
  304. Performance_schema_file_handles_lost 0
  305. Performance_schema_file_instances_lost 0
  306. Performance_schema_hosts_lost 0
  307. Performance_schema_index_stat_lost 0
  308. Performance_schema_locker_lost 0
  309. Performance_schema_memory_classes_lost 0
  310. Performance_schema_metadata_lock_lost 0
  311. Performance_schema_mutex_classes_lost 0
  312. Performance_schema_mutex_instances_lost 0
  313. Performance_schema_nested_statement_lost 0
  314. Performance_schema_prepared_statements_lost 0
  315. Performance_schema_program_lost 0
  316. Performance_schema_rwlock_classes_lost 0
  317. Performance_schema_rwlock_instances_lost 0
  318. Performance_schema_session_connect_attrs_lost 0
  319. Performance_schema_socket_classes_lost 0
  320. Performance_schema_socket_instances_lost 0
  321. Performance_schema_stage_classes_lost 0
  322. Performance_schema_statement_classes_lost 0
  323. Performance_schema_table_handles_lost 0
  324. Performance_schema_table_instances_lost 0
  325. Performance_schema_table_lock_stat_lost 0
  326. Performance_schema_thread_classes_lost 0
  327. Performance_schema_thread_instances_lost 0
  328. Performance_schema_users_lost 0
  329. Prepared_stmt_count 0
  330. Qcache_free_blocks 885
  331. Qcache_free_memory 58958760
  332. Qcache_hits 72836
  333. Qcache_inserts 29034
  334. Qcache_lowmem_prunes 0
  335. Qcache_not_cached 4911
  336. Qcache_queries_in_cache 7269
  337. Qcache_total_blocks 15519
  338. Variable_name Value
  339. Queries 132251
  340. Questions 132087
  341. Select_full_join 0
  342. Select_full_range_join 0
  343. Select_range 7948
  344. Select_range_check 0
  345. Select_scan 5337
  346. Slave_open_temp_tables 0
  347. Slow_launch_threads 0
  348. Slow_queries 11
  349. Sort_merge_passes 0
  350. Sort_range 30
  351. Sort_rows 1406
  352. Sort_scan 15
  353. Ssl_accept_renegotiates 0
  354. Ssl_accepts 0
  355. Ssl_callback_cache_hits 0
  356. Ssl_cipher
  357. Ssl_cipher_list
  358. Ssl_client_connects 0
  359. Ssl_connect_renegotiates 0
  360. Ssl_ctx_verify_depth 0
  361. Ssl_ctx_verify_mode 0
  362. Ssl_default_timeout 0
  363. Ssl_finished_accepts 0
  364. Ssl_finished_connects 0
  365. Ssl_server_not_after Dec 12 06:37:05 2027 GMT
  366. Ssl_server_not_before Dec 14 06:37:05 2017 GMT
  367. Ssl_session_cache_hits 0
  368. Ssl_session_cache_misses 0
  369. Ssl_session_cache_mode Unknown
  370. Ssl_session_cache_overflows 0
  371. Ssl_session_cache_size 0
  372. Ssl_session_cache_timeouts 0
  373. Ssl_sessions_reused 0
  374. Ssl_used_session_cache_entries 0
  375. Ssl_verify_depth 0
  376. Ssl_verify_mode 0
  377. Ssl_version
  378. Table_locks_immediate 53992
  379. Table_locks_waited 0
  380. Table_open_cache_hits 53758
  381. Table_open_cache_misses 2768
  382. Table_open_cache_overflows 870
  383. Tc_log_max_pages_used 0
  384. Tc_log_page_size 0
  385. Tc_log_page_waits 0
  386. Threads_cached 2
  387. Threads_connected 10
  388. Threads_created 12
  389. Threads_running 2
  390. Uptime 12798
  391. Uptime_since_flush_status 12798
Add Comment
Please, Sign In to add comment