New answers tagged

4

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes can then be seen much more explicitly (and both can be minimally logged). In this case if you were to create the temp table with an unnamed primary key constraint on the column up front ...


1

a) .. engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index .. - Only InnoDB uses clustering by PK, MyISAM uses HEAP tables (no clustering) and the primary key is just another index with all indexes using pointer/offset to the heap to find the right row. b) sequential vs ...


0

In the original description of a B-tree the internal nodes held not just the index keys but all columns of the table. In the B+-tree only the leaf nodes store all columns' values. As each node in the tree is a fixed size, removing non-key data from internal nodes frees space for more keys to be held. All operators can be made to work with the BTree/ B+Tree ...


1

With a Cassandra index (i.e. a "secondary index", as opposed to primary keys), each node has to query its own local data for responding to a query (see the Cassandra [secondary indexexes FAQ] (https://wiki.apache.org/cassandra/SecondaryIndexes)). These index are also built using a background process. This backgrounding means that the index may return false ...


1

Indexing can have several purposes: - Access your data faster and to accelerate the execution time of your queries - Define the degree of uniqueness of a given column: Should every field be unique? Are duplicates allowed? When you send a request to your MySQL server, it is first assigned to the "parser" SQL which aims to verify the syntax of your request is ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


0

To check if an index is working, use SELECT COUNT(*) rather than SELECT *, otherwise you're not only measuring index performance but also the time it takes to transfer all the data across your client application, which in this case is a whooping 50,000 rows! You can significantly simplify your query and index by date datatype instead of timestamp, which is ...


2

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages. This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to ...


8

You actually have 595,947 matching rows, which is about 3% of your data. So the cost of the lookup adds up quickly. Suppose you have 100 rows per page in your table, that's 200,000 pages to read in a table scan. That's a lot cheaper than doing 595,947 lookups. With the GROUP BY clause in the question, I think you'll be better off with a composite key on ...


5

The field in your WHERE condition is not the leading field of the index. You have measure defined as NVARCHAR so prefix the literal with an N: where Measure = N'FinanceFICOScore'. Consider creating a Clustered Index on SnapshotKey. If it is unique then it can be a PK (and Clustered). If not unique then it cannot be a PK, but can still be a non-unique ...


12

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here. In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID ...


3

some say, cluster index stores the whole records in deepest leaf of index. Yes this is correct. The leaf pages of the clustered index contain the actual table rows. This is the actual data. There is no other primary copy of it held elsewhere. The leaf pages also allocate a few bytes to hold the address (file and page) of the next and previous pages in ...


1

I have now found the source of the issue. It is because my ri_email_messages table was using the Aria engine. I had done this because InnoDB did not support full text search at the time. In MariaDB > 10.0.5, you can now do fulltext search in InnoDB, so I have switched back. The way to tell in the EXPLAIN statement is that the PRIMARY query was using ...


1

This can get confusing for people to wrap their heads around. Let's get a couple of points clear in MS SQL Server. MS SQL Server has 2 types of tables: Heaps and Clustered Indexes. A heap has no order what so ever to it. Imagine taking a thousand names and throwing them in the air, then scrambling them around. That's essentially a heap. A heap uses RID ...


2

FWIW, I tried investigating the differences between a newly created index in a db copy and the original index in syscat.indexes, sysibm.sysindexes and sysstat.indexes. The only difference I could spot was a slightly better density (100) for the new index compared with the old one (87). I therefore ended up recreating the index, and now the execution plan is ...


1

Indexes help to find data, sure, so you may choose not to index small tables. They also have a role in data quality. For example, to ensure a value occurs in at most one row in a table, a unique index could be used. For these roles the index must be defined not mater the number of rows in the table.


0

Whether or not performance is affected will be a function of data volume and machine capacity. Given the capacity of modern hardware, it's hard to imagine ticket sales volume that couldn't be handled by the design you describe. However, there are changes I would recommend for correctness, and might improve performance as a secondary benefit. Your get ...


0

Your database is likely corrupted, especially the indexes. You can rebuild all indexes and check all tables on a database by: REINDEX SYSTEM; -- run once REINDEX DATABASE <your dbname>; -- run for each database VACUUM (FULL VERBOSE ANALYZE); -- also run for each database after reindexing


5

Let us consider a dataset with N items, of which P items match the predicate and will be returned by the query. 1a.A linear search, unsorted data. There is no way for the algorithm to know whether a value will match the predicate until that value is read. Therefore the only solution is to read all values and ignore those which do not match the predicate. ...


3

For this WHERE clause, I would try an index on (customer_account_id, sync_inactive, last_modified, id): ALTER TABLE main_contactinfo ADD INDEX customer_active_last_modified ( customer_account_id, sync_inactive, last_modified, id ) ; The ORDER BY with e small LIMIT complicates things though, so a different order of the columns in the ...


2

Just going by the MySQL Documentation, I would say yes. According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly ...


3

Where a filtered index on a computed column is too limited, you have the option of creating an indexed view. The indexed view is maintained automatically by the database, so you do not need to worry about getting trigger logic correct for all possible DML operations. You also do not have to worry about complicated correctness problems under high ...


2

In order to make this blisteringly fast, what I would do is the following - with the proviso that I know nothing about your server RAM, CPU or disk config. Bear in mind that a decade's records will be 35M/year - so 350M for a decade - most servers should be able to cope with a small table like this - but I don't know what else is happening on your server. ...


1

As others have said, number of rows is no guide. Find a query that is typical for your system and execute it in SSMS with io on: set statistics io on; select * from SomeTable order by SomeColumn; Look on the Messages tab, at the "logical reads" counter. This is the number of pages that need to be read to satisfy the query. (The "logical" means it does not ...


1

There is no reason to not use an index. If the table is small, the cost estimator can easily deduce that a sequential scan has less overhead than an index scan followed by random access into the table data, because the index accesses are associated with a cost as well, and sequential scans also have lower cost than random access. The only downside is that ...


1

I don't know about a best but at a small number there is no real value. There is some overhead to using the index. Yes an index seek is faster than a table scan but there is some overhead to using the index. Index maintenance clearly has overhead. If a table has a PK then you should use that as a PK and typically clustered. Consider a table of ...


6

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. I want to ...


2

I thought about adding a computed column like this: ALTER TABLE dbo.mytable ADD Diff AS InboundQuantity - OutboundQuantity PERSISTED ; go CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity) WHERE Diff <> 0 ; but that does not work. This is a bit of a kludge, ...


0

The code for such is here, but it only works for "Type 1" UUIDs. It's a matter of shuffling the bits around. And while you are at it, BINARY(16) is more compact than VARCHAR(36) or whatever you have now. More By rearranging the bits of a type-1 UUID, you can take advantage of "locality of reference" when using an index on it. For example, if you had ...


3

The SRID does not appear to be an integral part of the spatial index nor can it be added. It is integral to the geometry even though there are no real tools for manipulating geometries based on the SRIDs in SQL Server. It does have a few rules about SRIDs. Geographies must have a SRID from a list of specific SRIDs. When comparing, manipulating geometries ...


2

I deem that there are multiple (very important) aspects that you need to consider before deciding which tool you are going to employ to develop your project. The primary objective should be to manage the pertinent data as it is, a quite valuable organizational asset, and a reliable manner to achieve said objective is by way of technical means that are ...


3

I think you will find that if you use an SSIS package to move your data between multiple servers @RobFarley's suggestion will work just fine. You are transmitting information across the network either way. In one case a backup file and in the other the data. However if you are absolutely against the idea you can use a combination of the two ideas. ...


4

As this database will only be used for reporting, we are thinking of shrinking the database. A one time sensible shrink is OK. Disk space is cheap ! Increases the index fragmentation is the side effect of doing a shrink operation. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity. Shrink database ...


4

Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.


-3

Fill the SQL tables with the worst case, a looooooot of happy users! Then see how performant it is with a lot of queries and then try to copy the data into Mongo. Might help you decide when the best for switching is. I usually go with NOW! ;-) Also not sure what SQL offers these days, but in Mongo you can do sharding across multiple servers if the ...


0

Agree with Mark mostly, but Mongo is not that bad. The chirp table would be better with the userid instead of the name, and then using the right calls, you could populate the data from the user collection/table, that is a lookup in an indexed field, pretty fast in Mongo. The indexes themselves are not to worry about, as long as you keep them flat and only ...


2

Given your description I strongly suggest against using MongoDB. Not because it would necessarily a bad choice (although I believe it is in your case for reasons other than pure technical ones). Here are the points that caught my eye. Data modeling Trying to use MongoDB with relational data model with no adaptations almost always leads to tears and misery ...


3

Based on the information you have provided, here is my suggestion on how to get good performance when querying the latest users that have cast a vote on a given sub item (performance is assumed to be your goal, given that you are asking about what indexes to add.) Create the following table: CREATE TABLE vote_sub_item_cast_uncast ( vote_sub_item_id INT ...


2

This depends on so much stuff. What are you building and how complex will your DB queries be? Hobby or professional use? Is the data critical? Do you need transaction safety? Do you already have all the data, in what format? or do you start from scratch? how many users do you expect? how many requests? Are you bound to PHP or can you use Node.JS? Is it a ...


1

I have noticed the same thing in last week. Sometimes, the mongo shell returns any empty array: Try running the currentOp() many times to get the progress. Use use db command and then perform db.currentOp()


2

Hash indexes are missing in action in PostgreSQL. PostgreSQL knows it needs hash indexes, and that it's code for hash indexes is old and moldy, but they don't remove it because they are waiting for someone to come along and overhaul hash indexing. See this thread: http://www.postgresql.org/message-id/4407.1115698257@sss.pgh.pa.us


1

This was an issue with 9.5.0. I upgraded to 9.5.2 and things are ok.


1

Your WHERE condition is not selective enough. It returns 1631 rows which is 1/5 of the whole table, while it should be not more than 1/20 for an index to be used.


0

I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed: select hostname, criticality, source, message, record_date from eventlog.logs l1 where not exists ( SELECT 1 FROM eventlog.rules r where l1.message like r.content and r.type = 'DROP' ) and criticality in ('High', 'Medium') and record_date > sysdate() - ...


5

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index. For instance: CREATE INDEX IX_temp ON dbo.SomeTable(SomeKey) WHERE SomeKey IS NULL; DROP INDEX dbo.SomeTable.IX_temp; I've created a script to ...


0

UPDATE table SET AGE = 69 WHERE id = 'some_id'; Will be performed quite efficiently since id is the PRIMARY KEY. Since AGE is not in any index, there is no INDEX will be modified.


1

It is logical to think that the current databases are smart enough to not update the index which data have not changed. But this logic should be straight supported by Mysql manual. And I've really found this statement on page which describes Speed of UPDATE Statements: An update statement is optimized like a SELECT query with the additional overhead ...


0

For date key you might prefer a number. So for April 7th 2016 201604 (Or 20160407 in case of extended requirements) might be best for your date. I don't think that in a DW you need an IDENTITY column for a primary key; After all, you're more aggregating then asking to see a specific value. What I do is make sure the index avoids duplication of values: for ...


0

Many optimizations in database administration share a common goal: Reduce the working set of the data so that less needs to be kept in memory Since the granularity of caching is done at a page level (both in InnoDB and de-facto in MyISAM due to filesystem block), having large 42-column rows means that you will fit fewer rows per page on average and there ...


1

Horizontal PARTITIONing, without a clear picture of why, is usually a mistake. There are very few cases where such is beneficial to performance. Please describe your PARTITION BY... clause and why you think it will help. I agree that InnoDB is the way to go today. Vertical partitioning is sometimes useful. Again, need more details to give a straight ...



Top 50 recent answers are included