New answers tagged

0

This all depends on the size of the table. You're doing a full table scan without a where clause so an index isn't going to help. The use of the rand() function for sorting is going to produce a temp table and this will harm performance. If you have a primary key you could generate a random number in the app and perform a query where a single row is selected ...


1

This is a too broad of a question sadly as each DB Engine might do it differently. To put it very simply most of them work by making a copy of the data you want sorted in a different way. This way when you look for a range of data it's already sorted so the DB Engine can very quickly find what you want without scanning everything. Imagine if you had ...


1

(This answer is probably more than you wanted. It is an in-depth discussion of the source of the problem, plus multiple solutions.) Long ago, when MySQL added CHARACTER SET utf8, it needed up to 3 bytes per character. And indexing was limited to 767 bytes, enough for VARCHAR(255). When utf8mb4 was added (because utf8 was incomplete), the encoding needed ...


-1

I think you don't need to have any indexes (Rather than primary key) for user table which is mainly used for authentication, because most of the fields in user table has unique rows (correct me if i am wrong), so Cardinality is very high. If you index high cardinality field it might be a performance hit. You can go through this article for more information ...


1

This is working as intended. The indexes are objects written to the disk. The index creation is also written to the online logs. Creating indexes is expected to take up disk space and cause I/O operations.


1

That happens when InnoDB compressed tables with large amount of rows are being updated (no matter if the algorithm is in place). Large operations for ALTER tables, will use the "online log", which is expected to consume space. You can check this using SHOW ENGINE INNODB STATUS in the History Length line. This happens always that you have large operations ...


3

According to the official ALTER TABLE documentation, there should be no issue with such an operation and there is no window when there is no UNIQUE constraint at all. See (emphasis mine): Storage, Performance, and Concurrency Considerations In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that ...


0

