Tag Info

New answers tagged

4

It seems to ignore any index I put on it Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint. Without the hint, the query optimizer (in Enterprise ...


3

Not redundant, but rarely practical. An INDEX has limitations, based on 255 characters. (There are too many wrinkles for me to go into detail.) FULLTEXT is often used for big blocks of text. Would you be looking at 'words' and doing = comparisons on the whole column? Seems strange. Here's one trick for when I want to search for a phrase that messes up ...


2

On your indices Ok, first things first. Assuming some structure like this { _id: new ObjectId(), date: new ISODate(), message: "Hello, Multikey Indices!", tags: ["MongoDB","Indices","Multikey"] } indexing tags would result in a multikey index. For the document above, the index would have three entries: "MongoDB", "Indices" and "Multikey", all ...


0

It would be a great design approach to put the clustered index on your FK if you will be fetching all the lines (I mean all the data of the line) for a given FK, or making joins based on that key : as they're stored physically in this order, they will come up much more quickly. Otherwise you could use a non-clustered index (slower fetching of the actual ...


0

But I think that something is wrong in the joins. Make a query and get the id took 5 seconds, and I think that this is not normal. See for example this query: explain (analyze, buffers) select p.account, p.direction, r.cost, r.usage, p.answer_time, c.timespans, e.extra_fields from cdrs_primary p join rated_cdrs r on p.cgrid = ...


1

The first, most important, thing you can do is get rid of that use of json fields. If you know the field you'll be querying in advance, preferably make it a real column. At the cost of inefficiently widening the table you can do that with a generated column using a trigger, then index the generated column. It'll hurt your insert performance a bit, but you ...


2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple ...


1

This error code [ORA-01654] means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error you can simply add data file using ALTER TABLESPACE ADD DATAFILE ...


0

@Hpk, since you have already answered your own question, my answer will provide you some food for thoughts : I ask because I need a way of rebuilding or reorganizing based on internal fragmentation rather than external and I don't want to create logic to rebuild/reorg if a avg_page_space_used_in_percent < 75 if the fill factor in the first place is 70 ...


0

avg_page_space_used_in_percent does not care about fill factor. Therefore if you set an index to have fill factor of 40, your avg_page_space_used_in_percent will straight away be 40.


0

Under ordinary circumstances, filter conditions can be specified either in WHERE or JOIN clauses. I tend to place filters under WHERE unless OUTER JOIN precedence could be affected (see below) or if the filter is very specific to that table (e.g. TYPE=12 to specify a specific subset of rows in the table). On the other hand both ON and WHERE clauses can be ...


3

There are a few things I can clarify for you here: Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before). Mysqlpump does this by default. When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation). For MySQL 5.7 ...


4

Masi, The PostgreSQL B-Tree index is very strongly based on the implementation by Lehman and Yao, which includes a lot of work oriented around multi-version concurrency control, but there's still great info in this paper. Of course, PostgreSQL doesn't make a 100% accurate replica of the method in the paper, and to find the differences, there will be almost ...


2

Not the stated question but may get better query plans with better queries You are killing the left outer with the where where profiledmo1_.Id=@P0 turns that into a join On indexes just the first two select addressdmo0_.Id as Id1_20_ , ... , addressdmo0_.Zip as Zip14_20_ from dbo.Address addressdmo0_ join dbo.Profile profiledmo1_ ...


1

You probably want to use a contextual index. Then when you search in the clob column you would do something like myclob contains ( 'aword') > 0 This would then be a substitute for regexp. I have not used this feature in a few years, but it does take some work to create and maintain the indexes. You should look for specific examples that people ...


6

Answer to question 1: From what you posted you can drop the first two indexes as the third will cover all of the queries you mention and the query optimizer see that as well when it builds the query plan (based on the plan you posted.) Answer to question 2: It's always using the third index because it has more data already in the index with the two ...


1

It seems as if you could drop index 1 and 2 as index 3 includes all information (columns) you need. It might be possible that another index makes sense as a clustered index to represent the primary key. With this limited information we can just guess. If you need more hints please post more detailed information as your complete table structure (tables, ...


0

Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on) This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.


-3

