Thanks so much for the response, it got me looking into a number of other issues with our current set up.
Here is the output from the SHOW statements; I have obfuscated some column names of the table:
--------------------------------------------------------------
SHOW CREATE TABLE table
CREATE TABLE `table` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`datetime_c` datetime NOT NULL,
`c1_id` int(10) NOT NULL,
`c2_id` int(10) NOT NULL,
`c3_id` int(10) NOT NULL,
`c4_id` int(10) NOT NULL,
`ip` tinytext NOT NULL,
`char_c` char(2) NOT NULL,
`c5_id` tinytext NOT NULL,
`url` text NOT NULL,
`bool_c` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `datetime_c` (`datetime`),
KEY `c3_id` (`c3_id`),
KEY `ip` (`ip`(15))
) ENGINE=InnoDB AUTO_INCREMENT=6851191 DEFAULT CHARSET=utf8
SHOW TABLE STATUS LIKE 'table'
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment
table InnoDB 10 Compact 6619306 134 890241024 0 529907712 1984954368 6848631
[CONTINUED]
Create_time Update_time Check_time Collation Checksum Create_options Comment
2011-01-22 00:45:02 NULL NULL utf8_general_ci NULL
SHOW VARIABLES LIKE '%buffer%'
Variable_name Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
join_buffer_size 4194304
key_buffer_size 134217728
myisam_sort_buffer_size 67108864
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 8388608
read_rnd_buffer_size 1048576
sort_buffer_size 2097152
sql_buffer_result OFF
MySQL Version: 5.1.54-log
--------------------------------------------------------------
You are 100% right about the query being cached. I thought I had the query cache disabled however after testing it, the query on the test server took around 45 seconds, then on next submit only 7 seconds. This, however, still makes me think it'd be a vast improvement if implemented on the production server, as running the same query with no cache and the same table as posted above, it takes a good 11 minutes to complete! (compared to 1 minute 14 seconds on the production server, still without indexes and no cache).
The production server is just a single server with 12GB of RAM, and the main reason that was chosen is because I wanted the entire database to fit into memory. It's maintained by a managed dedicated hosting provider who I believe optimized the settings when we first started with them, however this was when our entire database consisted of MyISAM tables rather than InnoDB. From the looks of it, InnoDB is not optimized as of right now to take advantage of the 12GB of RAM.
Some questions if you don't mind :) :
1. Thinking of the size of the table, would changing the 'ip' column from a TINYTEXT to a VARCHAR(15) show any significant reduction in the size of the table? This column NEEDS to be indexed, and from what I've read (please correct me if I'm wrong), having an index on a prefix of 15 characters on a TINYTEXT causes MySQL to pull the entire row rather than just using the index directly as VARCHAR(15) would. Would the performance difference be noticeable?
2. Same as question #1, but for the 'url' column. I've read that Apache has a max URL length of 8,192 bytes, but usually gives errors around 4,000. Would it be better to change the 'url' column from a TEXT to a VARCHAR(8192)? This column is not used in any WHERE/GROUP BY/ORDER BY clauses, so as far as I know this DOES NOT need to be indexed.
3. Are the TINYTEXT and TEXT columns able to be fully placed into RAM?
4. After looking at the SHOW TABLE STATUS result, the 'Data_free' column is scaring me. Does this mean that this table only has roughly 1.85GB of space left? If so, is there a way to modify this and a couple of other tables so it can store more? This table is going to get large (currently at ~7 million rows, looking at 100 million rows or more).
5. What would be your recommended settings for the MySQL and InnoDB variables? The server is a Core i7 930, has 12GB of RAM and 2 74GB 15k SAS drives in RAID 1, and is running both Apache and MySQL.
6. We have a lot of temporary tables being created, as well as a lot of JOIN queries between this table and another one about 1/10th the size in both row count and actual size (pulling data in the same ratio). Should the tmp table and/or the join_buffer_size variables be modified? I can post the tmp table vars if needed.
7. Would the performance increase that comes with having the entire database in RAM be so much so that more complicated, composite indexes would become insignificant?
8. Just making sure, if the table is stored entirely within RAM, writes will still be performed on disk right?
9. Last question, I swear! :) If the entire database is able to be stored in RAM and performance just increases drastically, would there be any issue with getting a server that supports say 64GB of RAM so the database will always fit as it grows?
Thanks again, your help is greatly appreciated!