Mysql – SQL – Select Rows in a Certain Order

MySQL

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.

Best Answer

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

Related Solutions

Mysql – Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

To make first,middle,last name searchable, should I make them Primary Keys?

No, any field in any table is "searchable".

A primary key is a unique identifier of a row in a table. Your idPeople field satisfies this requirement, though since you stated the table will have millions of rows, you'll want to make that an INT or BIGINT. The maximum value of SMALLINT is 65535. See http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

How would I allow to search through ItemCode.codeSection and return all "People" that match?

One way would be:

SELECT * 
FROM PEOPLE
WHERE idPeople in 
    (
      SELECT idPeople FROM Item WHERE idItem in 
      (
          SELECT idItemCode from ItemCode WHERE codeSection = 'Some Section'
      )
    )

Another way would be to use JOIN.

I hope I understand the foreign key concept. It's on the "Item" table so it allows multiple items per "People". Is this the correct way?

Looks correct to me. The notation is called crows foot notation, and on the Item side (1 line and a "crows foot", it's saying 1 to many items. On the People side (parallel lines), it's saying 1 and only 1 person. Using this notation, some of your other relationships appear incorrect. (People to County, in particular)

There will be separate lists for different counties, should I create a View for this?

You could, but they could also just be separate SELECT statements or Stored Procedures.

Best Answer

Related Solutions

Mysql – Cannot update certain rows in innodb tables

MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

Related Question