Start Free Trial

asked on

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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'

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hmm..just times out with a internal server error after running for 30+ seconds.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

Well, here's a working SqlFiddle.

ASKER

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.

Have you created indices on the used columns?

ASKER

Yes I have.

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?

ASKER

I'm on a dedicated box, quad core, 16gb ram etc...

InnoDB

It's MySQL at all.. can you post the execution plan?

ASKER

Here is a small dataset table dump if that helps.
insights.zip

ASKER

Yes, it's all mysql. Not sure how to get an execution plan?

ASKER

Figured it out, here you go.

User generated image

Can you also post the result of an EXPLAIN insights;? And the result of EXPLAIN EXTENDED yourQuery;.

ASKER

Sure, here you go.

User generated image

User generated image

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

ASKER

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

It returns 2 rows per insightsource. When you want unique ones, then filter for rank=1.

ASKER

I changed it to 1 and re-ran the query.

It returns 71 rows and of that many duplicate insightsources

Place a sample in SQL Fiddle.

ASKER

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ended up going a different route since this seemed to be too difficult, thanks for trying.