Improve search engine speed

[seomaxParticipant]

Hello,

We are experiencing a slow search difficulty
products in the search engine of the site – https://feniks.bg/.

The reasons for the difficulty that the encounter is the slow execution of requests to the database from the plugin ‘Ajax Search Pro’ used.
I provide you with a short sample of
running requests:

# Time: 221130 10:47:10
# User@Host: feniksbg_wp344f[feniksbg_wp344f] @ localhost [] # Thread_id: 945996 Schema: feniksbg_wp344f QC_hit: No # Query_time: 1.299446 Lock_time: 0.000933 Rows_sent: 55637
Rows_examined: 169669
# Rows_affected: 0 Bytes_sent: 2092553
SET timestamp=1669798030;
SELECT term_id, meta_key, meta_value FROM f3ni_termmeta WHERE term_id IN
(4145,4611,5697,3728,4706,5691,4576,4412,4375,4830,4610,4892,4430,4697,3674,4322,4257,4420,3730,4696,4664,5384,3733,5626,4846,5546,4665,2870,5230,3737,3742,4376,4681,4282,3743,3744,4506,4885,3636,3635,3746,4613,4154,4315,4164,3747,3748,4418,4354,4587,4609,5693,4739,4283,4275,4434,4276,4277,3696,4268,4733,4823,4503,3752,3753,4278,4189,4862,3002,4562,4281,2943,2944,3754,3755,4227,4358,5445,4762,3756,5234,3757,3758,4702,3765,3766,3767,4660,4842,4362,4363,4803,4131,4174,4353,3769,4873,4493,3770,3771,3772,4557,5237,4614,4335,4879,4666,4898,4366,3773,4259,4298,5241,4721,4521,4403,4286,4287,4288,4615,4374,5448,5711,4393,3776,3777,5712,4266,3778,4395,5618,3779,3780,5619,5713,4176,4175,4177,4732,4678,4263,4280,4279,4258,3781,3782,3783,4607,4274,4667,4419,5566,3784,3785,3792,4494,3793,3000,4356,4814,4352,5244,4410,4541,3047,5249,4495,5568,3001,3794,3795,3796,4653,4652,4633,5250,3797,4606,4122,3798,4758,3799,4612,4680,4342,5253,5260,4439,4831,4310,4692,5620,4783,3005,4305,4415,4253,4256,4255,30
03,3007,4786,4788,4795,4316,4252,3801,3802,3803,3804,5264,4875,5268,4334,3805,4586,5670,4265,3042,3043,3041,3040,3764,4605,4264,4143,3052,4723,4423,4146,4714,3004,3053,3054,4524,4474,5451,4863,4552,4553,3055,3056,5274,4492,4424,4425,4181,4407,4408,4914,3057,4904,4406,5715,4459,4460,4461,4462,4463,4464,4465,4466,4467,4468,4469,4668,5621,4445,4182,4250,4444,4171,4172,3763,3806,4382,4905,5707,3807,3808,4441,4452,4782,4123,4311,5717,3809,3810,4511,5718,4262,4261,4604,5277,3811,3812,3814,3815,3816,3817,3818,4874,4373,4166,5281,3673,4730,4603,3050,2869,4269,5284,4478,4864,4578,4579,4582,5288,4581,4705,5294,5709,5720,4646,4417,5635,4313,5298,3820,4856,3821,3822,3823,3824,3825,3726,3727,4433,4351,4341,4160,3826,3827,4602,4449,3828,3829,3900,4249,3830,4332,4333,4794,3831,3832,4887,4512,5706,4797,4496,4768,4346,4616,4690,4141,4689,4688,4687,3669,4473,5453,4906,5456,5457,4859,3833,3834,3835,4773,3711,5301,4273,5699,3836,4294,3837,4367,4414,3838,3839,4551,4550,4601,3724,3725,4488,5685,4537,4636,
4635,3840,3142,3841,4190,4695,5721,3842,3843,5722,4517,4364,4185,4186,4184,4183,4365,4751,3844,3845,4809,4810,5725,4861,3119,3121,3846,3847,3848,4427,4344,3849,3852,4752,4248,4643,3853,4339,4682,3854,3855,3850,3856,4247,3851,3857,3858,4761,4505,5636,5637,3859,5594,4498,4724,4167,4709,4391,5459,4138,4583,3860,3862,4686,4704,4749,4881,4854,3861,3863,4669,5648,5726,5655,3864,4715,3865,4337,4453,3866,3867,3868,4600,4267,4173,4642,2997,4513,5727,4847,4806,4719,4320,4401,5628,4711,3869,4558,4638,4639,3870,3871,5728,4799,5729,4394,4654,3872,5622,5305,4599,4296,4336,4345,3873,4693,3874,4617,4679,4421,4246,4907,3813,3875,3876,3877,3879,3880,4536,3881,4774,4670,5730,5593,4132,4359,3882,4531,4618,4312,3117,3118,4409,4510,4548,4598,4779,4908,4245,3883,4432,4535,5694,4383,3884,4619,5686,3178,3177,4778,5695,4718,4426,5629,4242,4675,3686,3885,3176,3886,4860,4378,4514,3887,3888,3889,4621,5312,3890,4597,4629,4475,4241,5313,4507,4137,4585,4357,3891,4487,5315,4440,4631,4770,5462,5464,4662,4442,3892,478
0,4436,3683,4300,4866,4180,4839,4838,4841,4840,3895,4910,4911,5696,3896,3897,4805,4886,4525,4889,4136,4135,5570,5708,5466,4472,4559,3143,4147,4485,3898,3899,4584,5380,3901,3902,4716,5468,4684,3745,3903,3904,4575,4144,4698,4837,3722,3723,5318,4560,4867,4456,3905,5320,3906,4754,4700,4239,3907,3908,3631,3632,3909,4787,3910,3911,3912,5470,5472,4431,4728,3913,5579,4622,3914,3915,3916,5322,4899,4188,3671,4915,4808,3924,3925,4392,4314,4546,4547,4708,4168,4178,3676,4179,3675,3677,3678,5474,5475,4544,4542,4523,3917,3918,4435,4769,4139,4880,4329,5690,4740,4238,4237,4236,4645,4760,3926,4368,4529,3927,5324,5326,5580,2999,4355,3928,3919,3672,3929,3930,3931,3932,3933,3934,3935,4658,4235,3936,3937,4570,4596,4734,5638,5639,3920,4569,3921,4448,4869,4868,4781,5328,3938,4595,4443,3923,4360,3939,4520,4815,2815,3682,3681,4777,4568,4381,3940,5330,3948,5332,4292,3949,3950,3951,5612,4489,2843,2841,2842,4720,5479,5478,5204,5334,5336,5338,5340,5206,5201,5342,3941,4483,4484,4594,4515,4540,5344,4593,5482,5346,3
953,3954,4661,4729,4226,3721,2840,2839,4563,4234,3942,3955,4632,4293,4450,3956,3957,3958,3959,3960,4901,5646,4501,2838,4486,4518,4741,4742,3961,3962,4756,4743,4744,3946,4784,4640,3963,3964,4130,4309,4641,4458,4722,4349,4772,4656,4142,4832,2834,2829,2830,5674,2835,4672,2836,4592,4437,3680,3679,4771,4623,4624,2831,2832,3947,5534,4824,4370,4707,3984,5647,3985,4755,4233,2828,4429,5640,4822,5348,3986,3987,3988,4848,4764,3989,3990,3991,4902,3992,3981,4003,3641,3642,4232,5350,5525,4845,5352,4231,4230,4225,4894,4229,4413,4522,4457,4004,4005,5535,4007,4306,5682,4573,4574,4228,4703,4422,4482,5485,5484,4196,4197,4195,4192,4191,4194,4325,4008,3995,4324,2811,4673,5354,4307,3996,4010,4567,3997,4011,4534,4883,4852,4012,4348,4499,4013,4634,4369,4014,5356,4591,5358,5360,5698,5488,5490,4647,4303,4304,4302,4301,4015,4564,4402,4590,4398,4648,4016,4017,4561,4571,4572,4018,4019,5492,4020,4223,4224,4659,4170,4169,4657,4021,4022,4405,4577,4023,4361,4024,5362,4884,3762,3761,4811,3740,3741,3738,3739,4025,4765
,5494,4222,5364,4853,4297,5366,5641,5642,5643,5644,4895,4221,3998,4565,4580,4909,4477,4508,5369,4026,4027,4028,4029,4476,2814,4350,4870,5370,3670,4377,3999,4000,4001,4766,4030,4031,4032,4033,4034,5372,4035,5520,5497,4851,4404,4036,4037,4481,4219,4220,4637,4479,4038,4317,4318,4039,4040,4757,4002,4710,4725,4491,4041,4817,4051,4533,4042,4218,4416,4148,4149,4043,5374,4526,5613,5499,5521,5581,4380,4850,4625,3648,3687,3688,3689,3690,3691,3692,3693,3694,3695,3697,3699,3700,5681,4644,4216,4217,4833,3702,3703,3704,3705,3706,3708,5537,3709,3710,3712,3713,3714,3715,4371,3685,4834,4836,4835,2833,3719,3720,4215,4214,4044,5502,4685,4683,4500,4912,4455,4212,5504,4338,5505,4903,4211,4694,4849,4045,4589,4209,4208,4290,4046,4053,4819,4054,4049,4050,4055,4056,4057,4058,4059,4555,4554,5508,5510,4701,4655,4347,4727,4060,4061,4062,4048,5376,3637,3684,4295,4527,5624,4271,4161,4272,4207,3731,3732,4676,4063,4064,4078,4532,4079,4890,5539,4080,2813,4206,4204,4205,4775,4712,5512,3633,3634,4081,4343,4626,4738,48
55,4753,4082,4083,5541,4737,4428,4323,4763,4084,5514,3058,4065,4066,4085,3059,4086,4067,4087,4650,4731,4203,4202,4201,4330,4627,4140,4088,4089,4649,4490,4539,5378,4090,4068,4630,4736,4735,4843,4844,5208,4588,4509,5595,4091,4400,4876,4454,4470,4291,4331,5516,4165,3179,4519,4792,4372,4379,4093,4094,5212,4095,4411,4096,4097,4098,4399,4099,5723,4726,4767,5617,4556,5216,4699,4129,4504,4159,4447,4446,4156,4155,4158,4157,4857,4663,4397,4101,4102,4103,5596,4104,4105,4200,4106,4826,4793,4107,4108,5220,4071,4072,4480,4882,4109,4110,4111,4384,4471,4340,4199,4112,4566,4308,5227,4198,4813,4113,4114,4073,5625,4299,4117,5645,4801,4628,4713,2816,4800,4118,4119,4074,4120,4075,4076,3643,4674,4759,3717,4913,4545,3006,4549,4121,4538,4790,4289,5523,4748,4747,4746,4745,4878,2827,4396,3759,3760,2837,5657,5574,5576,5572,5578,5577,5575,5573,5601,5604,3051,5553,5556,5561,3049,3048,5552,5559,5614,4543,2648,2649,5606,5608)
ORDER BY meta_id ASC;

