Database optimization aspects

From https://www.quora.com/ I-have-a-table-that-has-1-3-billion-sales-rows-and-most-of-the-columns-are-indexed-it-still-takes-a-long-time-for-me-to-r Un-a-procedure-or-a-view-even-with-conditions-how-do-i-solve-the-issue

Several possibilities come to mind:
1) Indexing strategy
2) Efficient queries
3) Resource Configuration
4) Database Design

First-perhaps your indexing strategy can be improved.

Here is some general guidelines consider them carefully-google these concepts to see if they apply to you:

1) Define a primary key (in most database systems, this would create an index)
2) Put indexes on columns used in SQL joins.
3) Put indexes on columns that is used as conditions in WHERE clauses (if the This results in indexing LOTS of columns th En you may have database design issues).
4) Put indexes on columns this appear in GROUP by or ORDER by

You said, "Most of the columns is indexed". It's important to carefully choose which columns get indexed, and which columns to include together in one index.

For example, consider a CUSTOMER table with these columns:
cust_id, email, fname, lname, Address_1, Address_2, city, state, zip, phone

If your queries often include city, state, zip together in the same SQL then it would bes to create a single index tha T includes all three columns.

But if a query uses just the zip and the index might not get used at all. In this case creating a index just for the zip makes sense.

Also, "Most of the columns is indexed" makes me wonder if you had a lot of unused indexes on your table. If you had columns and most of them is indexed then it's a fair guess that's the most of the indexes is not needed. Query your database system to get a index usage count. Consider dropping unused or seldom-used indexes.

Second-efficient Queries:
Just as important as having a good indexing strategy-are your queries written efficiently? Again, some very general guidelines:
1) Identify the slowest queries
2) Examine the query execution plan to see if the expected indexes is being used; If they is not a being used, refer to guidelines above-see what this might is the case.
3) is your using just a few SQL statements to satisfy a broad number of uses? I often find that is used this.

and third-does your platform have enough resources?
Run Diagnostics to the If your database server is the CPU bound or I/O bound or to the check for other problems.

Finally-database design. This was last in my list because it was the most time-consuming. This is a huge topic the won ' t explore here.

Have you considered partitioning your table?

This is a complex topic, requires detailed review on a case-by-case basis. Examine your query plans in detail. I hope I ' ve given you enough material to begin your.

I want to acknowledge the advice from others to move to another system (I-ve had a lot of success with Vertica DB), but I Think should start by looking at indexing and query effiecency to see if there is the for immediate improvement.

Database optimization aspects