Select Distinct query question

[RyanSF07]

Hi,

 

I have database of authors, and articles they've contributed.  I want to call out a list of all authors who have content that is currently "featured" on the site, or "new."

 

I don't want for example:

jim

jim

jim

karen

karen

miko

miko

miko

miko

 

Instead I'd like a list like this:

 

jim

karen

miko

 

This query below seems to freeze my site.  I must be doing something wrong here. How better to write this?

 

$sql = mysql_query ("SELECT DISTINCT registered_users.id, registered_users.user_name, video.id, video.user_id, video.title, video.pass_text FROM registered_users, video
WHERE registered_users.id = video.user_id AND video.pass_text = 'featured' OR video.pass_text = 'new' ORDER BY registered_users.user_name") or die(mysql_error());

while($row = mysql_fetch_array( $sql )) {

$author = $row[registered_users.id];

$content .= "<p>
<a href = \"page.php?id=$author\" target='_self'>$row[user_name]</a></p>";
}

 

Thanks,

Ryan

[QuickOldCar]

Look into making indexes for mysql on those fields to get faster results.

 

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

[RyanSF07]

thank you -- i will definitely look into this.  but I mean the sever is unavailable for more than 15 min. it has happened twice now.

 

is the query the cause?  regardless if it's slow, is it somehow crashing the server -- and if so -- can you help me understand why?

 

thanks again, Quick

 

Ryan

[BlueSkyIS]

I suspect it's the massive number of results that match your WHERE clause, then having to put those into a temp table to ORDER:

 

WHERE registered_users.id = video.user_id AND video.pass_text = 'featured' OR video.pass_text = 'new'

 

You should add parentheses to limit the matches:

 

probably: WHERE registered_users.id = video.user_id AND (video.pass_text = 'featured' OR video.pass_text = 'new')

 

 

[Maq]

Seems more of a SQL question rather than PHP, moving there.

[RyanSF07]

K.  Thanks Maq.  The query runs now -- but "Distinct" isn't working....  I'm getting:

 

jim

jim

jim

etc

 

instead one instance of "jim"

 

thanks again for your help with this

[QuickOldCar]

Any time you have to sort/order and pull from specific fields, but only this or that but exclude this....it will consume more memory and cpu, time out even.

 

It sometimes has to run through all those results a few times just to get the desired results.

 

Indexing it properly should help it a lot, but still don't expect blazing fast results.

 

Is almost a skill to get indexing set up properly.

By using EXPLAIN SELECT you can see which index it would use or any possible keys it could use for it.

 

Here's an older article, but explains the process very nicely on how to see and what to index and the orders

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

[RyanSF07]

Thank you Quick -- I am moving in this direction for sure and reading up on it.

 

I'd like to get the query working properly -- then take some time to make sure I'm understanding optimization properly.

 

The query now works and is fast -- but DISTINCT isn't working the way I thought it would. 

 

I want to see an author's name once -- even if they have contributed 100 articles.  (Not that author's name 100 times).

 

I tried this:

(DISTINCT registered_users.id),

 

but that didn't work. 

 

I'm googling... please let me know if you have ideas on how to get each author's name echoed out once instead of several.

 

thanks again Quick!

 

 

[QuickOldCar]

I think the problem lies in SELECT DISTINCT, as it will only display a distinct result but based on just different values, so don't think will work like you had hoped to.

 

Essentially what you were doing is grabbing any videos and showing the users name, so it will associate the users name because the name and the video information were all truly unique when together.

 

 

[RyanSF07]

OK -- I'm with you.

 

Any ideas on what to try instead -- or what keywords to be looking into.

 

thanks again for your help,

Ryan

[RyanSF07]

Solved it with GROUP By -- removed DISTINCT

[QuickOldCar]

ha, something as easy as that.

 

cool