The following is written for SQL Server, but should be quite the same with other RDBMS... The clustered index is physically sorted on the storage media and will cover all columns of your table - as if they were include columns. A good clustered index is bound to a column with an implicit sort quality, such as insertDateTime or a running number (e.g. with ...


0

If a table is large and growing fast, clustered index might become too expensive to maintain since the DB server has to reshuffle all the data while rebalancing the tree, not only nodes with key values. It might significantly affect speed of data modification queries. There can be only one clustered index per table because it defines the physical data ...


1

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to extract keys, but that's not so. Assuming all your JSON arrays are either empty or hold integer numbers as demonstrated. And the scalar values (non-arrays) are also ...


4

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans? Yes, the optimizer should recompile the plans since changes to an underlying table are one of the triggers that result in a plan recompilation. Since checking a constraint ...


0

To quote MSDN as an authoritative source: There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating ...


2

The first query is logical nonsense for your case: SELECT count(DISTINCT a.id) from the_view a; It makes no sense to use DISTINCT with the id defined unique. The second query is typically faster with a sequential scan: SELECT count(*) from the_view a; Indexes don't contain visibility information. Due to the MVCC model of Postgres it needs to check ...


10

I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one? This will be a decision of the cost based optimizer. It estimates that it is cheaper to fully scan the narrow index. It seems that you were expecting a nested loops with seeks on the clustered index? The execution plan shows that the table #ToPurge ...


-1

This will throw an error (which you can ignore) if the index doesn't exist and also depends on the key name being the same as the column name, so it's not a very robust solution but I'll add it anyway as it may be useful to someone: ALTER TABLE `table` DROP INDEX `column_name`; ALTER TABLE `table` ADD INDEX (`column_name`);


0

You need to examine one of the following: INFORMATION_SCHEMA.KEY_COLUMN_USAGE INFORMATION_SCHEMA.STATISTICS EXAMPLE #1 You have a table called mydb.mytable You have a columns called mycolumn You want to know if mycolumn is indexed at all Query #1 SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_schema='mydb' AND table_name='mytable' AND ...


-2

alter index "MYSRVR"."PK_ZXCATSET_CATID" rebuild tablespace MYSRVR_IDX_TB; or alter index MYSRVR.PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;


8

TomTom has a great answer with which I completely agree. sp_BlitzErik rightly cites Kendra Little as a good source for acknowledging that partitioning is not a performance feature: Why Table Partitioning Doesn’t Speed Up Query Performance Partitioning is, in fact, a data management feature. As noted by TomTom, bulk DELETE operations that encompass the data ...


0

Rolando, Thanks a lot for your explanation. I followed disc size and file access during this ordeal (46 hours, completed this morning) and yes .. even in MySql 5.6 this is what happens. My statements were: CREATE INDEX idx1 ....; CREATE INDEX idx2 ....; CREATE INDEX idx3 ....; ... and I saw in the "show processlist" how all the data was first copied to a ...


0

To check the column properties you can run the query SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema_name' AND COLUMN_NAME = 'created_at'; This will list all the tables in your schema with the columns created_at. Here you can check some of the column properties including the COLUMN_KEY with values PRI, UNI, MUL. For a detailed ...


2

The difference is not like arrays vs. linked lists (Both are data structures - But not the correct ones.). Both types of indexes use B-Trees (Data Structure) to organize the data. The difference is how the B-Tree is organized. For a clustered index, the data is organized in the data structure with the leafs being physically organized on the disk. This ...


0

You must be doing something odd with your DDL or you have a very, very old version of MySQL. MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago. Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum) May 12, 2011 : Does MySQL still handle indexes in this way? (...


11

Hmmm... The leaf level of a clustered index is the table in SQL Server. This is comparable to a phone book (to borrow from @BrentO) in that all entries in the book are stored in the order of the index - i.e. The index is ordered logically alphabetically (and physically if there is no fragmentation). This is somewhat similar to a physically contiguous in-...


11

You generally followed a delusion - that partitioning will give you a significant performance boost for queries compared to a standard index. This is not the case. There is little difference between filtering with an index and with a partition. Partitions are not there to make your queries faster, but to allow fast DELETE - by swapping out a partition with ...


0

If you are getting the LOWER('Meteora Breaking The Habit') portion of the query from a source that it outside your control, then I would recommend keeping your song/album table structure (although remove album_id from song table), but include a 3rd table: CREATE TABLE ALBUM_SONG ( album_id, song_id, album_song_name -- this would be album.name || song....


0

So as @RickJames, suggested in one of the comments, cardinality has little to do with order of indexes. Therefore if your query is similar to SELECT id, search_id, provider_id FROM t1 WHERE search_id=123 AND provider_id=567 Then the index should be ordered search_id,provider_id. If however you are running a more range based query. For efficiency, ...


0

In this case, it is always better to do an index scan instead of a table scan. All of the needed columns are in the index; that is, it is a "covering" index. The table has lots of columns, correct? So a row in the table is rather wide. The index has two columns -- email the PRIMARY KEY, cid (assuming you are talking about InnoDB). That makes for a much ...


0

g.dataset_id = 3 is killing the left try this FROM markers JOIN genotypes g ON g.marker_id = markers.id AND g.dataset_id = 3 The where is killing the left joins in the exists so you can drop that This may give you better performance and exists ( SELECT 1 FROM genotypes join accession ON genotypes.accession_id = accession....


1

If you solve it by adding a secondary index then imho MyISAM or InnoDB won't matter much, as both use B-Tree indexes. Append both additional columns to the index: (col3, col1, col2) or (col3, col2, col1) should behave the same for your query. The reason to add all the columns is that such index covers your query - all data needed to resolve the query are ...


1

The MSDN page says this about reorganizing and rebuilding indexes: The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when ...


2

If the index contains both cid and email, there is no need to look at the records. All the data will be retrieved from the index. This is a common optimization. The where clause should (on average) reduce the record count to less than four percent of the records. This should be a small enough set to make using the index faster than table scanning. ...


0

Database engine is having its own optimizer.So while parsing and executing any query it evaluates it on cost based optimization.Upon comparison basis on resources utilization,system choose path which results best.


0

How many unique values do you have for crawled_name? How feasible it is that you'll hit an apartment_id value greater than 2 billion (in the next 5-10 years)? Note that COUNT(id) is the same as COUNT(*) (because id is not nullable), and that COUNT(apartment_id) > 10000 is effectively COUNT(*) > 10000 along with WHERE apartment_id IS NOT NULL. What you'...


-2

Yes fragmentation will negatively impact performance. Anything over 30% is bad but on a small table still would not have a major impact. But on a small table defrag is fast so might as well do it. That report you posted is 20 or so rows and starts on 96. You could also have > 30% on some big indexes. In rows 1-95 I can pretty much guarantee you have ...


7

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them. This is a knee-jerk response to a performance problem. Your page counts are less than 10 ! Rebuilding the indexes will be a least help in your case. I would start by reading and understanding How to analyse SQL Server performance ...


1

You can change the tablespace of the index explicitly with alter index command. CREATE TABLESPACE superfastssdtablespace LOCATION '/path/to/super/fast/ssds'; ALTER INDEX name_of_the_index SET TABLESPACE superfastssdtablespace; Before you do that you may (also) want to look at partitioning your table or using BRIN indexes depending on the use case.


1

The large number of nscanned key comparisons is explainable by the skip value: the query is skipping 903,462 documents (ntoskip) in order to return 21 (ntoreturn). The nscanned value in your output is the sum of ntoskip and ntoreturn. The number of nscannedObjects (identical to nscanned) is because the skip stage in MongoDB 2.6.x query processing happens ...



Top 50 recent answers are included