Tag Info

New answers tagged

1

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on ...


6

how can I utilise SQL Server tools to identify query optimisations, missing indexes etc? No tool is perfect and you should not rely implementing the suggestions without proper testing your entire business life cycle. Native tools like DTA are OK, but I have seen many problems with using it. I would not rely on it. That being said, SQL Server exposes a ...


3

I have a broad question regarding SQL Server... Depending on which server version you have access to, the tools vary. Microsoft has an entire documentation section on optimization tools for each of the different server versions. Despite the differences, the fundamentals are the same across most versions. Missing indexes, for example, are addressed in ...


1

For it worth : I"ve ran optimize table on the main tables of a database who works under heavy load. The main tables have been under great load of inserts/updates/deletes. The optimize reduced the table sizes by 90% and improved the system performance by more than twice !


0

Other alternative's are: SELECT DISTINCT l.a, l.b FROM links l LEFT JOIN t_i ON t_i.i_id IN (l.a, l.b) LEFT JOIN t_o ON t_o.o_id IN (l.a, l.b) WHERE COALESCE(t_i.i_id, t_o.o_id) IS NOT NULL; SELECT l.a, l.b FROM links l WHERE EXISTS ( SELECT 1 FROM t_i WHERE t_i.i_id IN (l.a, l.b) UNION ALL SELECT 1 FROM t_o WHERE ...


0

As I understand you want to check each combination of a/b and i_id/o_id then simple rewrite to join is like this: SELECT a, b FROM links JOIN t_i ON (a = i_id OR b = i_id) WHERE id = x UNION SELECT a, b FROM links JOIN t_o ON (a = o_id OR b = o_id) WHERE id = x But for good index usage I suggest splitting it once more. It is even longer ...


4

{ This is a little lengthy, perhaps, but your actual problem(s) cannot be solved by looking at execution plans. There are two main issues, and both are architectural. } Distractions Let's start with the items that are not your major problem areas. These are things that should be looked into as it definitely helps improve performance to use the datatypes ...


4

In what situations is varchar(max) preferred Commenters have addressed this point in detail already. I'd say that VARCHAR(MAX) generally makes sense if you are 100% sure that the column will never need non-ASCII characters and the column's maximum length is either unknown or greater than 8,000 characters. You can read ...


1

Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here ...


1

Learn about composite indexes; use them where appropriate. For example: ON cpr.product_id = cp.id AND cpr.branch_id = 3 begs for either of these: INDEX(product_id, branch_id) INDEX(branch_id, product_id) Indexing flags (such as active) rarely useful. Don't use LEFT unless the "right" table has optional data. What do you expect the ORDER BY to do if ...


1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need ...


0

MySQL still (I thought they were fixing this) requires that the left most part of an index is used in the query: http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html Yes, you want the highest cardinality first always. Be aware that like oracle, innodb stores its indexes in clustered leafs off the primary index, so any secondary index call is ...



Top 50 recent answers are included