Huge Data Handling

qadeer_ahmad

I have a table containing more than 4,000,00 records with 6 fields i can pagination it easily but it takes long time some time dead the page when i try to get data by sorting.
Any idea to handle huge records so that it show records with short time as i need to show only hundred records per page?
most important to get records with sorting.

daredevil14

how are you sorting your data ? by what field ?

I suggest that you sort them by ID if you're not sorting them like that... since manipulating data by IDs and Ints is faster than strings...

Xager

Use ORDER and also LIMIT 100 then

jeremyphphaven

yup, to echo Xager... here's an example I wrote real quick for you to aid your pagination

// num_results = results per page
$num_results = 100;

// $page below you'll have to feed into this equation based on what page you're on
$min = ($page-1)*$num_results;

$query = "select * from table order by date_posted desc LIMIT $min,$num_results";

hope that helps

qadeer_ahmad

Yes off course i am using ORDER and limit i also need to sort records by string field. If i am not wrong mysql take a lot time or some time its hang the system if it handle more than 1,000,00 records here i have 4 times so sorting should hang the system or take a lot time which is not acceptable.
i have idea i create 4 same tables and through data in these tables by cutting whole data in 4 parts.
Now i need to run query for 4 table using union but again we got all records and again need to sort so this is bad idea.

Any other idea?

leatherback

A table with 4000 records is NOT huge. MySQL should be able to handle queries on this in a split second.

Please post the query you are running. Most likely there is something odd in there.

edit: As an example: I am running a scientific database with currently some 10.000.000 records in it, and retrieving a subset is a matter of a few miliseconds, using an indexed integer field as limiting field

qadeer_ahmad

Can you please guide me how to index data all data as my table have 4,00,000 not 4,000 only my table have only 8 fields
1. name
2. date
3. x_property
4. y_property
5. z_property
6. .......
7. .......
8. .......

so i need to sort and get records with simple query
select * from table limit 0,100 order by name
here my system take a lot time
can you please give me idea how i index for all fields so every time my query work fast.

daredevil14

Again, I'd advice you to retrieve your data using an indexed INTEGER, this would be faster... and below is how to add an index to a field ( pure SQL statement - not PHP ) :

ALTER TABLE `table_name` ADD INDEX ( `field_name` )

if you are using PHPMyAdmin, this is even easier...