optimizing table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

optimizing table

Post 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...?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: optimizing table

Post 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.
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Post 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...
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Did you even read my post?

:evil:
rami
Forum Contributor
Posts: 217
Joined: Thu Sep 15, 2005 8:55 am

Re: optimizing table

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

How often are items inserted and updated? How often will the table be searched?
Post Reply