Coming from the FileMaker world, indexing wasn’t something that was talked about much… especially in the early days.
Lately, we’ve been reviewing our SaaS Servoy solution and watching our performance and then indexing the proper columns when we find a query that is taking a long time.
We have one query that’s quite robust, and it continues to take about 7 to 10 seconds to run.
Also our reports seem to take quite awhile to come up.
The culprit seems to be the summarizing of AmountExtended calc columns and Invoice Total calc columns. (They are calculations in Servoy) Should we be indexing these columns? Any performance hit by indexing them?
Difficult to answer without knowing your query and what you do in these calcs…
AmountExtended InvoiceTotal calcs are stored? What if you don’t store them?
InvoiceTotal looks like an aggregate really, have you tried using it instead?
Do your calcs use relations? Because these are the kind of stuff that will take some time to compute (possibly involving queries for every iteration…)
You could look at the performance tab in your server-admin to check the queries that are generated.
I am using Oracle and we use trace options to find out the execution plan of queries.
The traces tell you where indexes could be usefull.
Some tools even give more advise on your datamodel and database settings.
A good datamodel in my opinion is the most important factor in performance.
I guess such tools are also available for MySql.
Also there is a lot of documentation and best practices on datamodelling on the web.
Most tips are not vendor specific.
Hans Nieuwenhuis:
I am using Oracle and we use trace options to find out the execution plan of queries.
The traces tell you where indexes could be usefull.
Some tools even give more advise on your datamodel and database settings.
A good datamodel in my opinion is the most important factor in performance.
I guess such tools are also available for MySql.
Also there is a lot of documentation and best practices on datamodelling on the web.
Most tips are not vendor specific.
my main tip would be to switch the slow query log on on the database, and then dont be too timid in adding multiple indexes. I was and found that they take up very little room and dont really reduce the write speed of your data.
So if you have a query using muliptle where’s then add an complex index which covers all of them, you can also use the Explain function in mysql to see whether your index gets used, but normally you will just see a massive improvement in your query speed. It worth using SQL Pro or another tool to try them out in raw sql to see what the speed is.