1

I'm having trouble with a MySQL query sometimes taking a few seconds and sometimes taking 30. I'm trying to figure out if it's the query or if the problem is being on a shared server.

Some background: table has about 2 million rows and every field with user data is encrypted (customer requirement). Since fields are encrypted, I can't do any indexes. Here's a query that usually takes 3-6 seconds and sometimes takes 30.

 $sth5 = $dbh->prepare("SELECT activityType, startDate, startTime, duration, quality, type, endTime, activityNum, activityClass FROM ActivityX WHERE user=? AND activityType=? ORDER BY activityNum DESC");

In some cases, I tried restricting the rows to be searched in the database and it helped only if I could restrict the rows to be 20,000-40,000 or so. (I often have to search the full database for the answer.)

 $sth5 = $dbh->prepare("SELECT activityType, startDate, startTime, duration, quality, type, endTime, activityNum, activityClass FROM (SELECT * FROM ActivityX ORDER BY activityNum DESC LIMIT $rowBack) s WHERE user=? AND activityType=?");

Anything I can do to speed up the query and make it more reliably fast?

I'll be moving to a VPS in the near future which may solve the problem but I don't want to rely on that.

Thanks,

  • Jon
Jon Schlossberg
  • 329
  • 2
  • 11

2 Answers2

0

Make sure you have an index on (user, activitytype, activitynum). I am not sure what you mean by encrypted data being indexable, that is generally not the case with most normal encryption types.

Over and above that, it sounds like you have a huge noisy neighbour problem.

Gordan Bobić
  • 1,748
  • 13
  • 16
0

Look in here. How do you get server CPU usage and RAM usage with php?.

Also should be working on shared. I've seen such indicators in Instant Cms.

Also you can find here some useful tips. https://severalnines.com/database-blog/how-identify-mysql-performance-issues-slow-queries

noszone
  • 190
  • 1
  • 14