Page 1 of 1

optimizing table

Posted: Thu Sep 28, 2006 2:53 am
by rami
suppose there is a table for comments in my site which has now around 100000 records
the oldest about 25000 comments are on no use now
i cannot delete it as it may work as archive...

but that putting that 100000 records in table...

does it effect in performance of table
like while searching ,updating ,inserting

so what will be best idea to do with that older comments...

can i move it to another table and say it archive...

what can i do to get best result
to spilt that table...?

Posted: Thu Sep 28, 2006 3:07 am
by ryuuka
hey

can you display the code for the table and your query?
i've had something similar maybe what helped mecan help you but
that depends on the source

type of DB and php version would also be nice

Re: optimizing table

Posted: Thu Sep 28, 2006 3:27 am
by onion2k
rami wrote:does it effect in performance of table
like while searching ,updating ,inserting
The size of the table has no affect at all on inserts. It'll only affect updates if you're not updating on an indexed key (eg your primary key). It will affect searchs though. You should add an index that includes columns you search on. Once that's done performance should be fine. 100,000 records isn't actually very many.

Posted: Thu Sep 28, 2006 4:50 am
by rami
i think it does effect while making search and other operation .The table also becomes hard to manage
i am doing
sql=select required feilds from table where ....(but not any before or after this date where clause)


so it must effect performance
so how can i manage things now

can i transfer data to another table and give priority to recent table while doing operations...

Posted: Thu Sep 28, 2006 5:02 am
by onion2k
Did you even read my post?

:evil:

Re: optimizing table

Posted: Fri Sep 29, 2006 10:36 am
by rami
onion2k wrote: The size of the table has no affect at all on inserts. It'll only affect updates if you're not updating on an indexed key (eg your primary key). It will affect searchs though. You should add an index that includes columns you search on. Once that's done performance should be fine. 100,000 records isn't actually very many.
i read it and didnt found concerete...
i was much thinking about spitting table and other way...

when i say index things...
mysql clearly says...
making index helps for select statement but it make insert,update and deletion difficult...

so and my point is that first 25000 is of not of active use now so drop it to another table so i was seeking for any such efficient mechnism or way of doing it..

well 100000 is just example its news site and may be with in some year it will be more than 3000000..
any way thanks

Posted: Fri Sep 29, 2006 11:18 am
by onion2k
How often are items inserted and updated? How often will the table be searched?