Products from one category only

[Wesley McDermottParticipant]

Hi Ernest,

I have installed the new version, have asked my host to monitor. Will get back to you when I know more.

Many thanks,
Wes

[Wesley McDermottParticipant]

Hi Ernest,

I have updated and been running the new version of the plugin now for over a week and my host is still saying the problem persists. They sent me the following message:

We see that the same query continues to executes.
Because it is heavy for the Mysql server and it need unusual long time to execute and it can lead to 503 errors.

Here it is the query and you can see that after 9 seconds it is still executing. And along with it there is another one similar to this which stays for 5 seconds.

==============================================

1789658 haunted localhost haunted_haunted Query 00:00:09 SELECT wp_posts.post_title as title, wp_posts.ID as id, wp_posts.post_date as date, wp_posts.post_content as content, wp_posts.post_excerpt as excerpt, wp_users.user_nicename as author, wp_posts.post_type as post_type, (SELECT DISTINCT CONCAT(‘–‘, GROUP_CONCAT( wp_term_taxonomy.term_id SEPARATOR ‘—-‘ ), ‘–‘) FROM wp_term_taxonomy LEFT JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id WHERE (wp_term_relationships.object_id = wp_posts.ID) ) as ttid, ((case when (lower(wp_posts.post_title) REGEXP ‘rugby|hotel|hall’) then 10 else 0 end) + (case when (lower(wp_posts.post_content) REGEXP ‘hotel|brownsover|hall|warwick|ideal|available|upon|avon|stratford|rugby’) then 8 else 0 end)) as relevance FROM wp_posts LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID LEFT JOIN wp_users ON wp_users.ID = wp_posts.post_author WHERE (wp_posts.ID <> 379) AND (wp_posts.post_
type
REGEXP ‘[[:<:]]dummy_notting[[:>:]]|[[:<:]]post[[:>:]]|[[:<:]]product[[:>:]]’) AND wp_posts.ID NOT IN (-99) AND (wp_posts.post_status = ‘publish’) AND ((lower(wp_posts.post_title) REGEXP ‘rugby|hotel|hall’) OR (lower(wp_posts.post_content) REGEXP ‘rugby|hotel|hall’) OR (lower(wp_posts.post_content) REGEXP ‘hotel|brownsover|hall|warwick|ideal|available|upon|avon|stratford|rugby’) OR (lower(wp_posts.post_title) REGEXP ‘hotel|brownsover|hall|warwick|ideal|available|upon|avon|stratford|rugby’)) GROUP BY wp_posts.ID HAVING (ttid NOT REGEXP
‘–344–|–325–|–326–|–327–|–328–|–329–|–330–|–331–|–332–|–333–|–334–|–335–|–336–|–337–|–338–|–339–|–340–|–341–|–342–|–343–|–345–|–346–|–347–|–355–|–356–|–357–|–358–|–359–|–365–|–366–|–360–|–361–|–362–|–363–|–367–|–368–|–369–|–370–|–371–|–372–|–373–|–374–|–375–|–376–|–377–|–378–|–379–|–380–|–381–|–382–|–383–|–384–|–385–|–386–|–388–|–389–|–390–|–393–|–394–|–395–|–396–|–397–|–398–|–399–|–400–|–401–|–402–|–404–|–406–|–408–|–409–|–411–|–412–|–460–|–413–|–414–|–416–|–418–|–419–|–420–|–421–|–422–|–424–|–425–|–448–|–449–|–426–|–427–|–428–|–429–|–430–|–431–|–432–|–433–|–436–|–437–|–439–|–440–|–450–|–441–|–451–|–442–|–443–|–444–|–445–|–446–|–452–|–453–|–454–|–456–|–457–|–458–|–459–|–465–|–461–|–462–|–463–|–464–|–467–|–469–|–470–|–471–|–472–|–473
–|–47
5–|–476–|–477–|–478–|–479–|–480–|–364–|–482–|–483–|–484–|–485–|–486–|–487–|–489–|–490–|–491–|–492–|–494–|–495–|–496–|–497–|–498–|–499–|–500–|–501–|–502–|–503–|–504–|–505–|–507–|–508–|–509–|–513–|–510–|–514–|–511–|–512–‘) AND (ttid REGEXP ‘–298–‘) ORDER BY relevance DESC, wp_posts.post_date DESC LIMIT 3

[Ernest MarcinkoKeymaster]

Thank you!

At least I see no errors, but the HAVING clause is what I think is the cause of the problem here (and maybe the number of keywords as well).
The term exclusion unfortunately has to do a left join, which breaks the indexing (wordpress is built this way).

I’m working on 2 plugin updates right now, one is Ajax Search pro and one is Related Posts pro. I’m completely re-working this query structure to a much more optimized one based off of ajax search pro. I’m probably finishing ajax search pro today, it’s under final testing, then I’m continuing with related posts pro.

Right now, there is not much I can do, as not an error is causing the problem but the query structure. I’m going to inform you as soon as I get an optimization ready on this plugin and upload a pre-release version for further testing.

After the planned optimizations the query execution time should be reduced greatly. Once the queries are fully optimized we can see how it’s working and if any further configuration tweaks are needed.

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

[Wesley McDermottParticipant]

Hi Ernest,

Thanks I will remove the plugin for now and then proceed when you have reworked it. I appreciate your ongoing help and work on this!

Wes

[Ernest MarcinkoKeymaster]

Hi!

Just checking in, I didn’t forget about you, no worries 🙂 I’m still working on the update, so far it looks very promising. I completely reworked most of the queries, it’s almost done.

I will upload a test version for you once it’s safe for testing. (around 48 hours)

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

[Ernest MarcinkoKeymaster]

Hi!

Good news. I finished a possible release candidate for version 1.4, I’m attaching it to this reply for testing. I have fully optimized the database queries based on the table indexes and best practices from the MySQL manual. From this point on I’m almost sure the performance only depends on the configuration and the server speed.

After installing, please change the Maximum Keywords count option to 4 on the Relevance Options -> Keywords options panel: https://i.imgur.com/YDEJ0tb.png
I have found that 4 is more than enough and optimal for the performance.

Let me know how this one works!

Attachments:

You must be logged in to view attached files.

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

[Wesley McDermottParticipant]

Hey Ernest,

Thanks Ive added the plugin again and will let you know if i have any issues.

[Author]

Posts