as

[Markus_VParticipant]

Hi,

we got a problem with the wp_asp_index table on our wordpress-site. We got lots of content and meanwhile the ASP index table is about 648 MiB – therefore the wohle wordpress-db now has over 1 GiB.

Now we got several problems:
– backing up this huge database
– using an up-to-date version of mySQL on our server (max-db-size = 1GB)
– performance issues

Is there any chance to get it smaller? Or would it be a solution using a own database just for Ajax Search Pro? Or do you have any other solution for us?

Thanks in advance
Markus

[Ernest MarcinkoKeymaster]

Hi!

The index table search performance is based on the separate table rows for each possible keyword, with it’s occurence values. While it is much much faster to look up as opposed to the regular content tables, it’s size is bigger because of that.
I don’t think there is a way as of now to use a separate database via WordPress for the plugin tables, so I think the best way to reduce the table size is by minimizing the number of rows – thus the number of keywords.

To do that, I recommend checking the following configurations on the index table options
– Make sure that only the required post types are selected – I assume you already have that, but it can make a difference
– Choose only the fields, that are neccessary for the search – custom fields, taxonomy terms can increase the table size drastically. Deselcting some of those could reduce the table size.
– Increasing the min. word length – in case of huge datasets, short words might not be as relevant. Depending on your scenario, ignoring words below this length could reduce the size further.
– Using the stop-words – this is also recommended, especially with such big datasets. You can even add more words related to your content, that are frequent, but might not be as relevant.

After making all the changes, you will have to re-create the index table of course.

I believe these are the best tips to reduce the overall table size drastically.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[Markus_VParticipant]

Hi Ernest,

thanks for your reply, I will check these steps.

Best,
Markus

[Markus_VParticipant]

Hello Ernest,

I tried the following:
– setting the min. word length from 1 to 3
– Using stop-words

– just required post types and custom fields and so on were selected before.

After that i started an new generation for the index.

before:
2.839.957 keywords
648 MiB size of index table

after:
2.204.814 keywords
656 MiB size of index table

So the table has -22% keywords but the size of the db table is 1,2% bigger?

Did I something wrong?

Thanks,
Markus

[Ernest MarcinkoKeymaster]

Hi,

You did okay, as the row count got significantly lower there. That size could be fragmented or maybe there is a database cache there in effect. If you use PHPMyAdmin or any similar tool, there should be a table optimize and flush feature built in, that should defragment and clear the database cache: https://i.imgur.com/nnq3HtP.png

Try running those three, but please be careful. Make sure to have a full database back-up before running those commands. They might take a few minutes to execute. If there was any fragmentation, the table size should definitely decrease.

I’m not sure how much reduction you can expect there, that table size with over 2 million rows of information sounds about right to me.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[Author]

Posts