List:General Discussion« Previous MessageNext Message »
From:Robert DiFalco Date:May 1 2006 9:58pm
Subject:RE: Optimizing DISTINCT searches
View as plain text  
How about something like this?

SELECT Site.Site_ID, Site, Status, Type 
FROM Site 
WHERE EXISTS( SELECT * FROM Project) 
ORDER BY Site;

I'm assuming Site_ID is unique in the Site table? 

-----Original Message-----
From: Stephen P. Fracek, Jr. [mailto:sfracek@stripped] 
Sent: Monday, May 01, 2006 1:27 PM
To: mysql@stripped
Subject: Re: Optimizing DISTINCT searches

On 2006-05-01 1:14 PM, "Robert DiFalco" <rdifalco@stripped> wrote:

> 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.

Robert -

Your query doesn't work - it finds ALL the rows in Project table and
hence repeats the sites..

I do have an index on the Site table, it is the Site_ID.  The
Project.Site_ID is also indexed.

Stephen P. Fracek, Jr.
sfracek@stripped


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1



Thread
Optimizing DISTINCT searchesJr. Stephen P. Fracek1 May
RE: Optimizing DISTINCT searchesRobert DiFalco1 May
  • FW: Optimizing DISTINCT searchesJr. Stephen P. Fracek1 May
RE: Optimizing DISTINCT searchesRobert DiFalco1 May
  • Re: Optimizing DISTINCT searchesJr. Stephen P. Fracek1 May
RE: Optimizing DISTINCT searchesRobert DiFalco1 May