Would you need the DISTINCT if you change the query like so?
SELECT Site.Site_ID, Site, Status, Type
FROM Site
JOIN Project ON Site.Site_ID = Project.Site_ID
ORDER BY Site;
You may also want to just try your initial query without the distinct to
see if that is the issue. Also, do you have an index on the "Site"
column? The issue with this query is that you are pretty much selecting
everything from the Project table.
R.
-----Original Message-----
From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED]
Sent: Monday, May 01, 2006 11:05 AM
To: mysql@lists.mysql.com
Subject: FW: Optimizing DISTINCT searches
On 2006-05-01 11:55 AM, "Robert DiFalco" <[EMAIL PROTECTED]> wrote:
> Well, normally a DISTINCT has to do a type of sort and is slower than
> non-DISTINCT queries. Each field of the result set is considered in
> the DISTINCT logic. Can you modify the query so that it does not
> require the DISTINCT? Can you post the query?
Robert -
Query: SELECT DISTINCT Project.Site_ID, Site, Status, Type FROM
Project, Site WHERE Site.Site_ID = Project.Site_ID ORDER BY Site;
Site is the site name, Status and Type contain additional information
about the site, and Site_ID is the unique site id.
The Project table contains among other things a list of sites where the
projects are being done.
The results of this query are supposed to be a non-duplicated list of
sites that are associated with at least one project.
As the number of projects and sites have increased, this query is now
frequently in the slow query log.
Stephen P. Fracek, Jr.
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
