Query Filter Help

I'm trying to figure out a way to write my query so that I don't return results of all the same insightsource when querying out a limit of 5.  I want to have unique insightsources returned.

Attached is an example of my dataset.  As you can see they are all clumped together and I may have 20+ insightsources all concurrent.
example.csv

Here is my current query
select title, link, date_format(regdate,'%h:%i:%s %p') as createDate, ins.name as sourceName
          from insights i
          inner join insightsources ins on i.insightsource = ins.id
          order by i.id desc
          limit 5

Open in new window

LVL 12
Nathan RileyFounderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

SELECT 	O.id,
	O.title,
	O.link,
	O.img,
	O.fileunder,
	O.category,
	O.status,
	O.fulltexts,
	O.type,
	O.insightsource
FROM 	tableName O
WHERE	O.id IN (
	SELECT	TOP 5 I.id
	FROM	tableName I
	WHERE 	I.insightsource = O.insightsource
	ORDER BY I.title
);

Open in new window

0
Nathan RileyFounderAuthor Commented:
I'm using mysql.  The top command doesn't appear to work.  If I try LIMIT then the query also fails with: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
0
ste5anSenior DeveloperCommented:
E.g.

 
SELECT	*
FROM	(
	SELECT	*,
		@rank := IF(@current_insightsource = insightsource, @rank + 1, 1) AS country_rank,
		@current_insightsource := insightsource 
	FROM	tableName
	ORDER BY insightsource, title DESC
) ranked
WHERE rank <= 5;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nathan RileyFounderAuthor Commented:
Hmm..just times out with a internal server error after running for 30+ seconds.
0
ste5anSenior DeveloperCommented:
Yup, typo:

SELECT	*
FROM	(
	SELECT	*,
		@rank := IF(@current_insightsource = insightsource, @rank + 1, 1) AS rank,
		@current_insightsource := insightsource 
	FROM	tableName
	ORDER BY insightsource, title DESC
) ranked
WHERE rank <= 5;

Open in new window

0
Nathan RileyFounderAuthor Commented:
Is the query very efficient this way?

I keep letting it run for 60 seconds or so then receive:
Error in Processing Request
Error code: 500
Error text: Internal Server Error
0
ste5anSenior DeveloperCommented:
Well, here's a working SqlFiddle.
0
Nathan RileyFounderAuthor Commented:
Yeah, even on there with the tiny dataset it's taking ages to load for me.  This table has about 200k rows, so I think that might be why it's timing out.
0
ste5anSenior DeveloperCommented:
Have you created indices on the used columns?
0
Nathan RileyFounderAuthor Commented:
Yes I have.
0
ste5anSenior DeveloperCommented:
hmm, sounds like you need to do some hardware optimization.. 200k rows isn't that much, even for MySQL.

btw, what engine do you use?
0
Nathan RileyFounderAuthor Commented:
I'm on a dedicated box, quad core, 16gb ram etc...

InnoDB
0
ste5anSenior DeveloperCommented:
It's MySQL at all.. can you post the execution plan?
0
Nathan RileyFounderAuthor Commented:
Here is a small dataset table dump if that helps.
insights.zip
0
Nathan RileyFounderAuthor Commented:
Yes, it's all mysql.  Not sure how to get an execution plan?
0
Nathan RileyFounderAuthor Commented:
Figured it out, here you go.
EP
0
ste5anSenior DeveloperCommented:
Can you also post the result of an EXPLAIN insights;? And the result of EXPLAIN EXTENDED yourQuery;.
0
Nathan RileyFounderAuthor Commented:
Sure, here you go.
Explain Insights
Explain Extended
0
ste5anSenior DeveloperCommented:
Okay, Try a query with only returning the ID's:

SELECT	T.*
FROM	(
	SELECT	id,
		@rank := IF(@current_insightsource = insightsource, @rank + 1, 1) AS rank,
		@current_insightsource := insightsource 
	FROM	test
	ORDER BY insightsource, title DESC
) R 
  INNER JOIN test T ON R.id = t.ID
WHERE R.rank <= 2;

Open in new window


and create an index with (insightsource ASC, title DESC, id ASC).

http://sqlfiddle.com/#!9/2518c/1/0
0
Nathan RileyFounderAuthor Commented:
Ok, that returns results in just a couple of seconds, but it doesn't seem to return unique insightsources I duplicates and it returns 139 results not a limit of 5

SELECT	T.*
FROM	(
	SELECT	id,
		@rank := IF(@current_insightsource = insightsource, @rank + 1, 1) AS rank,
		@current_insightsource := insightsource 
	FROM	insights
	ORDER BY insightsource, title DESC
) R 
  INNER JOIN insights T ON R.id = T.id
WHERE R.rank <= 2

Open in new window

0
ste5anSenior DeveloperCommented:
It returns 2 rows per insightsource. When you want unique ones, then filter for rank=1.
0
Nathan RileyFounderAuthor Commented:
I changed it to 1 and re-ran the query.

It returns 71 rows and of that many duplicate insightsources
0
ste5anSenior DeveloperCommented:
Place a sample in SQL Fiddle.
0
Nathan RileyFounderAuthor Commented:
I'm trying to use that tool, but not working for me.

http://sqlfiddle.com/#!9/3b551

I've got the schema building, but it errors when I put in the query.
0
Nathan RileyFounderAuthor Commented:
Ended up going a different route since this seemed to be too difficult, thanks for trying.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nathan RileyFounderAuthor Commented:
Ended up going a different route since this seemed to be too difficult, thanks for trying.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.