When changing the searched product (typing its name into a search engine), several similar slow requests are performed for the site, which slows down the search.

We see that the ‘Ajax Search Pro’ plugin has an option to cache the results, which would be useful for faster loading.
The option is enabled, but it did not result in faster loading – Ajax Search Pro -> Cache Settings -> Caching activated.

Please review and specify an opportunity to optimize slow queries, which would improve the discovery rate for the searched products.

Regards!

[Ernest MarcinkoKeymaster]

Hi,

Thank you for the feedback, but I’m afraid this query is not coming from our plugin. I have gone through all of the search process, and there is no query ever executed on the term meta table. This one even sets a timestamp, which is very unusual. The only instance is when the taxonomy search is enabled, but that uses a LEFT JOIN statement, not this query, it is completely different.

This might be something triggered by Ajax Search Pro after the actual search. I suspect maybe when the search is finished and the plugin requests the title, content or some other fields from WordPress, then there might be a hook from a different plugin to do something else, which then does this query. This is only a guess, the actual slow query could be coming from somewhere else too.

I will try to explain the query above, that may help you find the root cause: The query tries to get term metadata for specific taxonomy terms, the numbers there are listed the taxonomy term IDs. These taxonomy terms can be categories, post tags, product categories, product tags etc.. It looks like as if it tries to fetch metadata for a specific taxonomy term list for some reason – maybe to list categories or something similar.

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

