Hi All, I'm designing a rather large database, and I'm concerned about performance. Was wondering if anyone had any comments/advice?
The particular table I'm concerned about will have about 10 million lines, each referencing a physical item I need to track. Each item in the table will have a auto_increment primary key that will be indexed (of course). However, I will also want to search on these items by other fields... their current location, for example (and a few other fields). This table will also be very active-- probably 10-20 inserts/deletions a second, so I am concerned about performance with having lots of fields indexed. So, I thought, instead of indexing all of these fields in the single table, I was considering creating multiple auxillary "lookup" tables that would only have two fields for each of the 10 million lines. First, the field (indexed) I want to search on (item location, for example), and the second field being the auto_increment primary key for the "master" table. So, I could search on these other fields (find all items in location "A", for example), but the only field indexed in the main table will be the primary key, while the other search points will be easily accessible/searchable from the auxillary "lookup" tables. The main item table will still store the value, but it will not be indexed. Make sense? I feel like breaking the table apart, and having only a single index per table should help, rather than having a single table with multiple indexes. How have other people handled this sort of thing? TIA. -=-=-=-=-=-=-=- Brad LaJeunesse, PINES System Administrator Georgia Public Library Service www.georgialibraries.org "Scotty, I need warp speed in three minutes or we're all dead!" --Admiral James T. Kirk, Star Trek II: "The Wrath of Khan" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
