I'm trying to get a handle on how an increase inf physical memory will affect the query time for a large table. Here's the set up.
I'm running MySQL 3.23.37 on RedHat 6.0. The server has a 700Mhz process with 1.5 Gig of RAM. I'm querying a table with 47 million plus records that has 3 columns (int primary key, col 1 char(12), col 2 char(10)). I have the primary key index, an index on (col 1, col 2), and an index on col 2 only. I do searches on col 1 only, col 1 and 2, col 2 only. My key buffer is set at 700 MB. When I run a query "SELECT count(*) as count FROM tbl where col_1 like 'value1'", the query can take up to 45 minutes to bring back a result if there are large number (sometimes 1 million plus) of records that match 'value 1'. I've read the chapters in the manual about query optimisation and tuning the server and I'm trying to figure out what the best approach would be to speed up the query. Would putting more memory in the machine and increasing the key_buffer size solve the problem? Should I look at a processor upgrade as well? Are there any other suggestions that can make the query execute faster? Thanks in advance, Jeff --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
