how to integrate my requirements with MySQL capabilities

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

how to integrate my requirements with MySQL capabilities

Post by davidklonski »

Hello

My database contains information about over 15,000 individuals.
For each individual I keep the following information:
ID, Name, Gender, Birth date and so on.

Despite the large number of individuals, I only display a small set of them on the screen in any given moment.

The information on the individuals is displayed in a table with a row for each individual and the type of data in the column headers. The user who watches the data has the option of sorting the data according to any one of the types (ID, Name...)

Here is my problem:
It takes too long for the data to be retrieved from the database when I try to extract the infromation about ALL of the individuals:
"select ID, Name, Gender..., from individual_tbl;"

I could speed things up if I knew the IDs of the individuals that I need to display (then I would only extract them from the database).

The problem is that if the user chose to sort the data by Name (for example), then I need know who are the first 10 individuals when sorted by Name. Is there a way to ask MySQL to find it out without obtaining all the data sorted by Name and then to display just the first 10 individuals?

To put it more generally, I need to find the IDs of the ith - jth individuals when they are sorted by a certain criteria, and I need to be able to do it efficiently (remember that I have a huge number of entries in the database)

I would appreciate any help
thanks in advance
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

look up ORDERBY and LIMIT at mysql's page
Post Reply