> 500 mil rows
That's almost the largest table discussed on these forums.
> Col 2 varchar(75) 3000 +values
> Col 3 varchar(15) 3 possible values
In a large table? Normalize! Put col2 into another table; JOIN with a SMALLINT UNSIGNED. Ditto with col3 and a TINYINT UNSIGNED, or ENUM. Those changes will shrink the data and index size significantly, which will improve performance.
Possibly even better would be to put both of them into one table, with somewhere between 3000 and 3*3000 rows, with a SMALLINT UNSIGNED.
PARTITIONing does not necessarily provide any performance benefit. There are a few, hard to conjure up, cases where it does.
> performance while trying to generate aggregate counts
* Let's see the queries
* Consider summary tables
* Is the data unchanged (not UPDATEd, not DELETEd) once it is INSERTed?
To assist in analyzing slow SELECTs, please provide
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?
How is the data loaded? (The answer may impact the SELECTs.)
Munch on these, re Summary Tables:
http://forums.mysql.com/read.php?125,393184 (Storing a large, dense tensor (35gb scientific data) -- is mysql appropriate?)
http://forums.mysql.com/read.php?24,372008 (Index performance)
http://forums.mysql.com/read.php?10,364300 (Thinking about sorting articles..)
http://forums.mysql.com/read.php?52,359476 (Slow 'sending data' phase on mySQL query, for simple query)
http://forums.mysql.com/read.php?24,355548 (views, unions and different dbs)
http://forums.mysql.com/read.php?106,288561 (Partitioning with range(to_days) issue)
http://forums.mysql.com/read.php?10,298557 (UNIX_TIMESTAMP Duplicate key)
http://forums.mysql.com/read.php?20,294180 (Multiple complex queries)
http://forums.mysql.com/read.php?125,287739 (Large table designissue)
http://forums.mysql.com/read.php?10,282768 (Which storage engine?)
http://forums.mysql.com/read.php?10,276359 (should i go with myisam or innodb?)
http://forums.mysql.com/read.php?125,266570 (Best way to store/access large amounts of data?)
http://forums.mysql.com/read.php?24,263259 (Slow JOIN to convert IP to Country Code)
http://forums.mysql.com/read.php?10,263071 (Bitmap Index)
http://forums.mysql.com/read.php?24,253221 (InnoDB CPU Spikes)
http://forums.mysql.com/read.php?10,254332 (Very long query doubt... (Monster query...))
http://forums.mysql.com/read.php?125,252723 (Database Design for huge set of data)
http://forums.mysql.com/read.php?10,252593 (Counters)
http://forums.mysql.com/read.php?21,247124 (Solving table locking issues)
http://forums.mysql.com/read.php?10,247779 (compressing tables)
http://forums.mysql.com/read.php?125,245133 (Design approach for summary table by 3 items)