Jeff, Let's do some math here first. Your table has 47 Million rows. You have a primary key on an int, and indexes across 22 chars and 10 chars. You have 512MB of key buffer.
Now, assuming the indexes just have information on relevant fields (they're actually bigger than this since they have a row pointer too) you need 47M*4 + 47M*22 + 47M*10 bytes of memory for your keys. This works out to 47M * (4 + 22 + 10) = 47M * (36) = 1.6GB of memory to store your keys. You only have 512MB. Therefore, since MySQL can't keep the key in memory, it has to swap parts out to disk as it works on your query, which causes a huge performance decrease. If you don't need to search on inequalities or partial matches, I would recommend adding two columns -- an integer hash value for each of the char columns. Index those, instead of the char columns, and do your lookups on those. In this scenario, you will need 47M * (4 + 8 + 4) = 717MB of memory, which is much closer to what you have. If you aren't actively using the primary key, you may be able to squeeze the other two indexes in your available RAM. For a table that big, you should probably buy a little bit more RAM, if it will fit in your machine. RAM is cheap :) Steve Meyers On Fri, 2001-11-09 at 18:08, Jeff Isom wrote: > I'm trying to figure out how to optimize a query on a fairly large table. > I've been reading the MySQL documentation and have tried a few of the > suggestions, but not seem to have much effect on the query. > > The table contains three columns: > 1) int unsigned > 2) char(12) > 3) char(10) > > The table has 47,000,000 + rows. > > I have my key_buffer set to 512M. > > I have a primary key index on column 1, a multicolumn index (col2,col3) and > another single column index for col3. > > The columns are not unique, in fact, some column 2 has as may as 1,000,000 > rows with the same value. > > I am running MySQL 3.23.37 on RedHat 6.0 with 1 Gig memory and a single 700 > Mhz processor. > The hard drive is a single 18 Gig SCSI drive. > > I am searching on col 2 only, col 3 only, or col 2 and col 3. > > The queries on single columns can take as long a 6 minutes to execute. > > Any suggestions on how I can further optimize the setup to get better > results? > > Thanks, > 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