[Ernest MarcinkoKeymaster]

After some googling I have found a similar issue reported for a query executed by YOAST seo sitemap generator, it oddly looks identical: https://github.com/Yoast/wordpress-seo/issues/6761
In case you are using that plugin, then it is very likely the query is originating from that.

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

[seomaxParticipant]

Hello,

We do not use the Yoast plugin. We use rank mat for seo optimization.
Do you want us to send access to the administration to check and tell us how we can speed up the search engine because it is very important for the business and we want to use it.

[Ernest MarcinkoKeymaster]

Sure, I am more than happy to check. You can also add temporary FTP if you want to, I will debug the code too.

Still, the slow query above is not originating from our plugin, so I may not be able to tell what that is and where it’s coming from exactly.

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

[seomaxParticipant]

You cannot access this content.

[Ernest MarcinkoKeymaster]

Thank you!

I’m afraid this is not related to the plugin. I completely turned off the queries to see if it makes any difference, but all of the requests are super slow.
Even when I turned off the plugin completely, and run a fetch request to the WordPress ajax handler, it takes over 4 (!!!) seconds to respond to a completely empty request, which does nothing.

See the empty request, and the response times measured. This should be normally sub/around 1 seconds, that is perfectly fine. On the second screenshot you see, that some requests were almost normal, but most of them were simply extremely slow to respond, that indicates a serious problem on the server side. Also note, the search plugin was disabled programmatically during these tests.

It is very much possible, that this is caused by a piece of code or a plugin. I usually recommend deactivating the plugins one by one to see if at any point the response time gets better – usually that one is at fault. Same with the theme.
It can be much more complicated, it is also possible that the server simply can not keep up with the demand, or there is a faulty configuration somewhere.

Firewalls/virus scanners on the server can be also problematic, they will catch all requests, and scan them via heuristics, that can eat up a lot of CPU & Ram.

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

[Author]

Posts