Performance Optimisations

pts.
Tags:
MySQL
I need to optimize the DB for my Company web site expecially I need to be sure that I have all the right views. There is tool that analize the SQL request and propose to create the needed views? Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

There are two parts to this answer:

First, views: Nobody needs views, except as a convenience. Views are macros that expand to queries. Thus,

Example A:

CREATE VIEW myView AS
SELECT account,
SUM(amount)
FROM accounts
GROUP BY account;

SELECT * FROM myView;

Example B:

SELECT account,
SUM(amount)
FROM accounts
GROUP BY account;

Both of these take the same amount of time, because they’re both the same query.

Part 2:

The real question is whether your indeces are good enough. In a lightly writing, heavily reading system, you pretty much want and index for everything you’re searching on. In a system that reads and writes a lot, you have to experiment as to whether each non-obvious index is worth it.

In most tools, there’s an ANALYZE QUERY or EXPLAIN PLAN menu option. Use it on each query. If you find an index range scan, a table scan, or too many hashes, then you need to add an index.

In Oracle, you may have to add HINTs. (Search for HINT or “/*+”. In DB2, it will handle optimization for you.


Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following