Tag Info

New answers tagged

0

@Akash already gave an accurate explanation. As for your second case: SELECT * FROM kodiall WHERE numfield=NULL This is a gross misunderstanding of the NULL value. Per definition, nothing equals NULL, not even NULL. numfield=NULL always returns NULL, and only TRUE qualifies in a WHERE condition. So you get 0 rows for this, always. Postgres doesn't need ...


4

While you EXPLAIN your query, in the first instance, you can see that the planner estimates the total row count to 1 Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 **rows=1** width=193) Using a index in this case is beneficial as there are hardly any rows matching your WHERE condition. In the second case, there are around 1080 rows ...


2

Too bad If you cannot change the query at all, that's too bad. You won't get a good solution. If you had not table-qualified the table (run.frames_stat), you could create a materialized view (see below) with the same name in another schema (or just a temporary one) and adapt the search_path (optionally just in sessions where this is desirable) - for hugely ...


1

You could try creating an INSTEAD OF SELECT rule on the table .. although this might break the application (depending on what all actually uses the table in question) CREATE RULE "RETURN_MODIFIED_SELECT" AS ON SELECT TO run.frames_stat DO INSTEAD <MY QUERY FROM BELOW>; I have not used RULEs that much, personally - so I may have this ...


1

Compound indexes are great for static values Flip the WHERE components so that static values are first db.data.find( { c:"test", d:"yes", a: {$gte:1,$lt:2}, b: {$gt: 3} }).sort( { b: 1 } ) Create a different compound index db.data.ensureIndex({c:1},{d:1},{b:1},{a:1}) How does it help ? Static c value Static d value Sorted Range on b One more thing ...


1

SHORT ANSWER Only as a last resort LONG ANSWER Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer. I have written about this before Sep 18, 2012 : How are multiple indexes used in a query by MySQL? Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2) In essence, MySQL will do lookups along ...


1

If you are testing a and b, INDEX(a, b) is likely to be better. Indexing a flag (by itself) is almost never useful. Please provide SHOW CREATE TABLE and a few WHERE clauses; I will give specific advice. Here's a quick cookbook for building an INDEX that will often be optimal. Given a WHERE with a bunch of expressions connected by AND: List all the ...


2

Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course. No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index. No, I don't think your query should take ...


0

When a query plan is constructed, MySQL finally decides to use only one of the indexes on the table which means that multiple single column indexes will never be used in a single query. However, if you have multi-column single index which covers all the required fields (coverage index), the database will never have to read from the disc which will make the ...


0

There is no clear answer to your question, as it really depends on the query. BUT: If you like to filter e.g. for two colums a combined index will have a better effect.


2

Your query: SELECT scratch_tickets.id AS ticket_id, -- more columns FROM (scratch_tickets) WHERE scratch_tickets.created_at >='2015-02-12 00:00:00' AND created_at <= '2015-02-18 23:59:59' AND scratch_tickets.status = 4 LIMIT 50 ; needs a simple index on (status, created_at): ALTER TABLE scratch_tickets ADD INDEX ...


1