Can you try the following? select COUNT(id) from [tablename] where [columname] >=3 GROUP BY id;


0

Assuming you have these two indexes: ADD PRIMARY KEY (`domain_id`), ADD UNIQUE KEY `domain_name` (`domain_name`); Then it does not matter what the optimizer does; it must scan essentially an identical amount of stuff. Case 1: It does a table scan (or uses domain_id): It will scan (id, name) pairs, locating all the names, doing the SUBSTRING..LOCATE, ...


9

Your Predicate is different to your Seek Predicate. A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done. But before the data is returned, it checks every row ...


3

GetItemToProcessIndex is not fully seekable because your where clause is on ItemState + LastAccessTime + CreationTime. The indexed columns and the where clause are a not perfect match. If you create a covering index on ItemState + LastAccessTime + CreationTime, for each match you get from GetItemToProcessIndex, you also get the value of your Primary Key ...


2

Index fragmentation is much overrated. Do not worry about it. Two adjacent, somewhat-empty, blocks are merged together by InnoDB as the natural processing. Random actions on a BTree cause it to naturally gravitate toward an average of 69% full. Sure, this is not 100%, but the overhead of "fixing" it is not worth it. SHOW TABLE STATUS gives you some ...


0

Why wouldn't this work? select index_owner,index_name, table_name,column_name,column_position from all_ind_columns where column_name = UPPER('&COLUMN_NAME1') and column_name = UPPER('&COLUMN_NAME2') and column_name = UPPER('&COLUMN_NAME3') ;


1

I would create an index on (monitor_id, timestamp). It should be enough. If not, I'd use LATERAL JOIN. SELECT T.id ,T.timestamp ,T.value FROM controller_monitor INNER JOIN LATERAL ( SELECT controller_monitor_reading.id ,controller_monitor_reading.timestamp ...


3

All of this is unrelated to inheritance and partitioning. It's about indexing and query plans in general. The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even more readily use an index for wider rows. Possible reasons for the unexpected sequential scan include: You have substantially fewer rows in your tables for the ...


1

Let me begin this reply with a caveat: I've never encountered this exact problem, so I don't fully understand the nature of it. However, I'm going to give my advice so that perhaps it gives you the insight you need to complete your task. pageinspect extension Recent versions of PostgreSQL come with an extension which you can install that allows you to ...


4

Your existing index on DATE is obviously useless for the query. The first obvious step for your query: SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2'; is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like: CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b); ...


4

