http://www.tums.com
On Friday, December 7, 2001, at 06:58 AM, Erik Price wrote: > > On Thursday, December 6, 2001, at 08:35 PM, Arjen G. Lentz wrote: > >> There is also the one part that limits your selection (shoeshine.com). >> So that >> is very important. I've quoted the table structure of the >> subprojectweb table >> above, and the 'subproject_name' field is not indexed. Therefore the >> server >> will have to do a table scan on the subprojectweb table, to find the >> rows that >> match the specified name. >> So, add an index on that field, or at least a prefix. You don't need >> to index >> all 64 chars of it. > > I see. If I mentally apply this advice to the rest of my database, it > seems that I should index (or apply a prefix index) to any column that > holds search criteria -- such as files.file_name or > subprojectweb.subproject_name. I would assume that I should really > only do this with the most-commonly-searched columns, because indexing > every column would be unwieldy. > > Perhaps I should just go with what I have, and once the database (which > will only have 30 users or so for now) is cooking, I can go in and > analyze the queries and the optimizer and determine where best to place > the indexes. For now, the only indexes I have are the PRIMARY KEYs on > the "*_id" columns (for unique ID numbers) and the UNIQUE INDEX on the > "middle table (foreign key)" between "files" and "projects". I read in > my book that one way to do this testing is to just reconstruct a table > using CREATE TEMPORARY TABLE and go ahead and make changes to this > temporary table, and anything that seems to run faster can be later > applied to the actual table. > >> Re-order the list of tables in the FROM, and put the >> "subprojectweb.subproject_name = 'shoeshine.com' " bit first after the >> WHERE, >> that will also make it clearer for you to read. > > Is this just for my own personal clarity? I was under the impression > that the exact order of the JOINs wouldn't matter very much, but I > haven't found any evidence of this yet. > > > Thanks very much for responding to my questions about this, Arjen. > > > > Erik > > > --------------------------------------------------------------------- > 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 <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --- René Fournier [EMAIL PROTECTED] --------------------------------------------------------------------- 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
