Tag Info

New answers tagged

0

To get all unique pairs of elements from an array of arbitrary length: WITH a(a) AS (SELECT '{A,B,C,D}'::text[]) -- provide array here , i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i) SELECT ARRAY[a[i1.i], a[i2.i]] AS pair FROM i i1 JOIN i i2 ON i2 > i1 , a; You can then join to the message table. Without knowing any ...


1

Query You UPDATE statement looks good, mostly. I re-formatted and made minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN duplicate_product_sets d ON d.invtid = ...


0

Cassandra with a composite key seems to do what you want: http://planetcassandra.org/blog/composite-keys-in-apache-cassandra/


1

You'll have to write your own, it's not a native feature of oracle, it's something that has been implemented in Oracle tools. Grab the plan from v$sql_plan for your SQL_ID and then grab the delta of session stats before and after you run your query. It's a little more involved in that, but that's the basic mechanics.


1

There is an art and a science to database design. The science is normalisation. If you're intent on learning about databases you will need a solid understanding of it. The art is in deciding just what exactly the "things" are that are going into your database. Defining them in a why that is comprehensive and precise is a skill to learn. In your ...


0

Starting at the end - if you have, say, 20 large tables of 10 millions rows each at 200 bytes per row that works out at just under 40GB. Add twice as much for indexes and your entire database will fit into memory. With sensibile indexes in place you really shouldn't worry too much about joining multiple tables. Joins are what relational databases do, it's ...


0

Upgrade to 5.6 and use multi-threaded replication. But... This is limited to different threads for different databases. Use Galera -- it does not have the tight coupling between threads and databases, so it does (in theory) a better job of parallel execution. Read this for more thoughts. Also, let's see some of the more naughty queries, plus associated ...


0

Roland's ideas are good. Also Turn off the query_cache; it is unused overhead. (_type=0, _size=0) Batch size of 10 is probably good. How big are the BLOBs/TEXTs? How much data is there? Sounds like terabytes? To simply read and write terabytes takes hours -- just for the disk I/O. (So 40 hours may be reasonable.) comment out log-bin (no use writing a ...


1

InnoDB Architecture (from Percona CTO Vadim Tkachenko) What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)? Increase innodb_log_buffer_size to 256M Increase innodb_log_file_size to 2G I wrote about increasing these to accommodate TEXT/BLOB fields in the past Apr 20, 2011 : MySQL ...


1

Q1. Is storing computed bitorderstring are good way or maybe let user wait a little? =) (I guess, result set will be much more than I tested) MySQL supports columns of type bit. These only use one bit each, in sets of 8, as you would expect. This will give the same disk and memory density as you're currently getting, but with much easier query ...


1

WHERE (1 << CVbits) & ConcVal AND CVbits != 0 -- (to "exclude the zero bit")


1

I have a simpler way to put it. "Batched INSERTs and LOAD DATA run 10 times as fast as single-row INSERTs." By "batching", I mean INSERT INTO t (a,b) VALUES (1,2), (2,3), .... The optimal number is between 100 and 1000 rows per INSERT. Beyond that, you get into diminishing returns. Here are some issues that impact performance, especially for Batched ...


2

QUERY #1 Each time you do an INSERT, you are doing this under the hood SET @sql = 'insert t select null'; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times. QUERY #2 Running insert t select null from(, you fully ...


2

After asking on pgsql-performance list, Jeff Janes figured out that the cause was associated to the default collation used by Postgres (see this link for more informations). MacMini was using the much performing collation while Dell T420 was using the en/US collation. T420 (Postgres 9.4.1) List of databases Name | ...


7

Out of the box, no. You'd need to hook into the event stream for extended events and then take action based on that. Tom Stringer has a good overview and sample code to do this!


1

Views are a good way to accomplish what you want to do. If your views take advantage of existing indexes or are 1:1 against the underlying tables, then queries against them will use the indexes. Since you're expecting periodic updates, you'll want to script the view creation, and in that same deployment script you can always add indexes if you need more. ...


0

