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
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/ ?