Problems with large data table...

Hi,

I used Phoruym version 5.1.16a since 2006 now and it works very well. Last week I received a complaint from my provider because my database is getting very big ( >600 Megs). He said, there were 2 large table :

phorum_messages >140 Megs
phorum_search > 430 Megs

I don't want to touch the first one but what is the used of the second one and what to do reduce it?

I don't really understand the need to keep track of all users' search. Can you enlight me about this?

Regards,

Yex

its not keeping track of "all users' search" but its a kind of copy of the message data with a fulltext index to avoid locking on the messages table while search is running.
I don't kow what kind of provider is "complaining" about 600MiB databases.

---

Thomas Seifert

Hi Thomas,

The name of the provider is Pair.com. They are always complaining about this ot that...

Is it absolutly necessary?

Can it be prune or compress in any way?

Regards,

Yves

what do you want to prune? its your message-date! you won't be able to search through a part of the messages if you remove them from the search table. and no, no compression available. maybe you can fall back to non-fulltext search and remove just the fulltext index but thats plain experimental and I've got no idea how much it will hurt with your lots of data. you will be trading space for performance.

---

Thomas Seifert

Hi Thomas,

OK. I have another question. If the search table is actually a copy of the message table, why is it that it's about 3 times bigger than the original? The message table is around 142 Meg and the search table is around 430 meg.

Regards,

Yves

This is because the search table is not an exact copy, but instead it is a full text search index, which takes much more space than the original data.

---

Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce

Has anyone dabbled with hooking Swish-E into phorum?

Yves ... if this continues to be a problem, swish-e could give you a site search outside of phorum.

Or, it has API hooks that would allow it to be written into a phorum search plug-in (I'd expect)

I expect pair.com is complaining about that DB size because you're on a shared MySQL server, right?

Is there a log to track how many phorum searches are done? The table size isn't really a problem unless people are hammering the search all day.

Sp

------------------------
Sean Phelan
Sites By Sequoia - Web Marketing ... The Experts at Building Your Success Online

well, seems like another tool which has to be installed which is usually impossible in a shared hosting environment.
otherwise we could offer a sphinxsearch module which works well with phorum already.

---

Thomas Seifert

Quote
sphelan
Has anyone dabbled with hooking Swish-E into phorum?

Yves ... if this continues to be a problem, swish-e could give you a site search outside of phorum.

Or, it has API hooks that would allow it to be written into a phorum search plug-in (I'd expect)

I expect pair.com is complaining about that DB size because you're on a shared MySQL server, right?

Is there a log to track how many phorum searches are done? The table size isn't really a problem unless people are hammering the search all day.

Sp

Hi Sphelan,

Yes I'm on a shared server. Their dedicated server solution is too expensive to run a free acess forum like mine...
I'm not very familiar with the solution you're proposing. I originally paid someone to install Phurum on my server because I know nothing of php. Is this something complicated to do?

My other solution is to move everything elswhere where I'm not being bothered any longer. Pair tend to apply "Bush-Cobow" like method of discipline. They keep disabling section of my Web Site without warning because they complain it exceed their low limits. And for God knows why, it always occur when I'm gone working outside on a contract in remote places without Internet access... :-(

Regards,

Yves

sphinxsearch does look interesting ... I'll have to remember that one.

Swish-E might be a better fit, though, for most users - it doesn't require a daemon, so shared hosting environments should be OK with it.

I know, for a fact, that Pair is OK with it - I know people who run swish-e on shared accounts.

The downside, compared to sphinx, is obvious - swish-e is a document-search engine, where sphinx is mySQL-optimized.

Having said that, though, swish-e can be fed search data in a format like "document1 name, data, doc2 name, data, etc...), so you can populate it's index with the results of a SQL query (or phorum php output), and tell it the corresponding URL to show if the result gets matched later.

Hypothetical, of course, but it comes back to the same conundrum of sphinx, or a big search table - when your forum gets that big, hopefully it's making enough money that you can get a dedicated server and run whatever you want :)

------------------------
Sean Phelan
Sites By Sequoia - Web Marketing ... The Experts at Building Your Success Online

Another option which may even generate a little money (hah!) would be to add Google's Adsense for Search to your website and allow Google to handle the searching. This could be added in the same location as your current Phorum search bar with a template edit.

Obviously, this will not be as nice as the integrated Phorum search, but it should allow you to offer full-text search without the database issues.

---

Joe Curia (aka Azumandias)
Modules: l0Admin Mass Email00000000l000000Automatic Time Zones000ll.l00000Enhanced Custom Profiles0.00Google Calendar0000l.l000000Post Previews
000000000Admin Security Suite000000000000Check Modules for Upgrades0000External Authentication000000Group Auto-Email00000.00000Private Message Alerts
000000000Attachment Download Counter0000Custom Attachment Icons000ll.ll00Favorite Forums000000.00000Highlighted Search Terms0000Self-Delete Posts Option
000000000Attachment Watermarks0l00000000Custom Language Database00l.l.0Forum Lockdown00000.00000Ignore Forums0000000000000Threaded Tree View
000000000Automatic Message Pruning00.llll.00Easy Color Scheme Manager0l.l00Forum Subscriptions0000lll000Moderated User Group
Templates:lGeneric Integration000000000 0000Simple Rounded000000 00000000Tabbed Emerald