1

I'm new here - if I missed anything in the question please let me know.

I'm trying to build a people directory like LinkedIn has:

http://www.linkedin.com/directory/people-a

I don't want to fetch all the rows with name field starting with A and build the link list like LinkedIn. Is there any way in MySQL so I can only fetch rows in this sequence:

1st, 100th,101st,200th,201st,300th,301st,400th,401st, Last

That means I am trying to get two consecutive rows after a certain gap including the first and last item. The ids are not in nice uniformly increasing order.

Say my initial query SELECT * FROM businesses where name like 'a%' order by name returns ids like this:

1,3,5,6,8, 9,12,33,45,66,77,88,100,103,120,133,155,166,177,178,198

Above is if I want to get all the rows. But instead I'm trying to get only the items after a certain distance. For example if I want to pick after every 5 items:

1,(skip 4),9,12,(skip 4), 88,100,(skip 4),166,177,(skip 1),198

So skip 4 items and take next two. Is this possible without fetching all the rows?

I'm not sure if the above is the best way to achieve what I'm looking for. If you have better solution please let me know.


SELECT * FROM businesses where name like 'a%' order by name this query returns about 50K rows. But I don't need all of them. I'm trying to build people directory that is nested several levels. So, on the first page I'll show links like this:

Page1: http://example.com/people/a
Links: 
   Adam - Alan => http://example.com/people/a/1   //here I skipped say 1000 people after Adam and before Alan
   Albert - Amy => http://example.com/people/a/2   // Here I skipped another 1000 people
   ...

Page2: http://example.com/people/a/1
    Here I show all those 1000 people profile links that I skipped in page1.

This is only two level. If you visit the above linkedin link, you'll see 3 or 4 levels of nesting.

  • Why do you want to skip 4 items? Explain a little bit more so we can help you better – oNare Jul 6 '15 at 15:57
  • I updated the question with my scenario. – hassansin Jul 6 '15 at 16:09
4

This is pretty clumsy but will perhaps give you some thoughts. The idea is to number the rows and pick from there using modulo:

select x from (
    select @n := @n + 1 as n, x 
    from t, ( select @n := 0 ) as dummy
) as u 
where mod(n,6) between 0 and 1 or @n = n

Edit: As observed by @hassansin @n will have the value of the last n. Therefore the union of the last n can be replaced with the predicate @n = n

| improve this answer | |
  • Thanks! It works just like I wanted. Although it is loading all the 50K records in a derived table, but I'll take it and dig a bit more. – hassansin Jul 6 '15 at 19:46
  • 1
    @hassansin - Unless you have all the rows consecutively numbered, any solution will require touching all 50K rows. What did you mean by "loading"? – Rick James Jul 6 '15 at 21:21
  • sorry for using wrong terms - not familiar with DB terminologies. When I try explain on above query - it shows two derived tables - one of them has 0.5M rows. I've about 50K rows that start with A but the whole table has about .5M rows. Why the explain shows .5M instead of just 50K? – hassansin Jul 6 '15 at 21:31
  • 1
    @lennart, I used this where mod(n,6) between 0 and 1 or @n = n to remove the union clause. – hassansin Jul 7 '15 at 8:32
  • Ah, I did not think about that, good observation. Personally I find variables (like @n in this case) a bit confusing since they restrict the ways a query can be evaluated. – Lennart Jul 7 '15 at 9:33
0

I have a way that is functional, but not the best for performance:

SELECT * FROM business WHERE name LIKE 'a%' order by name LIMIT 0, 2
UNION
SELECT * FROM business WHERE name LIKE 'a%' order by name LIMIT 6, 2
UNION
SELECT * FROM business WHERE name LIKE 'a%' order by name LIMIT 12, 2
...

You can build this query in your code, and change the limits based on your needs.

Note however that when the numbers in limit get big, there might be a performance hit there.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.