I have gotten by until now with ensuing 3rd order norm and having good indexes & foreign keys.

Today an AJAX query is exceeding 30 seconds on a modest search (PHP builds JSON arrays for each candidate who has multiple jobs which have multiple skills, with approx. 1.5k candidates with average 5 jobs with average 5 skills & not a lot of other data (start/end date, company name & address).

I started using microtome() in PH and each query is taking about 0.075 seconds. Sure, there are a lot of queries (max 1,500 x 5 x 5?), and the database will grow, but 0.075 seconds seems long to me, for a query with no JOINs.

I am currently using Kachegrind to profile my PHP code, but perhaps I ought to be looking at my SQL?

I am looking for a gratis tool, for Widows, which will :

  • Analyse my table structure, and offer optimizations (e.g indices/foreign keys/data types)
  • Analyse my data, in addition to the bare structure, if anything can be gleaned from that
  • Monitor query performance at run time & offer suggestions for improvements

I haven't really looked into this before, so can’t give much detail on what I am after, alas. I envisage a tool where I click a button and it gives me the low hanging fruit of optimization, at least the 80% of the 80/20 rule. I realize that manual optimization is likely to be better, but I don’t have time to get really up to speed at guru level, so can accept a tool that gives my performance a boost. E.g stuff like this

Or, should I be asking at https://dba.stackexchange.com/ ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.