MySQL query dilemma

[Mitch100degrees]

Okay, assume you have a blog where multiple authors can get credit for one entry and you store their userids in a field titled userids separated by commas like this: 1, 368, 9841

Now say you wanted to pull the blog post and their usernames along with it, but the usernames were stored in another table...

You could do this query: SELECT blogstable.*, GROUP_CONCAT(userstable.username ORDER BY FIND_IN_SET(userstable.userid, blogstable.userids) ASC) AS usernames FROM blogs AS blogstable LEFT JOIN users AS userstable ON (FIND_IN_SET(userstable.userid, blogstable.userids)) WHERE blogstable.bid = '1' GROUP BY blogstable.bid

Having bid as your PRIMARY KEY on the blogs table and It works using that index, but with one problem, it does a full table scan of your users table even with a index on your userid field.

Now you could just do this:

$blog = SELECT blogs.* FROM blogs WHERE bid = '1';

then SELECT username FROM users WHERE userid IN ($blog[userids])

and that works using both indexes.

My question to you is, is there anyway to avoid the full table scan that you know of, and if not, how would you personally setup such application to pull the blog and the contributing usernames in 1 query or is that even possible using indexes?

[Eikinskjaldi]

Originally Posted by Mitch100degrees

Okay, assume you have a blog where multiple authors can get credit for one entry and you store their userids in a field titled userids separated by commas like this: 1, 368, 9841

Well I wouldn't have done that for a start.

Set up another table with one line for blog id with a single userid, with multiple rows for multiple users. Either not bother with a primary key or set up a dummy auto-increment field if not having a primary keys gives you the willies (still index both fields)

then you can do

SELECT blogstable.*, group_concat(distinct usertable.username)
FROM blogstable
JOIN blogusers USING (bid)
JOIN usertable on (usertable.userid = blogusers.userid)
WHERE blogstable.bid = 1
GROUP BY blogstable.bid

My question to you is, is there anyway to avoid the full table scan that you know of

Not if you are using find_in_set there isn't.