"date" Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like: CREATE TABLE test ( id serial PRIMARY KEY , ts timestamp NOT NULL -- also adding NOT NULL ...


8

Ah! It's the image column. [column11] [image] NULL, Online only works on tables without blobs. Guidelines for Performing Online Index Operations


2

In addition to Craig's thorough answer, I wanted to add that the cover of the book you reference says: Covers Oracle, DB2 & SQL Server So I wouldn't trust it to be a great source of advice on PostgreSQL in particular. Every RDBMS can be surprisingly different! I'm a little confused about your original question, but here's an example showing that ...


1

How do I check fragmentation of a particular index in MySQL, not the table as a whole Pass. Does OPTIMIZE TABLE actually fix the internal / external fragmentation of an index as in SQL Server? It completely rebuilds the table and its indexes. When I optimize a table in MySQL, does it actually rebuilds all the indexes on the table? That's the ...


1

You should disable anything related to such keys before dropping them Using the variables FOREIGN_KEY_CHECKS and UNIQUE_CHECKS in your session, run SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; Doing this in your session makes mysqld bypass referential checks in your session. You are still subject to the usual locks if anyone is accessing the tables ...


2

You could also embed WHERE 1=1 SELECT count(*) as c, SUBSTRING ( domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2 ) as tld FROM `domains_import` WHERE 1=1 GROUP BY tld ORDER BY c desc LIMIT 100 ypercube just asked me Rolando, is MySQL's optimizer so dumb that a simple always true condition ...


11

It's absolutely not clear why you want this but you can use the hint USE INDEX () to tell the optimizer not to use any index. From MySQL docs: index hints It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error. Your query becomes: SELECT count(*) ...


-1

You haven't posted your query or example data. But the most common reason indexes are not used has to do with volume. Indexes are like a phonebook that translates a column to a row location. If you're only looking for a few rows, it makes sense to look up each row in the phonebook, and then look up the row in the main table. But for more than a few rows, ...


0

Just for a bit of background on pros/cons to not having FK's in your database feel free to review this post on StackOverflow. As you can see from that article there are some good reasons (performance penalty on insert/update more painful to the business than dirty data) to go without foreign keys and some bad reasons (laziness/ignorance). I think you ...


8

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either. If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index. I can't figure out what you're trying to say with: If ...


1

Similar to what @Eelke and @Kassandry suggested. But you don't need a counter or trigger. CREATE TABLE t1 ( user_id text NOT NULL -- should probably be integer , photo_nr int NOT NULL , photo_id text NOT NULL , date_created timestamptz NOT NULL DEFAULT now() , CONSTRAINT t1_pkey PRIMARY KEY (user_id, photo_nr) , CONSTRAINT max_30_photos CHECK (photo_nr ...


0

Right now, both your unique index and PK uniquely identify each row. This is redundant. The index can be remove and the cluster can be move into the PK. However it will fail if there are foreign keys on ItemId. First look for foreign key(s): Select OBJECT_NAME(constraint_object_id) From sys.foreign_key_columns where referenced_object_id = ...


5

Short version: no. There's no practical way (in PostgreSQL, at least) to index a pattern column so it can be matched against plaintext inputs in a way that will speed up "does this plaintext match any of these patterns" queries. PostgreSQL would need a special custom index type that "understood" pattern matches. I'm not sure how practical it'd be to ...


1

You could do something like this: Example table: CREATE TABLE t1 (user_id text, photo_id text, photo_limit_count integer NOT NULL, date_created timestamptz); Add a check constraint: ALTER TABLE t1 ADD CONSTRAINT photo_limit_count_chk CHECK (photo_limit_count > 0 AND photo_limit_count <= 30); Create a unique index, to make sure that there can't ...


2

Your program could manually number the records for each user then you could put a unique constraint on the combination of the user_id and the recordnumber (or make it the primary key) and put a check constraint on recordnumber cheking it is between 1 and 30.


1

Indicies are used only to select (find) the rows. Once they are put into a final resultset, it is simply a set of rows and columns, regardless of where they came. Order by (sort) is applied as the last operation to this resultset. In your case, you are getting a very large resultset. Obviously, any effort to sort it would add up to the query time.


3

The other two answers are spot on in that the reason why the two indexes exist: you told the database to create two indexes. Furthermore you could make your primary key clustered and remove the second index. To answer your question about why the second index is "needed" boils down to a limitation/requirement for Sql Server Azure edition databases that ...


4

The real answer is that "prefix" indexes are virtually useless. I am referring to KEY `testStoreTitle` (`storeTitle`(182)) Since the index contains only the truncated values, it does not have a completely ordered list of Titles, hence cannot easily be used to do the ORDER BY. InnoDB has a limit of 767 bytes (the max for a utf8 VARCHAR(255)). That can ...


0

The answer to any performance question is "it depends." Discrete columns can be fast and byte column flags can be fast too. In absolute terms you can probably save a bitwise operation by having discrete columns here and there so discrete columns are theoretically faster. A theoretical bump shouldn't be the main reason to choose a strategy. To paraphrase ...


5

Normally we'd expect that when postgres was restarted, the crash recovery process would have removed files related to a rollback'ed index from the data directory. Let's assume that it didn't work, or at least that it has to be checked manually. The list of files that should be in the datadir can be established with a query like this: select ...


5

David Spillett's answer is correct in all points, except for the "encouraging" suggestion. Here's a way to not only encourage but (in almost all versions) force the optimizer to choose a plan that uses the wanted index to find the 50 rows - and only after that perform the join. It can't always be used but the FOREIGN KEY constraint assures that in this case ...


2

The "why it takes so long" is Using filesort - this means that it is spooling all the results out to sort them without the index. The problem is that the SQL engine can only use one index per table reference. In this case it will be using the index best suited for your join predicate (the one for your primary key). IIRC mySql's query planner still follows ...



Top 50 recent answers are included