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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