Indexes essentially duplicate data on disk. The idea though is the tradeoff in performance. Extra disk used, but faster response looking up data. But since indexes do cost extra disk, one should weigh carefully whether or not the index is actually needed. If not needed, it will cost that extra disk and may even cost you in terms of performance (especially ...


1

When combining two tables with FULL OUTER JOIN all rows are included in the result. Typically, it doesn't make sense to use indexes for the joining columns. That's why attempts with indexes on (ts_minute, user_id, campaign_id, host_id) will be fruitless. It just so happens that the first column also fits the relevant predicate on ts_minute. See below. The ...


0

I do wonder, why you have the report_type as attribute of the question? Be that as it may, your objective: The objective of the query is to figure out per county, district and report type for a specific question how many reports we have that have answered that question. Why would you include report_name in GROUP BY step? That conflicts with your ...


0

That query doesn't work. You need to use sys.indexes and sys.objects. Moreover, since Primary Keys are Indexes indeed in Sql Server, if you follow a naming convention where creating indexes, you can use the prefix for filtering out Primary Keys from 'other' indexes. In the following example, i use IX as prefix for indexes, but any other will work: ...


1

Your explanation here is not quite right: When checkpoint reached or WAL buffer becomes full, these WAL files are written to data files. What happens when a CHECKPOINT hits is that dirty buffers held in shared_buffers are guaranteed to be written (i.e. fsync'ed) to disk. The WAL files must have already been fsync'ed to disk at COMMIT time, assuming ...


5

@AaronBertrand has a very good answer and is correct - it depends. I'd like to add that you're only looking at one metric - index fragmentation. That's a good start, but there are other things to consider. Consider the following scenario (using AdventureWorks2014): select * from Sales.SalesOrderDetail where SalesOrderID < 50000; --modify delete from ...


5

The big difference, in my environment, is that Reorganize is single threaded, and Rebuild can go as parallel as you have processors. Rebuild also has the added bonus of updating your statistics, so you can forgo separate statistics updates where you've rebuilt (unless you need to scan at a higher interval than the internal MS algorithm.). From my ...


0

More indexes --> slower INSERTs. (However, this may not be a critical issue.) More indexes --> slower UPDATEs when you modify an indexed column. Indexing a flag (or other low-cardinality field) --> almost never will the optimizer use that index, so it is a wasted index. (I'm guessing is_active is such.) However, a compound index that includes that flag ...


1

InnoDB: The PRIMARY KEY is UNIQUE and clustered -- always. Secondary keys are non-clustered. They have the PRIMARY KEY (not a rownum or rowid) in the leaf node of the secondary key index. MyISAM: Keys are non-clustered -- always. The leaf nodes of indexes have a pointer (think: byte offset) into the data file to get from the index to the data. ALTER ...


2

There are actually two different variants of the IN construct in Postgres. One works with subquery expressions, the other one with a row expression, which is just shorthand for expression = value1 OR expression = value2 OR ... You are using the second form, which is fine for a short list, but much slower for long lists. Provide your list of values as ...


3

Your index attempts are still futile, since the predicate u."name" LIKE 'garr%' is obviously the most selective one but cannot use a basic (default) b-tree index unless you are operating with the "C" locale (which is uncommon). SELECT * FROM "Users" WHERE name LIKE 'garr%' AND id <> 2449214 AND hellbanned IS NULL AND ...


-3

Maybe you will try to use nested query... select * from (SELECT * FROM Users AS u WHERE u."name" LIKE 'garr%' AND u.id NOT IN (2449214) ) AS u WHERE u.hellbanned IS NULL AND u.hellbanPostsAfterDate IS NULL ORDER BY followerCount DESC NULLS LAST LIMIT 3;


8

Let me quote another part of that same page, which basically says IT DEPENDS - emphasis mine: These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best ...


4

John M's link in the comments is perfect, but if you want a quick summary: Covering indices allow the engine to pull all of the relevant data directly from the values in the indices themselves; in this way, the data pages never have to be loaded and parsed to fulfil the query and build the result set. Example with index on (A,B): SELECT B FROM table1 ...


1

It depends. Prefix indexing INDEX baz(800) is rarely useful. MyISAM and InnoDB work dramatically differently in the area you are in. In InnoDB, if id is the PRIMARY KEY, it is almost useless to have an index starting with id. This is because the PK is clustered with the data. For MyISAM, the PK is a separate BTree, so the analysis is quite different. If ...


3

You assumption about adjacency is correct. If we use TPC-H as an example: Clustering the LINEITEMS table on on ORDERID will locate all order lines belonging to the same LINEITEM physically adjacent on disk. This speeds up queries that fetch all order lines for a given ORDERID. Clustering on the foreign key to the parent also allow fast merge joins between ...


0

What is the value of sort_buffer_size? 200M might be reasonable on your machine. (A sort might be involved in the index creation.) Keep in mind that it will rebuild all the indexes. Still 58 hours is unreasonable. What is the value of innodb_buffer_pool_size? It may be using that. It should be about 11G for that size machine, assuming you are using ...


4

Instead of using a huge IN-list, join on a VALUES expression, or if the list is large enough, use a temp table, index it, then join on it. It'd be nice if PostgreSQL could do this internally & automatically but at this point the planner doesn't know how. Similar topics: http://stackoverflow.com/q/24647503/398670 ...


4

In short: No. There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure. When the index name is ...


1

I would put an index on the foreign key ( foreign keys don't automatically have an index created) and check out Kimberley Tripps article below. http://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/


1

Have a look at the query execution plan, that should tell you if it is using an index. I don't know why a sort join couldn't use an index, keys can be read from the index and then sorted if required, which saves scanning the table.


2

For a moment I though one might be able to use a pre-existing text_pattern_ops index with the USING INDEX clause when adding a UNIQUE CONSTRAINT. But that fails, because: ERROR: index "book2_name_like" does not have default sorting behavior Per documentation: The index cannot have expression columns nor be a partial index. Also, it must be a ...


2

Be careful. Your two designs are not the same. Case 1 implements a unique constraint on the name of the book but includes case, so "The Lord of the Flies" and "the lord of the flies" would be different. Case 1 then creates a second index to support efficient searching of queries of the form select * from book where lower(name) like 'the lord%'; Case 2 ...



Top 50 recent answers are included