Hi Erik, ----- Original Message ----- From: "Erik Price" <[EMAIL PROTECTED]>
> 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. As well as useless: the server can only use 1 index per table for any particular query. A good rule would be: find the most limiting factor in your search criteria, a field that is tested for something that will most limit the number of rows returned. > 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. In theory that is fine. However, when your user numbers grow, if you find later that you want to redesign some table structure (beyond simply adding indexes) for better results, it will be more tricky since it involves copying the db and putting new scripts into place. So it IS quite important to get the basic design right before your userbase starts to grow. > 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". Well, as noted, you need to verify that an index is actually being used, by looking at EXPLAIN SELECT .... In case of multiple indexes on a table, you'll also want check that the best one has been chosen. > 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. Sure. But adding/removing an index on a small data set is easy enough, you can try that kind of stuff on the main tables. > > 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. Join order *does* matter, the table with the search criteria should generally be checked first, most limiting the number of rows. Then the other tables are joined into the result using their foreign keys. You don't want te query to be performed based on the foreign keys, with the search criteria being applied last! For a regular join with commas, the optimiser will try to work out an optimal join order. However, you will need to have a look at the output from EXPLAIN, which will show the tables in the order they are joined. If it is non-optimial, you could modify your query by using STRAIGHT_JOIN or other tricks to get an optimal join order. > Thanks very much for responding to my questions about this, Arjen. You're quite welcome. This kind of stuff (optimising) is an important subject in MySQL training courses (www.mysql.com/training/). Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- 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
