Tag Info

Hot answers tagged

15

No. No gain at all. The manual explicitly states: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database ...


6

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are: It can improve read-ahead performance for large range scans; and It may improve the page density (number of rows per page) Both of these factors are less important for small indexes, by definition. The counter-argument to ...


4

Short answer: you can't with 100% accuracy. Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't ...


3

Your problem is not the primary key. It is a misunderstanding of what a clustered index is. If you look at your structure you will see that the primary key is clustered. This is the default and fairly normal but is not always the case so it pays to check. The clustered index is basically the table itself. The data pages of the clustered index are the ...


3

The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it. What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy. This covering index would ...


3

In order to make very small temp tables, you need to rewrite the query to do this: LIMIT first on the 20 most recent created timestamps then JOIN those 20 ids to visits_views Here is the proposed query SELECT A.id FROM (SELECT id FROM visits ORDER BY created DESC LIMIT 20) A LEFT JOIN `visits_views` B ON A.id = B.visit_id; I use LEFT JOIN to preserve ...


2

The index data order on disk for text columns depends on the locales provided by the underlying operating system. The same locales (that is, with the same name) may differ between operating systems on the order rules, even on simple things. As an example this question: PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly ...


2

Being in the database version control space for 5 years (as director of product management at DBmaestro) and having worked as a DBA for over two decades, I can tell you the simple fact that you cannot treat the database objects as you treat your Java, C# or other files. There are many reasons and I'll name a few: Files are stored locally on the ...


2

Starting from 2008 onwards Index rebuild is fully logged in full recovery model. If you rebuild huge index in full recovery model its bound to produce too much logs. So in this table you have how many indexes? Are you rebuilding all such indexes in one go. If you are doing this you must consider doing it piece meal. One index at a time Of course you can ...


2

DBCC REINDEX is minimally logged in the SIMPLE or BULK_LOGGED recovery model. If the database is currently in the FULL recovery model, consider toggling it to BULK_LOGGED for the index maintenance. See http://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


1

Explanation My question is: why does this not use the index amplifier_saturation_start? Even with 30,000,000 rows, only 3,500 in the date range it can be faster to read tuples from the top of the index amplifier_saturation_lddate on lddate. The first row that passes the filter on start can be returned as is. No sort step needed. With a perfectly ...


1

If you ONLY want to optimise for this query. This is the best index: ALTER TABLE items ADD INDEX (category, created_at, user_id) This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index. We can ...


1

Also consider the first answer. Query This does what your current query currently does, just simpler and faster: SELECT p.id, p.note, p.photo, p."createdAt", u.id AS "user.id", u.name AS "user.name", h.id AS "hashtags.id", h.count AS "hashtags.count", h.name AS "hashtags.name", h."createdAt" AS ...


1

Why? The expressed requirement that artist and title must match in the same element of the JSON array is not reflected in your query, which finds all rows where at least one element matches the artist and another (possibly a different one) matches the title. The example data for your first case was inconclusive, since the query cannot fail this way for a ...


1

There are many general solutions to the problem. Here are three that quickly come to mind. SSAS VoltDB Map Reduce Framework What you're describing is what would historically fall under an OLAP solution. If you can handle some lag time in your data then perhaps a solution such as SQL Server Analytic Services (SSAS) could provide you a solution. You could ...


1

Yes, there is a safe way (not faster though) to alter schemas if you use tool. This does not require any downtime. The one we use everyday on our production is to use pt-online-schema-change this tool will show you the progress of the SQL. I hope this helps


1

Querying the plan cache to look for the indexes involved in the calculation of finding missing indexes DMV's can be looked from below pasted link: http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ and refer to link as well for more explanation on the same ...


1

That's a tough question to answer without more schema and workload information, but the key benefit to a clustered secondary index is that it contains not just the indexed columns and all other columns in the table. "Normal" secondary indexes contain the indexed columns and the primary key (which is used to look-up the other columns for your SELECT ...


1

Dropping multiple indexes all in one SQL command is way better. Why ? SCENARIO #1 ALTER TABLE mytable DROP INDEX ndx1; ALTER TABLE mytable DROP INDEX ndx2; ALTER TABLE mytable DROP INDEX ndx3; This is what would happen Creates an empty temp table Removes the index from the empty temp table Copies the data into the temp table Swaps temp table with ...


1

Rather than try to guess, you should download Percona Tools. You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it: ...



Only top voted, non community-wiki answers of a minimum length are eligible