@Renegade - Just for the record, I designed and wrote the entire system...From the db, to the UI ... So the shooting option is out...
But to be fair:
I seldom use joins.
all the critical stuff is indexed.
and I haven't a clue what denormalizing is/does. ...And I suspect it's rather unlikely that I've already done it by accident.
"Not normalized" is my #1 database pet peeve that sets me off into a irrational, frothy-mouthed, insane frenzy.
(I just had another episode last night dealing with WordPress and GUIDs in its database...)
"Denormalizing" is a way of tuning a database to perform better under specific business logic restrictions that effectively pollute the absolute integrity of the data, but not enough to make it useless -- only enough to sacrifice integrity that your business logic will never require. e.g. You should always have family names in their own field, but if you will never ever use only part of a full name, then you can collapse all name fields into 1 field. Now you can never know the family name, but you can know the full name faster and easier.
For indexing in MySQL, you might be surprised at just how much extra performance you can get by indexing fields that you would not normally index. I was working on a largish database and started experimenting with performance there -- additional indexing helped a fair bit.
Anyhow, here's the thing... While I could, with agonizing care, try restructuring the report queries. I'm not entirely convinced that that will truly give me net effect I'm looking. I say this because of the following reasons:
- as I mentioned before 99% of the time everything runs just fine
- Sometimes a large query really is just that: 5,000+ clients, Several thousand items (seasoned with pricing/quantity info that gets tallied & totaled on multiple levels), data set is for the last many years... (Yada yada)
- It is only the large reports (that should be CPU hogging, yet are not) that are taking time to run
- The server the queries are run against never shows more that 20% (per core) usage during the query.
- There are no hardware based reasons (tons of memory available, no disk contention, etc.) for it to take that long.
- I know that I used the conservative resource settings back when I originally deployed the rig.
So... I'm looking for more of a "Let Loose the Reins!" type of solution so the MySQL server service can have have a better crack at the CPU's "time". I just don't want to over do it because there are a few other things the box does/needs to be handling also.
Sounds like a tough nut to crack.
Is it just that the query itself takes a long time? With no resource issues... Bottleneck in there?
Have you tried a query profiler?