Hi Erik, ----- Original Message ----- From: "Erik Price" <[EMAIL PROTECTED]>
> mysql> SHOW COLUMNS FROM subprojectweb; > +-----------------+-----------------------+------+-----+---------+ > | Field | Type | Null | Key | Default | > +-----------------+-----------------------+------+-----+---------+ > | subproject_id | mediumint(8) unsigned | | PRI | NULL | > | subproject_name | varchar(64) | | | | > +-----------------+-----------------------+------+-----+---------+ > 2 rows in set (0.01 sec) > > So if I want to find out which files were used in a web subproject > called "shoeshine.com", do I really use the following query? > > SELECT files.file_name > FROM files, projfile, projects, subprojectweb > WHERE files.file_id = projfile.file_id > AND projfile.project_id = projects.project_id > AND projects.subprojectweb_id = subprojectweb.subproject_id > AND subprojectweb.subproject_name = 'shoeshine.com' ; > > This is a HUGE query. Well, maybe it's not -- maybe there are lots of > queries this big. The point is, I rarely see reference to a query this > big on this list or in my book. Is this the norm? Or am I doing > something weird, am I "not getting" this relational database concept? Well, books give examples that need to be clear. A 3 or 4-way join is theoretically the same as a 2-way join, the same 'rules' apply. The above query is actually quite small ;-) There is a prob with the above query though, in that it will turn out to be very slow when your tables grow. When you look at the WHERE clause, most of it just deals with the table relationships. That's fine. 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. 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. Then, run the query with EXPLAIN in front of the SELECT, and look at the output. With the EXPLAIN command, the server will tell you how the optimiser has organised query execution: in which order will the tables be joined, and which indexes can and will be used. You'll want to set up your query so that the most limiting stuff is done first, and then the other tables are just used based on their primary key, one for each matching row in the first table. For this type of query, that'd be the fastest route. If that's not what happens (see EXPLAIN) you can rephrase your query so that it is (for instance by using STRAIGHT_JOIN). There's quite a bit of background info involved in the above, which I can't possibly post in a short message. So, it's just some things to point you in the right direction.... By the way, this is the kind of cool stuff you learn as part of a MySQL training course (www.mysql.com/training/), the bit we call "Make The Dolphin Fly" ;-) 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
