mysql table structure

[dg]

hi ....all

i have a mysql attendance table which has student_id, time_in, timeout, date ........

and storing for each day ..... now my problem is at the end of year my table has around 9lacs records .... which slows down the processes

 

is ther any better way to store or change the structure of the table........

 

 

thanx

dg

[cooldude832]

the # of records shouldn't have a significant impact if you are storing them properly.

[dg]

hi,

i m design a attendance system for a company..... which has around 3000 employees and each employee is working in shifts .... so my table structure has employee_id, date, time_in, time_out , shift_id ......

so a employee work for two shift......two records goes into the table .....

so for a year if the employee falls in two shifts then he has 365*2 records per  year ....

so for whole company 3000*365*2 records per year ....

now accessing/searching through the table becomes very time consuming.

 

any suggestions for storing the data or changing the table structure .

 

--thanx

dg

[Barand]

Use indexes on commonly searched columns. employee and date look likely candidates

[dg]

indexes are already there for the table , but still i wnted sme better table structure which would reduce the no of records in the table....since for the years to come the table will get populated........

 

thanks in advance........awaiting for a reply........

[mbeals]

that's basically what placing an index on employee ID is doing......

 

An index is a second table containing references to common things,  So the employeeID index looks something like:

 

EmployeeID  |  Location

------------------------------

      1                    1,3,4,5

      2                    9, 23

      4                        12

 

So when you search on an index, you really are just searching on a smaller table...but you don't have to go through the pains of manually reorganizing the data.

[Zwiter]

it may be stupid, but are you running the databse on a PII 233 computer aith 256MO ram?

Maybe you should look for your machine performance instead of your database performance.

Z.

[accident]

Databases are meant for storing millions of records.

Like the person above said, if you are running your database on a VERY old machine, you may want to upgrade it, but if you are running it on a newer machine it will have ZERO problems with this.

 

If you REALLY want to reduce the records you could just create an archive table, so you only keep the last years records in the main table, and anything older in another table.

 

Just make sure you have indexes set up on the correct columns and you wont notice a problem

Try inserting in two years worth of records and see how it performs.. it should be very fast. I mean you are only inserting ~1.5million records per year