I believe I've encountered a database scaling issue. I have a table with nearly a million rows in it now and our rails application appears to be hanging whenever we try to create a new instance. I notice this also happens when we destroy a record as well. However, find and update are extremely quick.
Here's some information on the table:
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
| instances | InnoDB | 10 | Compact | 972380 | 1897 | 1845493760 | 0 | 152846336 | 0 | 922976 | 2009-03-04 18:34:07 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 5120 kB |
And also:
+-------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| card_id | int(11) | YES | MUL | NULL | |
| email_id | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
| subject | varchar(255) | YES | | NULL | |
| prototype | text | YES | | NULL | |
| forwards_count | int(11) | YES | | 0 | |
| recipients_count | int(11) | YES | | 0 | |
| sent_at | datetime | YES | MUL | NULL | |
| created_at | datetime | YES | | NULL | |
| body | text | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| original_id | int(11) | YES | | NULL | |
| saved | tinyint(1) | YES | | 1 | |
| notify_on_receipt | tinyint(1) | YES | | 0 | |
| total_views | int(11) | YES | | 0 | |
| total_sends | int(11) | YES | | 0 | |
| public | tinyint(1) | YES | MUL | 0 | |
| approved | tinyint(1) | YES | MUL | 0 | |
| sender_copy | int(11) | YES | | NULL | |
| recipient_base64 | varchar(255) | YES | | NULL | |
| user_generated_video_id | int(11) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| position | int(11) | YES | | NULL | |
| messages | text | YES | | NULL | |
+-------------------------+--------------+------+-----+---------+----------------+
I wa wondering if there is anything I can try to look into to further diagnose the issue. Or configurations I should attempt to change as possible solutions. Essentially a find or update take around 8-12ms but a create or destroy statement will take over 10 seconds to complete.