1GB requires LONGTEXT or LONGBLOB. But that datatype may give you headaches. I suggest you experiment with one row with 1GB of data in a column declared LONGTEXT CHARACTER SET ascii. If it is just ASCII, then LONGTEXT makes sense. LONGBLOB would work very similarly. You will probably need to set max_allowed_packet. I have seen a few systems with it set ...


1

I have used a hybrid approach with success. Tables contain BOTH an auto-increment primary key integer id column AND a guid column. The guid can be used as needed to globally uniquely identify the row and id can be used for queries, sorting and human identification of the row.


0

Running EXPLAIN QUERY PLAN on your query gives this: 0|0|0|SCAN TABLE LEGS AS l 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|5|SEARCH TABLE TRAVELDAYS AS d USING AUTOMATIC PARTIAL COVERING INDEX (Day=? AND Value=?) 1|1|4|SEARCH TABLE TRAINS AS t USING AUTOMATIC COVERING INDEX (TrainDaysUID=?) 1|2|0|SEARCH TABLE LEGS AS l USING AUTOMATIC COVERING INDEX (TrainUID=?) ...


1

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options: Update to MySQL 5.6 and use a FULLTEXT index Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB. Convert to a MyISAM table, or create a spare MyISAM table that you can ...


0

It is not a great practice to mix the app and database on the same server. You're competing for resources. You also mentioned remote execution versus local execution on the same disk. I'd contend that you could suffer from disk contention if it was all on the same disk and the overhead of the remote replication is questionable by comparison. A lot of factors ...


0

Many things can be done to speed things up... Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude. (This is one of many things to shrink the record size.) Do not INDEX boolean values like clocked_in and public_post. (The optimizer is unlikely to ever use the index. And it is costly to update.) Don't split DATE and TIME; use DATETIME instead of ...


1

It depends a little on what storage system you have behind the scenes. You see, read and write IO operations are very different. On a RAID 5 to perform a single block write you must: Read the update block. Read the parity block. Write the new block. Write the new parity block. So for a single random write, RAID 5 needs 4 operations per write. This is ...


5

There are two types of activity to consider here: reads and writes. As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected. Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when ...


1

(More of a bunch of comments, plus a redundant answer.) Your two examples are not identical -- one is limited to 2015; the other is not. WHERE birthday BETWEEN '2015-04-01' AND '2015-04-01' + INTERVAL 1 MONTH would be able to use INDEX(birthday), but that only covers those who will be born next month. Even if you had a mnth TINYINT UNSIGNED COMMENT ...


2

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type. Using MONTH(birthday) allows MySQL to grab the ...


1

I go for SELECT * FROM customers WHERE MONTH(birthday) = 4; since its the common way to select by month


0

This answer has nothing to do with mysql database versions or types, I wanted to know if update statements were making changes AND to do this in my php code.. Created a dummy table with one record and one field which I would query to get the value of mysql's current_timestamp. To the data table being updated, added a timestamp field and used the mysql ...


2

You have a odd system. Your database size is 350+ G and you have 32 bit system I would say this is a system which I would never like to have in my environment. Its very difficult to manage 350 G database on 32 bit SQL Server which has VAS limit(by default) of 2 G. You are bound to face memory pressure going ahead. AWE in 32 bit system only allows SQL ...


1

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.) More than half the users will have only one link. Some users will each have over 100K links. More than half the sites will have only one page. Some sites will each have over 100K pages. What does it mean? It means that any form of indexing, compression, etc, needs ...


1

In almost every use case, InnoDB is preferred over MyISAM. So, yes. To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased. In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it ...


3

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time: CREATE TABLE measurements ( id bigint IDENTITY, parameter_id int NOT NULL, measuretime datetime NOT NULL, value float NOT ...


1

I think the optimiser is right. When you use INCLUDE, it only stores the included column values on the leaf level of the index, they do not make up the key. So what it is suggesting is that it can decide which branches of the index to scan (measuretime is the key, so it leaves a huge chunk of records out), which means the WHERE doesn't need to test each row. ...


3

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration. The ranking processes could be offloaded onto a read-only copy maintained via ...



Top 50 recent answers are included