Tag Info

Hot answers tagged

26

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val. Since id is the key it will be indexed and the order by will be a trivial amount of work. To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows. Change both queries to order by ...


6

First, you need to know what you are doing to InnoDB when you plow millions of rows into an InnoDB table. Let's take a look at the InnoDB Architecture. In the upper left corner, there is an illustration of the InnoDB Buffer Pool. Notice there is a section of it dedicated to the insert buffer. What does that do ? It is ised to migrate changes to secondary ...


6

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does. This is because SQL is "declarative" not "procedural": you say what you want, not how to do it. It's nicer to read though...


5

Assuming you are dealing with a single time zone at a time (e.g. British Time), and can identify all rows that are in British Time (maybe that's everyone so far), you could create a calendar table that has the DST time change dates for Summer Time. You subtract an hour for all times that fall between, and don't touch the ones that don't. Sample data: CREATE ...


5

The performance difference in your query is well explained by MG. I am going to address this: I've always believed that * queries should be avoided specifically for performance reasons. select * carries no particular penalties by itself, it is problematic when misused. In a single-table query it works just fine. now join that table to another with 20 ...


4

I wanted to write a comment (as this is not a definitive answer), but it became too long: I am going to give you several broad pieces of advice, and we can go into details for each one, if you want: Reduce durability (you have already done some of it). Latest versions allow even doing it more. You can go as far as disabling the double write buffer,as ...


4

Best is to use a calendar table. To update, do it in batches. shamelessly copied from Aaron's 3 part series on MSSQLTips. Thanks @AaronBertrand ! Note: Read all the 3 parts thoroughly as I have implemented the concept in my company and it works flawless. CREATE TABLE dbo.TimeZones ( TimeZoneID TINYINT NOT NULL PRIMARY KEY, StandardDescription ...


4

You can use the following query to determine how much RAM is being used by each database: USE master; SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.databases d ON bd.database_id = d.database_id GROUP BY d.name ORDER BY d.name; To see memory used by objects in a specific ...


3

I will take you question point wise 1.In my opinion its higly unlikey for an unused large table to cause issue with query running for diffrent database. SQL Server memory is dynamic in nature if suppose large portion of memory is occupied by datapages of DB1 Lazy writer and checkpoint pages will work together to age out pages which are not used recently or ...


2

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it. In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that ...


2

Keep it. A foreign key exists primarily to ensure that the value given in the child table really does exist in the parent (foreign) table. Cascading deletes or updates are, generally, a distant second. That's the general case; your specific business rules may be different.


2

I have an old post Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios. In that post, I do not use a foreign key constraint in my example. Soft deletes are way faster. You just have to integrate JOINs for tombstone tables or AND deleted=0 in WHERE clauses. In light of these things, you probably could live without the ...


2

This is SQL Server updating the statistics on a table. It can be triggered automatically when about 20% of the data in your table changes, or on demand if you run jobs that update statistics. You have a few ways to lessen the pain. You can enable asynchronous statistics updates, which lets SQL Server update stats behind the scenes without blocking other ...


2

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression. Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table. Since you have a limited number of questions ...


2

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?". There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully ...


1

You should keep an eye on things like your buffer sizes, table cache, query cache and innodb_buffer_pool_size, key_buffer_size as all of these memory allocations can have an affect on query performance which can cause MySQL to eat up CPU. Enable slow query log and see if there are queries getting logged there. Observe "mysqladmin status" output for ...


1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc. What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the ...


1

When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column. Does anyone know what this command is? It is related to Full text catalog. You can find out more info using sys.dm_fts_outstanding_batches e.g. from Pro Full-Text Search 2008 book, Below will give you the Number of Full-Text Index Population Batches ...


1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at the transaction level or at the session level while NOLOCK is a query hint. You mentioned that you fully understand dirty reads, so using at the transaction level is what I would recommend. if you want dirty reads on some tables only, then NOLOCK hint will help you. SQL server 2005 and up allows you ...


1

So in this case it does seem to have been the 8 tempDB files causing the biggest issue. I ran the analysis suggested here http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ and found no PAGELATCH issues and a very high proportion of PAGEIOLATCH waits (I don't remember exactly, but ...


1

Identical subqueries are normally only performed once. However, the way you write a query isn't neccessarily the actual execution order, so there are no guarantees. To make sure, view your estimated execution plan with one or both of the subqueries, and compare them. In your example, you could place the subquery in a JOIN instead, which would make it both ...


1

Given that you can not afford to drop and recreate the table, this related answer would be a better fit: Best way to populate a new column in a large table? You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable). And all the general advice for performance optimization applies. There is not much more ...


1

The order in which tables are joined isn't defined in the query (unless you use a join hint, which I wouldn't recommend), so you can't be sure that A is joined to B before B is joined to C. To answer your question, in most cases SQL Server can tell from the statistics on the table (or other criteria) that the query won't return any rows. In that case, ...


1

I have three suggestions SUGGESTION #1 : Rewrite the query You should rewrite the query as follows SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND ) GROUP BY http ORDER BY count; or SELECT * FROM ( SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( ...


1

Let's do a hypothetical answer to a hypothetical question (all of this are ball-park calculations, I do not intend to be very exact, but to have a big picture): Your table, at the bare minimum, will have a storage requirements of: 50 000 000 000*(8 + 8 + 1 + 1 + 20*3) / 1024^4 ~ 4TB I do not have into account the TEXT because, even if it can be inline, ...


1

Yes, all the basic optimizations in MySQL 5.5 apply to MariaDB 5.5. However, starting with MariaDB 10, a real fork, not all improvements in 5.6 are in MariaDB codebase, and alternatively, MariaDB has some exclusive features (Hash JOINS). For now, those are not too different, that may change in the future. For a book, where most of the optimizations are ...


1

What finally solved my problem was turning archive mode off, perform the truncation, then turn archive mode back on. Whith archive mode on, the truncation toook to long and corrupted the database.


1

The whole point about profiling is to ascertain where your system is spending time. This can often give developers some clues as to how to optimise their queries (by adding indexes for example). The 600 pound gorilla in this area is Oracle's Wait Interface (OWI) which is one of the most sophisticated monitoring and profiling tools available to database ...


1

Here you have a video on the basic inner structure and inner workings of indexes. I recommend you to watch it all. Basically, indexes are ordered structures on disk (although they can be cached, and they normally will for better performance) that will allow certain operations to be done faster. In particular, in MySQL, B-tree/B+tree (the most common ones) ...


1

Your entities are based on hours, not days, so you should go with one row per hour, just like you did, not a full day in a row. I did not understand if when you say data between two timestamps, if timestamp refers to the "inserted" column, or if it refers to the date and times on the data file. If it is the latter one, then having a datetime column for your ...



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