-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[EMAIL PROTECTED] wrote:
| I am performing a query along the lines of the following:
|
| SELECT DISTINCT property.Internal_ID
| FROM property, owner_names
| WHERE property.Internal_ID = owner_names.Internal_ID
| AND [ other conditions ]
| ORDER BY owner_names.Name
|
| Without the order by clause this is a pretty quick query, but with it,
| things
| slow down considerably. The query then takes 5-10 times as long. :(
| the property table has 1,000,000+ records
| and there are an average of about 1.4 owner_names records for each
property
| record
|
| I'm not exactly sure what happens with the ORDER BY clause, because
| if a property record has more than one owner_name record associated with
| it, how does MySQL decide which to use for sorting?
|
| I'd guess that if there are more than one, it will take the first one in
| sort order.
|
| The owner_names table also has a field named Display_Order, which
determines
| in what order to display the names for mailing labels, etc.
| I'd really like the ORDER BY to only consider records with Display_Order =
| 1,
| but the SELECT to consider all owner_name records.
|
| I know you can put some types of expressions in an order by clause, but i
| don't
| know how to express the above concept in SQL.
|
| thanks
| sean peters
| [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
|
Is owner_names.Name indexed? If not, MySQL is probably going to need to
do a filesort after it has select all of the rows, which will take a
very long time...Use "EXPLAIN [your query]" to see what MySQL is really
going to do.
-Mark
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6-2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE9ScwdlyjUJM+7nP4RAghsAKCHTB+pKISrX9AP2s2UHuiD0/elTwCffQNv
ZwdnPDarmO8MPfz85NRSP2Y=
=N7g9
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
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
