Choosing between MyISAM & InnoDB - two servers with related tables

"MyISAM is faster than InnoDB" -- This is an old wives tale.  It used to be somewhat correct; now it is somewhat incorrect.

SELECTing during UPDATEing -- MyISAM is usually fast enough so this is not an issue, but InnoDB will usually not block at all.  Go with InnoDB.

Power failure -- InnoDB automatically recovers.

You are talking about two "servers" -- Do you mean two different computers?  And two differen instances of mysqld?  There is nothing to have them talk to each other.  (OK there are Replication and Federation, but it did not sound like you are into those.)

If you really meant "database" or "table" on a single MySQL instance, then so state.  Tables (even in different databases) can be JOINed for most operations.


How to download Oracle Database 11g release 2 for Mac OS

Oracle 11g R2 Database Release 2 only support Windows, Linux, and Solaris. So if you want to using Oracle 11g R2 on a Mac OS you will need to using something like Virtual Box or VMware Fusion. You can read more about installing Oracle 11g R2 on these operating system here.


How to SELECT all records where there is not another record with the same key within a given time offset?

maybe you can try something like :

select ds1.*
from SampleData ds1
left join SampleData ds2 on ds1.id = ds2.id
    and ds1.timestamp < ds2.timestamp
    and ds1.timestamp > dateadd(hh, -5, ds2.timestamp)
where ds2.id is null


Java driver dont fire query-start probe in postgres 11

I presume the difference between you running your queries via psql vs. from a Java program is that you use prepared statements in Java. Processing "simple statements" and prepared statements take different code paths in the Postgres backend (postgres.c).

"query-start" probe is only called when a simple statement is executed (exec_simple_query), while a prepared statement processing has two separate distinct stages: parse (exec_parse_message) and execute (exec_execute_message).

Both code paths converge when they call PortalRun in pquery.c, where the "query-execute" probe is called.

In other words, the two code paths look like this (omitting the "done" probes):

psql -> simple statement
  query-start probe
  query-parse-start probe
  query-parse-done probe
  query-rewrite-start probe
  query-rewrite-done probe
  query-plan-start probe
  query-plan-done probe
  query-execute-start probe
  query-execute-done probe
  query-done probe

JDBC -> prepared statement
  prepare phase
    query-parse-start probe
    query-parse-done probe
  execute phase
    query-execute-start probe
    query-execute-done probe

If you're not particularly concerned about the query parsing and planning latency, you need to capture the "query-execute-*" probes. If you need the query text, capture also "query-parse-start".


Count() versus keeping a column with a total count

You can also consider materialized views (not all RDMS support this feature though)


Your main problem here would be maintaining what backup was relevant to what, and you would have to continue taking full backups on your primary server in order to reset the differential base and prevent you diff backups from becoming as large as the fulls.

Consider it this way:

Full backup taken on primary (once)
Differential taken every night
Full copy_only backup taken on the secondary every Saturday
Log backups taken on secondary every 30 minutes

In this situation you are never clearing your differential base, this means that the differential backup will get larger and larger over time (you can only run copy_only backups of databases from secondary replicas). Eventually the number of pages changed in the database will reach a sufficiently high number that the differential is as large as the full backup. Not an ideal situation.

I would either go with running full backups nightly, off of your secondary, or take once a week (or more often as needed) full backups off your primary, and use differentials on the other days.


While MySQL is in-memory database (tables you are querying should be in memory to get quite nice performance) so you have to have enough of RAM to hold accounts table in buffer pool (innodb_buffer_pool_size). Can you calculate the size of your table (query information_schema.tables for this information)? For example, if it's 10G and you can get 10G at least (it would be nice to have at least +20-30%) so you can go with virtual server. If you cant afford 10G RAM (for example you need 700G RAM) you should think about sharding (moving data to different servers by accountId).

It's a good option to use partition by accountId it will reduce amount of blocking inserts/updates.

MySQL 5.5/5.6 has a nice feature to load/dump buffer_pool to you do need to do warm up after each server restart (look at innodb_buffer_pool_load_at_startup, innodb_buffer_pool_dump_at_shutdown).

That is where I would start. General advice: change settings in my.cnf and benchmark them.

Hope it helps


SQL Server 2016 Always On with Availability Groups: add instance on both servers

Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM.
Currently I have one instance on each, they serve 4 Availability Groups with a total of ~300 GB data in 95 databases.
I have the task of migrating more databases (35 databases, 235 GB data) into this environment [...]

Considering there is a single socket with 6 cores and will hold 130 databases, that's already a good portion of available threads just for Always On usage and not counting anything else.

had it not been for the fact that these databases come from a cluster with another collation. Hence, I'm thinking of adding an instance on each server and creating a separate Availability Group for this collation.

Is it a requirement that the instance level collation be the same as the database collation? This is unclear.
Adding another instance is only going to make troubleshooting the inevitable issues you're going to have, that much harder as now you'll need to monitor both instances to see how one is impacting the other while the other is having issues.
I, personally, would not do this.

I found a similar question from a few years back but since there was no evident reason for the multiple instances the TS received a lot of whys, donts and whats. I my case I really need to have an extra instance due to the collation issue, my question to the Community is: is there anyone who could tell me "Yes" or "No"? And if either answer - could you please give me a good motivation?

You're throwing aside whatever has already been discussed and want an absolutist answer to an environment no one but yourself has access which is quite a hard question.
Instead of adding another instance, I'd add another set of nodes to the cluster or create a new cluster so that items were separate. It's still not clear if the requirement is instance level collation to match database level collation, so we're not even sure another instance is required.

If I can't add the instances I would have to go with another server, and due to the license costs of Enterprise Edition this would then be a non-clustered solution - reducing uptime, complicating patch routines, removing redundancy and so on.

Licensing aside, a stand alone instance would be simplified patching - using an AG complicates patching.
I'm afraid it's more or less down to two things:

Multiple Instance (if even required) are annoying beyond belief in terms of maintenance and troubleshooting. If you want to do this, then you need to be okay with knowing the investments of time and energy to troubleshoot small issues as BOTH instances would need to be investigated, mostly regardless of which was having the issue since one can easily affect the other.
The limited hardware, just 6 cores. Even if you added the databases to the current instance, the hardware just doesn't seem adequate. Since we aren't in your environment we don't know the current level of load on both servers, their current specs, and what this would do when migrated so it's really not possible for anyone to say. Having said that, it still doesn't make me feel good, considering there just isn't that much hardware available.


Handling or preventing conflicts in a multi-user system

What you are looking for is called Optimistic Concurrency Control.

Add an int column to your table called version. Start at 0.

create table mytable (
  id bigserial primary key,
  whatever text,
  version int not null default 0
);

Add a row:

insert into mytable (whatever) values ('something');

Read a row:

select id, whatever, version from mytable where id=1

Results:

1, 'something', 0

Update a row:

update mytable set whatever = 'something new', version = version + 1 where id = 1 and version = 0

If that row was not found, throw an exception. If you have a REST API on top of your database, you should use HTTP 409 Conflict.

If you use an ORM like Hibernate, you can just add a field with a @Version annotation and it will handle this for you.


Try

SELECT DISTINCT dtp.ci,
            dtp.nombre_apellido,
            dtp.telefono,
            dtp.direccion,
            dtp.correo,
            dte.codigo
FROM   default_tb_persona dtp
   LEFT JOIN default_tb_envio dte
          ON dtp.ci = dte.id_persona_envia;

Max.


How to return a set of rows from this function?

Your query returns two columns (one integer, one float), but the function is defined as returning a table with a single varchar column.

You need to change your function definition to:

RETURNS table(maturity integer, avg_implied numeric)

to match the query result.

returns setof and returns table are the same thing but returns table lets you define names for each column in the result.

You also don't need PL/pgSQL for this, a simple SQL function will do just fine:

CREATE OR REPLACE FUNCTION generate_term_structure_by_moneyness(arg_ticker text
                                  ,arg_date integer
                                  ,arg_underlying float
                                  ,arg_lower float
                                  ,arg_higher  float)
  RETURNS table(maturity integer, avg_implied numeric)
$BODY$
    select maturity,avg(Implied)
    from options.options
    where ticker=arg_ticker
      and date=arg_date
      and strike/arg_underlying > arg_lower
      and strike/arg_underlying < arg_higher
    group by maturity
    order by maturity asc;
$BODY$
LANGUAGE sql;

You probably also want to re-think the usage of the approximate data type double:
http://floating-point-gui.de/

date is a horrible name for a column. For one because it is also a keyword, but more importantly it doesn't document the column's content. Is that a "start date"? An "end date"? A "due date"? A "registration date"? A "change date"? A "creation date"? And even more so if you don't actually store a date in that column but an integer value. What kind of date is the value 42 supposed to be?


You will need to have the Full Recovery Model enabled on all four databases.

Now you will issue a full backup on all four databases, let's say ten minutes before the point in time you require.

Now you will do a transaction log backup of each database, at a point in time immediately after the exact moment you need.

On the other end, you will restore each full backup WITH NORECOVERY, and then transaction log backup, with the RESTORE LOG WITH STOPAT switch to specify the exact moment in time you need, as a DATETIME.

Remember to RESTORE WITH RECOVERY at the end to bring them into active use.


Remote connection scheme without Windows Authentication/Domain in detail

how safe is this remote SQL Server access scheme

The shared account credentials embedded in the application can be read by anyone who controls a computer on which the application runs.  No matter how you try to hide the credentials, you can never hide from a user with a debugger, who can just set a break point on the function that handles the password and examine the memory.

And from the description, it sounds like having access to the shared user credentials would, at the very least, allow a user to perform actions as some other user.  So unless you really trust all these users, this scheme isn't very good.

  When a user is trying to login to the system, he actually sends his id and password to the application
  ...
  whether there is an alternative or better to say a best practice on this problem.

Create a SQL Server Login or Contained Database User for each application user, instead just storing the users in a table, and use the credentials presented by the user to authenticate to SQL Server.


Add linked server from SQL Server 11.0.5058 to SQL Server 10.50.4000

As your administrator I'd like to confirm that the problem has been caused by firewall restriction. SQL port is opened so you can complete linked server setup.

Cheers, mate


How to list all the inherited tables from the parent table?

Try this:

SELECT
table_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE table_schema = 'database_name'
AND referenced_table_name = 'user';

This will list of referenced tables under table 'user'.


In PostgreSQL you have to create a trigger function that will perform the calculations and the use it in a trigger.

Example:

Create or replace function CalculQStockProduit() returns trigger as $$
begin;
 update QuantiteEnStock set NEW.QuantiteEnStock = NEW.QuantiteEnStock-NEW.Quantite;
return NEW;
end;
$$ language 'plpgsql';

Create trigger trgCalculQStockProduit after update on Produit
for each row execute procedure calc();

I would refer to trigger function in postgresql manual for better understanding

This is just a sample so you will have to work out the calculations needed.

PG Trigger functions

Choose according to your postgres version.

I have used plpgsql as language but there are various options. plpgsql, sql, plv8 etc etc


I've recently had the occasion to use something like this:

CREATE FUNCTION gzip(text) RETURNS bytea
    LANGUAGE plperlu IMMUTABLE STRICT COST 100000 PARALLEL SAFE
    AS $_X$
    use Compress::Zlib;
    my $x = Compress::Zlib::memGzip($_[0]);
    return encode_bytea($x);
$_X$;

But note that bytea is itself transmitted in an escaped form which will enlarge the size.  With any luck, JDBC implementation might unescape them on the fly as they are read into memory, and not store the entire result set in the raw form.  Sorry, I don't use JDBC enough to know.

Can't you just use a cursor to process one row at a time and then discard it, rather than reading all rows into memory?


I think you have a very strange row estimation here (2064 vs 1)
->  Seq Scan on pg_class c  (cost=0.00..96893.03 rows=2064 width=76) (actual time=0.073..386.865 rows=1 loops=1)
                                                              Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND (((relname)::information_schema.sql_identifier)::text = 'some_instances'::text))
                                                              Rows Removed by Filter: 962568
                                                              Buffers: shared hit=69844

I think maybe this is affecting you PG 12 release notes:

Treat object-name columns in the information_schema views as being of type name, not varchar (Tom Lane)

Per the SQL standard, object-name columns in the information_schema views are declared as being of domain type sql_identifier. In PostgreSQL, the underlying catalog columns are really of type name. This change makes sql_identifier be a domain over name, rather than varchar as before. This eliminates a semantic mismatch in comparison and sorting behavior, which can greatly improve the performance of queries on information_schema views that restrict an object-name column. Note however that inequality restrictions, for example

SELECT ... FROM information_schema.tables WHERE table_name < 'foo';
will now use calculation“C”-locale comparison semantics by default, rather than the database's default collation as before. Sorting on these columns will also follow “C” ordering rules. The previous behavior (and inefficiency) can be enforced by adding a COLLATE "default" clause.

Edit, in pg 12 I get for your example query:
->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..8.30 rows=1 width=76) (actual time=0.018..0.018 rows=0 loops=1)
                                                         Index Cond: (relname = 'some_instances'::name)
                                                         Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                                                   ->  Seq Scan on pg_namespace nc  (cost=0.00..1.09 rows=1 width=68) (never executed)
                                                         Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))


Since MYISAM is a filebased Databasesystem that stores Tables in files, it might be possible to just copy the folder of your database into your data folder of your MySQL installation.

BUT if you are using INNODB or something else for any of your tables then it might run into problems.

Easiest way to check that would be to just set up a mysql installation on any other machine (for the sake of systemsanity you can just set up an XAMPP or LAMMP and use that for short term) and copy your files into the data folder and check if all tables you know of are there and accessible.

Once that is done you can acces that seperate system to do an export via a backup Tool of your choice (mysqldump is also possible) and then input the backup you just made into the production server.

Next step would be to set up a nice backup for your new production machine (at least once or twice a day depending on the load of data that will be put into your machine) so that dataloss is limited.

If you want to be really shure and have verry frequent backups without killing your production system you can set up a replication system where the production server works as a master and replicates all data to the slave which will be the backup server. On the slave you then do the backup in a way you are comfortable (using mysqldump or some more sophisticated tool). In case the production server crashes again, you have your backups AND the slave server that could be easily swapped and work as a fast response short term replacement.


Mysql : what are the steps for creating a database schema for a big project with many tables?

These are the steps I take:

Start with a piece of paper, write down the entities and their relations to each other.  Write the relations in plain language, e.g. Customer places zero to many Orders.
Once you have an idea of how many entities you will have and their rough relations, formalize that drawing into a hierarchy diagram - this is usually just the entities and their relations to each other, not any column or key information.
Starting at the top of your hierarchy, identify the properties of each entity and identify potential primary keys (candidate keys). There may be more than one per entity.  Do not create/assign "id" or other columns at this point unless they are being created by the calling application (InvoiceNumber,etc) and/or generally accepted practice for things like Person,Address.  "Codes" provided by outside authorities (ISO, others) are not only acceptable but preferred - CountryCd,CurrencyCd, etc.
Again starting at the top of your hierarchy, choose a primary key from your list of candidate keys and migrate that primary key through all other related entities.  Identify and list all required alternate keys.
Create your preliminary ER diagram.  I would use IDEF1X as it is easy to read and commonly accepted in the real world.  If you are unfamiliar with IDEF1X, please read this primer https://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf
Once you have your draft diagram complete, you can identify instances where a surrogate ("Id") may be appropriate.  My rule of thumb for determining when a surrogate is appropriate is a) subtypes that group separate entities b) very wide keys where no element is required for referential integrity later c) the data needs to be obscured for security/privacy reasons.  Once you choose a surrogate, it becomes the primary key and your existing primary key must become an alternate key.  That last sentence is key.  If you use a surrogate and do not have an alternate key, you will not have a relational database.
If there are rules which must be enforced through transactions or check constraints, a brief description of the rule can be assigned and labeled to the appropriate entities.
You now have a logical model which can be used to build the physical model.  The physical model will include data types and any instances where you might implement things differently than the logical model (different tables for optional columns, etc.).
Now you can start writing your DDL.

I would also recommend that when you are designing your database that instead of using an "Id" column as a surrogate in every instance, you find if there is an appropriate/accepted shorthand that could be used instead.  So if each Order has a Status of "Received/In Process/Shipped/Canceled", when you create the reference table perhaps use a StatusCode like "R/P/S/C" instead of meaningless numbers "1/2/3/4".  Not only is CHAR(1) less storage than a typical INT, any queries/code written against the database are much easier to understand and you're less likely to commit the sin of using the wrong digit for the wrong column.


How to export mysql databases with different ENGINE?

You should create two mysqldumps

Schema Only, No Data
Data Only, No Schema

Using sed, mysqldump all databases except information_schema, mysql, performance_schema

Create a script with the following commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DB_CSV=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
DBLIST=`echo "${DB_CSV}" | sed 's/,/ /g'`
#
# Schema Only, No Data
#
mysqldump ${MYSQL_CONN} -d -B ${DBLIST}|sed 's/ENGINE=MyISAM/ENGINE=NDBCLUSTER' > schema_only.sql
#
# Data Only, No Schema
#
mysqldump ${MYSQL_CONN} -t -B ${DBLIST} > data_only.sql

Run this script. You should have two files

schema_only.sql

Should have all the CREATE TABLE commands
Should have no data
All ENGINE=MyISAM will now say ENGINE=NDBCLUSTER

data_only.sql : All the INSERTs to reload the tables

Run the schema_only.sql` script in the MySQL NDB Cluster setup.

Check to make sure every table was created.

Make sure there are now special options prevent the table creation, such as

FULLTEXT indexes
ROW_FORMAT option

If something went wrong, you can always hand edit schema_only.sql and reload it.

Once every table is in place, run data_only.sql in the MySQL NDB Cluster.

Give it a Try !!!


Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence

Your question seems to be logically inconsistent, as tolerating a latency in persistence and "Actually I can afford to lose transactions that are in memory only" by definition violates the "D" in "ACID".

If you really want to be just "ACI" compliant, you can turn off "synchronous_commit".

Note that this means you could report to the traders that their trade executed, but then later find out that oops, it actually didn't.  It will still be atomic and consistent, as both sides of the trade and both changes in cash balances will have disappeared together.  But this could still be a bad customer experience.

If you decide you can't run with "synchronous_commit" off, you should at least do the test with it turned off.  The outcome of the test will indicate how you should proceed for further optimization.


What tools are there for data masking? (MySQL, Linux)

I would be very surprised if there was a generic tool for this - how would it "know" what is sensitive data and what wasn't? For example it would need to examine all your data and recognize all possible formats of credit card number, phone number, postcode, email address, and whatever other data is considered sensitive. It would also need to be smart about your schema - e.g. should it rewrite all customer email addresses to "nobody@company.com" - or does any part of your database, applications, other tools assume that a customer's email address (or SSN or whatever) is unique? Or do you have some part of the application that checksums credit card numbers, that would break if you reset them all to 0000 0000 0000 0000? Or does your telephony system assume that a customer's dialing code corresponds to the country in their address?

Basically, configuring any tool to do it will be as much or more work than just writing your own script, using your knowledge of the application. At my site, we simply made it policy that anyone who adds a column with such data in updates the script to anonymize it at the same time, after an initial audit to find all those columns and write version 1.


Why is my DB size increasing when rebuildling an index using sort in tempDB in SQL Server?

I have quoted three sections from different articles and gave the links at the bottom. Bottom line is you need space for the new index to get build plus transaction log space for rollback. If you cannot accommodate the space required you might want to consider INDEX_REORGANIZE.

When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.

Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy. The pages and extents required to do this will always be allocated as needed, as with any other operation in SQL Server.

Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. For more information, see Transaction Log Disk Space for Index Operations.

Reference:

Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking) by Paul Randal
Index Operations That Require Additional Disk Space
Transaction Log Disk Space for Index Operations


Filtering subquery results with group by

Your query is non-deterministic as it abuses GROUP BY.

It may give incorrect/unexpected results in any version of MySQL, depending on the execution plan and available indexes. You may not have seen such behaviour because it hasn't happened yet (you were just lucky or the indexes and size of the table have up to now lead to plans that give correct results) or it happened but you didn't notice.

There are versions (MariaDB 5.3+) where similar queries - not sure about this one but similar - will almost always give incorrect results due to some optimizations that remove the redundant ORDER BY in the subquery.

There are versions of MySQL (5.7) where you'll get an error if you try the  query - exactly because it might give inconsistent results. In that version, you'll have to change the default settings (and get the wrong non-deterministic behaviour) or you'll have to rewrite/correct the query.

In short, don't use it.

I would write the query like this. Much simpler, deterministic and probably more efficient as there is no GROUP BY and doesn't join the pi table twice:

SELECT
         pi.pub_date,
         pi.x,
         ( SELECT   pps.page_size
           FROM     print_page_size AS pps
           WHERE    pps.effective_date < pi.pub_date
           ORDER BY pps.effective_date DESC
           LIMIT    1
         ) AS page_size
FROM
         print_issues AS pi
ORDER BY
         pub_date ;              -- or whatever

An index on print_page_size (effective_date, pagesize) would be further help.


Protocol used by Oracle RMAN to communicate with auxiliary

The thing you are looking for is called the SBT API. I can't find any free documentation on it, but the process is that a vendor of say a tape library implements the functions of SBT (e.g. sbtopen(), sbtwrite() and so on), compiles them as a shared library (a .so file) and this is placed in the ORACLE_HOME. When RMAN starts it looks for a file called libobk.so and dynamically links to it. RMAN then calls the functions, and in turn the protocol between the MML (media management layer) and the actual hardware e.g. a tape library, is proprietary.


SQL Server Integration Services 2012 - Legacy deployment

No*, it won't.

The Package Deployment Model, if deployed to the database, will store individual packages in msdb.dbo.syspackages90 (SQL Server 2005) and msdb.dbo.sysssispackages (SQL Server 2008+)

The Project Deployment Model, deploys to the SSISDB. There it takes the form of Folders/Projects/Packages which are stored in

SSISDB.catalog.folders
SSISDB.catalog.projects
SSISDB.catalog.packages

*With the 2016 release of SQL Server, we now have incremental package deployment for SSIS projects using the project deployment model. Based on an early release candidate at least, this means you can keep your SSIS projects in the package deployment model yet deploy to the SSISDB to take advantage of the improved management, logging, configuration and execution there.


No. What you seem to want is not a Backup of the database but some sort of extraction or export. A SQL Server backup performs the extraction of the whole contents of the database, in an internal format, so the database is restorable as a whole in a coherent point in time.

If you want what is usually known as export of the database, and it seems you need a partial export (just exporting a subset of the records). As you suspect, there is no way to do this without:
1/ well knowing the structure of data inside the database, and the relationships and constraings on tables, columns, ...
2/ running queries against the tables in order to extract just the rows you want.
3/ knowing which format do you want the data in.

Even if you get to extract just the data you want, it would be not easy to use this data as a backup, because restoring this data into the original database in the event of a data loss or Corruption would not be an easy task...

If you just want to save some space, you can replace the full backups by differential ones. you would need to save the first full backup in order to be able to restore with a differential one, but the sizes of the generated files would be much smaller.

hth


When is it ok to use an Index Hint on a query in production code?

It's OK to do this only when a better solution doesn't exist.

As David Browne - Microsoft commented:

  If you were writing in another language you wouldn't think twice about telling the computer not just what to do by how to do it.  That's all an optimizer hint is.


If I understand you correctly below anonymous block should do it.
DO $$
declare

tabs cursor for
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
  AND table_name NOT LIKE '%suffix$'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY table_name;
rec record;

query_statement text;
BEGIN

open tabs;
loop
fetch tabs into rec;

exit when not found;

    FOR i IN 0..20 LOOP

    query_statement:= 'CREATE MATERIALIZED VIEW IF NOT EXISTS "'||rec.table_name||'_factor'||i||'" AS SELECT id, POWER(2,'||i||') AS factor FROM "'||rec.table_name||'" WHERE factor IS NOT NULL;';
    RAISE NOTICE '%' , query_statement;
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
end loop;
END; $$


Postgres query plan of a function invocation written in plpgsql

I addition to @rfusca's advice: SQL statements inside plpgsql functions are considered nested statements and you need to set the additional Parameter auto_explain.log_nested_statements.
Unlike some other extensions, you don't have to run CREATE EXTENSION for this one. Just load it dynamically into your session with LOAD. You must be  superuser for this.
Your session could look like this:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
SET auto_explain.log_nested_statements = ON; -- statements inside functions
-- SET auto_explain.log_analyze = ON; -- get actual times, too
SELECT * FROM get_paginated_search_results(...);

May produce a lot of log output.
The current manual on auto_explain.
Depesz wrote a blog article about it when it was introduced with PostgreSQL 8.4.


Should I believe Query Store runtime stats?

Pathology!

This could happen to queries in ways that wouldn't impact the plans:

Heaps: Forwarded Fetches
Indexes: Internal fragmentation

Additionally some selects that cause writes may trigger additional reads if there are stats updates, spills, spools, etc. Though none of those show in your plan, someone else may some along later and find this tidbit useful.

Heaps

DROP TABLE IF EXISTS dbo.el_heapo;
CREATE TABLE dbo.el_heapo ( col1 BIGINT, col2 VARCHAR(8000) DEFAULT 'A' );

INSERT dbo.el_heapo ( col1 )
SELECT TOP 1000000
       x.rn
FROM   (   SELECT     ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 / 0 )) AS rn
           FROM       sys.messages AS m
           CROSS JOIN sys.messages AS m2 ) AS x;

Run a simple query:

SELECT COUNT_BIG(*)
FROM   dbo.el_heapo;

Table 'el_heapo'. Scan count 1, logical reads 2718
CPU time = 62 ms,  elapsed time = 62 ms.

Look at the table: No forwarded fetches.

SELECT OBJECT_NAME(ddips.object_id) AS TableName,
       ddips.index_type_desc,
       ddips.forwarded_record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(),
                                      OBJECT_ID('dbo.el_heapo'),
                                      0,
                                      NULL,
                                      'DETAILED') AS ddips;

Cause some forwarded fetches:

UPDATE dbo.el_heapo
SET    col2 = REPLICATE('Z', 1000)
WHERE  col1 % 3 = 0;

If you run the count and forwarded fetch query again, you should see:

~330k forwarded fetches

Table 'el_heapo'. Scan count 1, logical reads 380564
 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 360 ms.

But the execution plan will be the same.

Indexes

Create a dummy table do I don't mess up my real Users table:

SELECT *
INTO dbo.TempUsers
FROM StackOverflow2013.dbo.Users AS u;

CREATE UNIQUE CLUSTERED INDEX c ON dbo.TempUsers(Id);

Check on the table size:

SELECT   TOP 2147483647
         s.name AS schema_name,
         OBJECT_NAME(ps.object_id) AS table_name,
         i.name AS index_name,
         ps.row_count,
         ps.in_row_used_page_count,
         (ps.reserved_page_count * 8. / 1024.) AS reserved_MB,
         (ps.lob_reserved_page_count * 8. / 1024.) AS reserved_LOB_MB,
         (ps.row_overflow_reserved_page_count * 8. / 1024.) AS reserved_row_overflow_MB
FROM     sys.dm_db_partition_stats AS ps
JOIN     sys.objects AS so
    ON  ps.object_id = so.object_id
    AND so.is_ms_shipped = 0
    AND so.type <> 'TF'
JOIN     sys.schemas AS s
    ON s.schema_id = so.schema_id
JOIN sys.indexes AS i
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
ORDER BY ps.object_id, ps.index_id, ps.partition_number

Should be 44,450 pages and 348MB.

Simple query:

SELECT COUNT(*)
FROM dbo.TempUsers AS tu

Table 'TempUsers'. Scan count 1, logical reads 44439
   CPU time = 219 ms,  elapsed time = 229 ms.

Update the DisplayName column to be the max length (nvarchar 40):

UPDATE t
SET t.DisplayName = t.DisplayName + REPLICATE('A', 40 - (DATALENGTH(t.DisplayName) / 2))
FROM dbo.TempUsers AS t

Now the table is much larger:

88,392 pages, 691MB.

Query results:

Table 'TempUsers'. Scan count 1, logical reads 88381
   CPU time = 250 ms,  elapsed time = 245 ms.


What does: ERROR: tuple offset out of range: 0 mean?

It was all caused by the bug described and fixed in this release: http://www.postgresql.org/about/news/1506/

It caused us a lot of trouble!


Pitfalls of creating a foreign key constraint on a system table?

I did not find an elegant way to solve this question but adding a check constraint on the "Owner" column of the metadata tables accomplished the objective of ensuring that only schema owners would be listed.  If a new schema owner is added then all the check constraints would have to be changed but that is not too onerous.

I tip my hat to Phil for confirming that backing away slowly from system tables is the best approach and wish he had posted this as an answer.


Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

Multiple problems. This should work:

CREATE OR REPLACE FUNCTION insert_data_func()
  RETURNS TRIGGER AS
$func$
BEGIN  -- !
   EXECUTE
      $x$INSERT INTO tb_moldsummary AS t
         SELECT $1.machine                               -- !
              , $1.model
              , split_part(lot, '_', 1)
              , right(lot, position('_' IN lot) * -1)    -- ! simpler
              , COUNT(lot)
              , $1.datetimestamp
         FROM   model$x$ || to_char(now(), 'YYYYMM') || '
         WHERE  lot = $1.lot
         AND    machine = $1.machine
         GROUP  BY machine, model, lot
         ON     CONFLICT ON CONSTRAINT tb_summary_unique
         DO     UPDATE
         SET    machine = $1.machine                     -- !
              , totalshots = t.totalshots + 1
              , datetimestamp = $1.datetimestamp'
   USING NEW;                                            -- !

   RETURN NEW; -- can be RETURN NULL for AFTER trigger
END
$func$  LANGUAGE plpgsql;

I marked lines with important fixes.

Major points

Pass NEW to EXECUTE with the USING clause (as instructed in the answers to the question you referenced):

Insert RECORD into a table in a trigger function?

Dollar-quoting helps to simplify the syntax for string literals containing single quotes.
Replace

SUBSTRING(NEW.lot FROM (POSITION('_' in NEW.lot)+1) FOR LENGTH(NEW.lot))

with this simpler and faster equivalent expression:

right(NEW.lot, position('_' IN NEW.lot) * -1)


Search for first and lastname without order

There are two main components to the question: the searching of the name column and how the data is delivered to the user. As predictive typing is more related to programming, caching, etc. and as Aaron mentioned is not a good idea to do on each keystroke, I will not cover it.

As for how best to query the database:

The logical choice here is Full Text Search, because that provides the exact features you're looking for:

  Full-text queries perform linguistic searches against text data in
  full-text indexes by operating on words and phrases based on rules of
  a particular language such as English or Japanese. Full-text queries
  can include simple words and phrases or multiple forms of a word or
  phrase. A full-text query returns any documents that contain at least
  one match (also known as a hit). A match occurs when a target document
  contains all the terms specified in the full-text query, and meets any
  other search conditions, such as the distance between the matching
  terms.

However, the OP has updated the question to say that the database belongs to another company. So that leaves two main options:

Using a complex setup of the LIKE operator
Creating a localized version of the database and implementing the Full Text Search features.

Option 1

  For example the person I search for is called John Williams: It should
  work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'

To achieve this, you'd need to split the user's input by spaces (or some other desired delimiter) and dynamically build your WHERE clause based off that in a format like

WHERE ... LIKE '%' @string1 + '%' OR ... LIKE '%' + string2 + '%' OR ...

While possible, this is far from ideal as the LIKE operator is not very efficient when it comes to comparing strings, even if there are only 200,000 rows. It will not scale well and I wouldn't trust it for this use case in a production system:

  In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Aside from this, you'll have to accommodate users who have special characters (language-specific characters, punctuation), extra spaces, abbreviations, etc. in their input.

Not the "right" choice by any means, but you can probably hack it together if you must.

Option 2

Use replication, mirroring, etc. to get a local copy of the user data and build a Full Text Index on it to leverage all of the features, like a thesaurus, language support, and limiting search results based on rank of relevance which would greatly help your predictive text typing if you still decide to pursue it.

While requiring more up front effort, this is the "right" way to do this.


How to change the AES key of a specific column in mysql?

Once you have encrypted the data using an AES key the data is encrypted (the point of using AES). The only way to change the key would be to decrypt the data with the old AES key and then re-encrypt the data using a new ASE key.

Some good MySQL AES documentation.


Insert Statement Take too long to insert - MYSQL

The table is MyISAM

You should really switch to InnoDB.

If something taking 14.7 (or more) seconds touches that table, then writes can occur until it finishes.  The "something" could be mysqldump, a complex SELECT, etc, etc.

InnoDB avoids most such lengthy hangs.


Invalid length parameter passed to the LEFT or SUBSTRING function when inserting data into a table that has computed columns

This is a similar issue to the one discussed here, but I think it deserves its own question/solution as in my case the issue was not the use of substring in a query, but in a table that's being inserted to. Though, the reason apparently is the same ... as Aaron Bertrand stated in his answer to another question:

  ... because you can't always rely on SQL Server filtering rows before attempting calculations.

While that's understandable in a case where the filter and the computation happen in the same query, it is not so obvious that the same appears to happen when inserting data into a table that has computed columns. Apparently, the persisted columns are computed before being inserted into the table, therefore (I guess depending on which plan the optimizer chose) also computing values of rows that due to the filter condition are not being inserted into the table. Changing the table definition making sure the length parameter cannot become negative solved the problem.

CREATE TABLE #KeyValuePairs (
  [PK]       uniqueidentifier NOT NULL,
  [KeyValue] varchar(500) NOT NULL,
  [Key] AS CONVERT(varchar(35), SUBSTRING([KeyValue],1,NULLIF(CHARINDEX('=',[KeyValue]),0)-1)) PERSISTED NOT NULL,
  [Value] AS SUBSTRING([KeyValue],CHARINDEX('=',[KeyValue]) + 1, LEN([KeyValue]) - NULLIF(CHARINDEX('=',[KeyValue]),0)) PERSISTED NULL


Wiki answer collecting partial answers originally left in question comments

That's more than enough, you don't need a server.

60 million rows isn't that big nowadays - unless you've got huge numbers of blobs. Stick with your desktop unless you start to have major performance issues.

PostgreSQL runs better on *nix than on Windows and the number of drives is a big factor too. Nothing beats a ton of SSDs in RAID 10, but you'll be fine with your current setup!

If you want help improving your queries, you should post a question with the query and query plan from explain (analyze). See postgresql-performance for more details on which information is required for questions regarding slow queries.


Try something like

SELECT @cnt:=CASE WHEN c.id=@id THEN @cnt+1 ELSE 1 END count_t,
       @id:=c.id id,
       so.trans_id trans_id
FROM (SELECT @id:=0, @cnt:=0) init_vars,
     customer c LEFT JOIN sales_order so ON ...
ORDER BY c.id, so.trans_id;

fiddle


Cannot find the Order Entry folder in $ORACLE_HOME/demo/schema/

That is 12.1 documentation. You have a 12.2 database.

Installing Sample Schemas

  Starting with Oracle Database 12c Release 2, the latest version of the
  sample schema scripts are available on GitHub at
  https://github.com/oracle/db-sample-schemas/releases/latest.

  During a complete installation of Oracle Database, the HR schema can
  be installed either manually or automatically when creating a database
  using the dbca option. All the other sample schemas must be installed
  manually via the scripts available on GitHub.

You can download the scripts from the above github URL.


How can I handle a huge amount of logging generated from frequent table rebuilds?

one option (if feasible for you considering the downside) would be to change the database to bulk logging and bcp into the target table from a view reading the source tables.  the actual logging should be minimized, but the log backup will still be huge as it will include the changed extents.  a downside of bulk logging is that is compromises the ability to point in time recover to a time frame during the bulk logging operation.  make sure you take a log backup immediately after the bulk import


How to manage a large SQL Server database: transaction log, reduce size, alter table

Points, in no particular order:

You do need the transaction log for a whole bunch of reasons I'm not going to go into. That said, if you're running out of transaction log space while you're in simple recovery mode, that probably means you have long-running transactions. Make your transactions as short as possible (gather all required user input before you start a transaction). (When SQL Server is trying to free log space, it can only free up to the start of the oldest current transaction.)
Making NVARCHAR(MAX) columns nullable will not necessarily save you much space (off the top of my head it'll be 2 bytes per row, per column).
Deleting/archiving old data will help you - with a caveat that it has to pass through the transaction log first.
Shrinking your database will not help you, as a matter of fact it will kill performance (both while it's running and afterward, since DBCC SHRINKDATABASE uses a naive algorithm that almost perfectly fragments your indexes) and won't regain you much if any space.


CREATE TABLESPACE could be used.
create tablespace dedicated_datastore owner postgres location '/my/mount/point/'

and now the tablespace can be used as parameter when creating a database, (or a table, or an index)
create database my_database tablespace dedicated_datastore


How to find queries that timed out in MongoDB?

On a pretty large dataset I tested a query that times out after 10 seconds:

db.collection.find({"foo": "bar"}).maxTimeMS(10000)

(In my case the collection has > 100 GB of data and no index on the field foo). I searched for the query using:

db.system.profile.find({"query.query" : { "foo" : "bar" }}).limit(5).sort({ts: -1}).pretty()

and found that the fields exception and exceptionCode are filled:

"exception" : "operation exceeded time limit",
"exceptionCode" : 50,

So searching for the field exception should give you the desired result:

db.system.profile.find({ "exception" : { "$exists" : 1 } }).limit(5).sort({ts: -1}).pretty()

And if you explicitly look for queries that timed out, try:

db.system.profile.find( {"exceptionCode": 50} ).sort( {ts: -1} ).pretty()


Can someone explain the magic of Opendatasource/Openrowset?

Check Windows Event Viewer.  Look in the application logs, security logs, and system logs.  If you've nailed down all of the obvious stuff (and it sounds like you have), then it could be anything.  The file's folder could have Windows domain security on it, and your machine might not be able to authenticate against the DC, for example.


Estimate query speed for +1.5 billion rows mysql table

(UNHEXing is not a significant part of the problem.)

The real problem is the randomness of hashes.  The leads to jumping around lots of places on disk.  Let's dissect the query.

The IN list is a list of values scattered throughout the INDEX(hash).
Each value is looked up by drilling down a BTree (found in .MYI file) that is cached in MyISAM's key_buffer.  What is the value of key_buffer_size?  What is the result of `SHOW TABLE STATUS LIKE 'fingerprints' \G ?
If the index_size is greater than key_buffer_size, then many of the lookups will hit the disk.
At the leaf node of each BTree lookup will be a 5-byte (I think) "record number".
Now to lookup the row -- This will be a random disk access (a seek, no BTree) into the fingerprints.MYD at offset = 17 * record_number.  (The records appear to be FIXED length of 17 bytes.)
Again, we are looking at a probable disk hit -- now assuming that the remaining free space on disk is less than the Data_length (see the TABLE STATUS).

What to do?

Case 1:  Data_length + Index_length < RAM size:  Have key_buffer_size a little bigger than Index_length.  Gradually both caches will fill with index and data, and the I/O will go away.

Case 2:  That sum is slightly bigger than RAM:  Pick one of the caches to make big enough.

Case 3:  The sum is a lot bigger than RAM:  You are stuck with lots of I/O until you get more RAM.

I suspect the Data_length and Index_length are about equal.  I would split available RAM in half -- half for key_buffer_size, the rest for data caching.

Here are 2 more ideas:

Rather than fetching the ints in a second step, have KEY(hash, int1, int2)  This means that only the BTree lookup is needed; the data will sitting in the leaf node.  With this approach, you could set key_buffer_size to 'most' of available RAM.  That SELECT won't touch the data, only the index.
Switch to InnoDB.  It's blocks are 16KB, not 1KB.  This may make things faster.  But the disk footprint will be 2-3 times as much.  Again, use the 3-column index, but shrink key_buffer_size to 20M and raise innodb_buffer_pool_size to 70% of RAM.

Other Notes:

"Sending data" does not tell you anything.  Profiling, in general, is useless.
SSDs run a lot faster than HDDs.
You appear to be I/O bound.
Whether you are I/O-bound or not, the total query time is roughly proportional to the number of hashes being looked up.  (This can be deduced from my dissection.)
MEMORY is not likely to be significantly faster or slower than MyISAM.  And if your data needs to persist, then there is a hassle because MEMORY is volatile.
I predict that compression is useless since you have only 6 bytes to compress.  (And the hash, itself, is probably not compressible.)
If your provider is limiting the IOPs, that is a problem.  If your index is fully cached (and not so big that it is unnecessarily eating RAM), then the IOPs are fetches of data blocks.  The 3-byte key would be about 70% larger; will a large enough key_buffer fit in RAM?  If so, that approach might be optimal.


If you open Report Server Configuration Manager, you should be able to see the edition on the first screen that pops up as indicated below.


Select rows based on a minimum in one column

Use DISTINCT ON:

SELECT DISTINCT ON (gid)
       id, gid, distance, time_interval
FROM   dis
WHERE  time > 0  --  filtered just to take positive values (orig. Q)
ORDER  BY gid, time_interval; -- optionally more expressions to break ties

Details:

Select first row in each GROUP BY group?


How to create a DB Link to a Database which has failover in TNS

The referenced MOS just states the following:

Dblinks do not support TAF. This refers to FAILOVER_MODE parameter, not FAILOVER. The first parameter is about TAF, the second is about connect-time failover.
It is not recommended to use the SCAN address for dblinks in case of RAC. Instead of the SCAN address, use the VIP addresses.

Removing FAILOVER parameter will not change anything, the default parameter for it is ON.

FAILOVER

You do not need to remove connect-time failover parameters.


Is PostgreSQL JSONB @> operator equal to ->''=?

I have created a small test for checking this:

CREATE TABLE jsonb_test (id serial, data jsonb);

Then inserted some generated data:

INSERT INTO jsonb_test (data)
SELECT row_to_json(x.*)::jsonb
  FROM (SELECT i AS bla,
               ARRAY[i, i+1, i+2] AS foo,
               ARRAY[('v' || i::text, i), ('v' || (i * 10)::text, i)] AS bar
          FROM generate_series(1,100000) t(i)
       ) x;

Let's find something in there (version 1):

SELECT * FROM jsonb_test WHERE data @> '{"bla": 545}'::jsonb;

The execution plan looks like

 Seq Scan on jsonb_test  (cost=0.00..7604.89 rows=369 width=36) (actual time=0.425..47.600 rows=1 loops=1)
   Filter: (data @> '{"bla": 545}'::jsonb)
   Rows Removed by Filter: 99999
   Buffers: shared hit=2997 dirtied=676
 Planning time: 0.078 ms
 Execution time: 47.692 ms

If I do the same with the other version (version 2) you propose (with the operator changed from -> to ->> as I think you meant that):

SELECT * FROM jsonb_test WHERE data ->> 'bla' = '545';

I get a plan like

 Seq Scan on jsonb_test  (cost=0.00..8526.47 rows=1843 width=36) (actual time=0.613..34.657 rows=1 loops=1)
   Filter: ((data ->> 'bla'::text) = '545'::text)
   Rows Removed by Filter: 99999
   Buffers: shared hit=2997
 Planning time: 0.116 ms
 Execution time: 34.727 ms

Running them repeatedly, I see no significant difference between the execution times.  It is no wonder - the expensive operation here is the sequential scan, while the two different filter conditions should't be very different in this regard.

Now I add a GIN index on data:

CREATE INDEX ON jsonb_test USING gin (data);

When executing the queries now, version 2 does the same as above, while version 1 has a new plan:

 Bitmap Heap Scan on jsonb_test  (cost=28.77..372.05 rows=100 width=36) (actual time=0.081..0.083 rows=1 loops=1)
   Recheck Cond: (data @> '{"bla": 545}'::jsonb)
   Rows Removed by Index Recheck: 2
   Heap Blocks: exact=1
   Buffers: shared hit=10
   ->  Bitmap Index Scan on jsonb_test_data_idx  (cost=0.00..28.75 rows=100 width=0) (actual time=0.062..0.062 rows=3 loops=1)
         Index Cond: (data @> '{"bla": 545}'::jsonb)
         Buffers: shared hit=9
 Planning time: 0.084 ms
 Execution time: 0.122 ms

This already shows that the two versions are not fully eqivalent - a GIN index on the jsonb column supports only one of them (version 1).

One can define an other index to help version 2:

CREATE INDEX ON jsonb_test ((data->>'bla'));

Now version 2 gives the following:

 Bitmap Heap Scan on jsonb_test  (cost=12.17..1323.49 rows=500 width=36) (actual time=0.065..0.070 rows=1 loops=1)
   Recheck Cond: ((data ->> 'bla'::text) = '545'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=2
   ->  Bitmap Index Scan on jsonb_test_expr_idx  (cost=0.00..12.04 rows=500 width=0) (actual time=0.046..0.046 rows=1 loops=1)
         Index Cond: ((data ->> 'bla'::text) = '545'::text)
         Buffers: shared read=2
 Planning time: 0.480 ms
 Execution time: 0.133 ms

In this experiment, I don't see a huge performance difference between the two, given proper indexing.  However, you should consider the following:

  Insertion into a GIN index can be slow due to the likelihood of many keys being inserted for each item. So, for bulk insertions into a table it is advisable to drop the GIN index and recreate it after finishing bulk insertion.

Usually it is said that BTree indexes are cheaper to maintain (meaning data changes on the table have a smaller overhead) that GIN indexes.  From reading around, this is not always the case - you have to test these solutions on your own data set.  For example, I cannot tell off the top of my head if a set of very complex jsonb values will prefer this or that.

Notes:

to understand the similarities and differences of the json(b) operators, the official documentation is an excellent source.
it might make sense reading Bruce Momjian's presentation about indexing techniques.


Is there a way to optimize this using indexes?

Generally indexes will stop working when you run "top" select over a nested select, like:

(select
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C

OR

   (SELECT
        count(masterID) as cnt, yearID, teamID, lgID
   FROM
        batting
   GROUP BY yearID , teamID , lgID) D

You define derived tables C (as well as D etc.). Also your derived tables have GROUP and FUNCTION inside.

When you make the comparison:

WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

it runs over new "tables" and these new tables do not have indexes.


designing user friendly relational databases

I have absolutely no idea what a user friendly database could possibly look like. Your apps are like the dashboard and controls of an automobile. This is where you might hear the expression user friendly. The database is like the engine and/or transmission. They have important functions to perform and here is where you would expect to hear expressions like reliable.

Your top concern when designing a database is data integrity. That is what gives it the aspect of reliability. Without that, the most user friendly interface in the world would be useless.

There is, however, one section of a database that we might concern ourselves with user friendliness, if by user we mean app developer. That is the api or the interface to the database. The app developer doesn't really care about relationships and really shouldn't be concerned with the physical layout of the database. Sure, in their code one entity may relate to another, but the physical implementation of that relationship in the code may be (and generally is) very different from the physical implementation of the relationship in the database. No computer language has "foreign key" as one of its features.

What the app developer needs are:

Reliable data
Fast retrieval and storage of the data
Data in a format easiest to handle within their coding framework

The first need is delivered by designing the database with data integrity foremost in mind.

The second need is delivered by a good combination of software (efficient queries and stored procedures) and hardware. Very rarely will efficiency require a change in the design of the data, such as denormalization.

The third need can be delivered by a variety of methods which can vary according to the particulars of DBMS and front-end platform but chief among them are a library of stored procedures (heavily overridden to supply an interface for data in a wide range of forms) and views (exposing the data for direct access also in a wide range of forms).

One rule that seems to apply over all three of these needs is this: do not allow developers direct access to the tables. Find out what data they need and in what form they need it, then supply the stored procedures and/or views that will give it to them. An actual api written in the app language would be a great addition. Then the developer doesn't have to think of the database at all, just the api.

This rule helps in several ways but by far the most important is that you will not need to change the design of the database to satisfy the needs of the application developers. Database designers who find themselves in the situation where a database design change is forced by developer needs almost inevitably do so by sacrificing data integrity. When you find yourself going down that road it is all down hill -- and not in a good way!

With a wall of abstraction between the data and the application, when the database needs to change because of changes to entities or relationships, those changes can be made in a way that maintains the all important reliability and then the stored procedures and views changed to maintain the expected interface with the now-changed data. When the application adds or modifies functionality, this can change what data it needs or the form it needs it in, but this does not require a change in database design to fulfill those changing requirements.

Life is good.

In your particular situation, you have Writers, Orders and Payments. This seems like a fairly straight-forward design which you have made a pretty good start. With just a few tweaks (the Payments table doesn't need a writer field -- that is contained in the Order), you should have it nailed down in short order.

Then answers to such questions as "whether the payment has been made for each order done by the writer" can be supplied by stored procedure or view.

If you get to that point and run into trouble, ask another question.


That is an option of the "flat file connection" editor. Its on "Advanced->TextQualified". Here is an image of it:


ssms does not let me change to server authentication mode and I can not log in

Check this pinal dave solutions - https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/


Entity-relationship diagrams and application program functionalities

You touch several subjects, therefore it is important to establish their scopes and the links that exist among them.
Firstly, if the specific business domain you are working with implies (a) multiple one-to-many associations along with just one associative entity type —or many-to-many [M:N] association—, and (b) you are representing said characteristics with the demanded precision at both the conceptual and logical levels of abstraction of the database of interest then (c) there is nothing special about such a situation, you are doing just fine.
Conceptual schema: capturing the informational requirements of a business
A properly delimited conceptual schema —I am not sure, but it seems that that is what you mean by “project scope statements”— implies to the identification and definition of the relevant business rules that describe the interconnections (1) between entity types, (2) between entity types and their own properties and (3) between the properties themselves, which may come about in quite different cardinality ratios —neither only one-to-many [1:N], nor exclusively many-to-many [M:N]— and may or may not be represented in an entity-relationship diagram (ERD for brevity).
Regarding the initial title of the question, an associative entity type —which is different from an entity, i.e., an instance or occurrence of an entity type— can only be missing if the database modeler failed to (i) identify it and consequently (ii) did not reflect it in the pertinent conceptual schema and perhaps in an ERD.
ERDs: graphical representations of conceptual schemas
An ERD depicting the associations and the corresponding cardinality ratios of a certain scenario is not supposed to solve “the problem of digitizing lots data”, but serves the purpose of capturing and exposing graphically the conceptual definitions of the business in question. This sort of diagram is a communication instrument that can be really powerful when used appropriately.
If you want to see examples of the delineation of the conceptual schemas of databases with associations of very distinct cardinality ratios (and the subsequent logical-level representations), visit, e.g., my answers to the questions entitled

“Modeling a database for a video game production business context with multiple many-to-many relationships”, and
“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”.

Levels of abstraction: application program components, ERD symbols and relational/SQL instruments
Since you mention “tables”, I assume that you have the intention of building a relational database, and said type of database must be independent from the application programs (apps, for brevity) that share access to it.
Naturally, the number of conceptual and logical elements of a database has repercussions on the number of kinds of items that the apps have to (a) receive from an end-user terminal or another system, (b) apply some computational processing and (c) send to the database.  But the quantity of apps components (e.g., object-oriented classes) does not necessarily have to mirror (1) the quantity of conceptual-level entity types of a database nor (2) the quantity of base tables of the corresponding logical layout (e.g., the fields of a single object-oriented class may encompass the columns of two or more base or derived tables).
On the other hand, an ERD is not composed of tables but of graphical constructs portraying entity types and relationships (I prefer the words associations, connections or links, for reasons I will detail below). Said entity types and relationships are, in turn, usually represented by base tables (i.e., base relations), and enforced via constraints declared on columns with the respective data types or domains if possible (i.e., relation attributes), if they are managed by virtue of a relational database running on a relational database management system —which is the typical case, but the conceptual-level elements could be handled by, e.g., an obsolete network or hierarchical database—.
In this manner, it is very important to distinguish between (i) a conceptual-level relationship and (ii) a logical-level relation which is the mathematical construct —proposed by Dr. E. F. Codd in his relational model—
utilized for administering data in a relational database. In this regard, you may find of help this series of posts.
It is as well opportune to differentiate between base and derivable data structures; the former commonly represented by base tables (i.e., those declared by dint of CREATE TABLE … ( … ); statements), and the latter by derived tables (i.e., those expressed by way of SELECT operations that, e.g., “combine” columns FROM distinct base tables or other derived tables via JOINs, sometimes fixed as VIEWs), when operating on a relational database. Since the app components have to do directly with the way in which the information of interest is displayed to end-users, they belong in the external level of abstraction when it comes to the ANSI/SPARC architecture, hence they should have a direct correspondence with the aforementioned views.
Application programs and data management: separation of concerns
In turn, the functionalities of the apps involve carrying out processes (“behaviour” using object-oriented programming terminology) and should be analyzed and defined by techniques (e.g., algorithm development) that are distinct from the ones used in the creation of a database, because database modeling and app design are two different (although related) disciplines.
The structural and functional richness of a whole computerized information system (i.e., a database and one or more apps sharing it) depends entirely on the skills possessed by the designer(s) with respect to the successful (i.e., accurate) representation of the informational (regarding the database) and processing or computational (concerning the apps) requirements. This is in agreement with the software development principle known as separation of concerns.


Wordpress query on MyISAM tables in MariaDB on ZFS takes too long to execute

Short answer

ZFS, etc, are not the main problems.  (See Comments for a rebuttal on ZFS.)

Use InnoDB, not MyISAM.

Fix the indexes in postmeta, as discussed here:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Avoid index prefixing.  (The above link discusses that, too.)

(If you must use MyISAM, then the indexes recommended there are not adequate.  Let me know if you need more help.)
Long Answer
Several dimensions to this question:

Version -- MariaDB 10.3 versus other versions of MySQL/MariaDB.  This is probably not significant.

OS -- Linux vs FreeBSD vs Windows -- The OS is just a middleman; all are quite mature and have mostly caught up with each other.

HDD vs SDD -- SDD drives are inherently faster for any access.  Plus they are faster for random accesses due to the lack of a physical "seek".

FileSystem -- ZFS versus many others.  "Journaling" and other advanced techniques will have some impact on the effective performance.

RAID, and especially "battery-backed write cache" has some impact on reads and possibly a big impact on writes.  This applies across all disk operations in all applications.

InnoDB vs MyISAM -- These differ significantly in locking, caching, the organization of data.  Indexes differ a lot.  In MyISAM, fetching a block of data uses the FS's blocksize and caches it in the OS.  A MyISAM index block is 1KB, which is likely to be smaller than the FS block slide, thereby leading perhaps to an extra level of caching.  For InnoDB, all blocks are 16KB.  The interactions between these MariaDB block size and the FS block is hard to predict.  Peformance at many levels depends on whether the data or index needs to be scanned linearly or probed randomly.

The INDEXes -- Above, I focused on this topic because it is where the main improvement can be had.  In the situation here (accessing WP's postmeta), will have a significant and far-reaching performance impact.

Impact of INDEX

My recommendations include having this composite index: (post_id, meta_key) which is optimal for your slow query.  Furthermore...
When using a secondary or primary index, MyISAM reaches into the data file with a random seek and fetches a "block", hence performance is dependent on FS and HDD/SSD.
With InnoDB, the PRIMARY KEY is "clustered" with the data, thereby eliminating the extra random fetch.  Some of my recommendations take advantage of this efficiency.
With InnoDB, a secondary index requires a second lookup via the PK.
So, in general, it is beneficial to lay out the data in some clustered manner to improve performance.  Changing the PK to start with post_id, helps in this respect.  Furthermore, InnoDB avoids the random seek that MyISAM requires.
"Index prefixing" (eg, "meta_key(191)") is virtually useless.  So, part of the performance enhance involves any of about 5 techniques to eliminate prefixing.  I'm pretty sure MariaDB 10.2.2  has already fixed the issue; this would let you remove the (191) from any indexes having it.  Second best is if you can assure that all keys will always be under 191 characters, then changing the definition of meta_key is a simple answer.
All of the above add up to decreasing the 6GB of disk activity, probably significantly.  Sorry, but I cannot say specifically if ZFS has any impact.

The specific Query

Scan the entire posts table.  There is no getting around this.  This will be a linear scan through the "data".  The engines do this task quite differently, but neither engine has a strong advantage here.
For each post row, reach into postmeta.  This is where the engine and indexes make a significant difference.
Dedup the list of meta_key values -- This may be done in RAM or on disk, at the whim of the Optimizer.  You could change a setting to influence that decision, but it would have the side effect of potentially impacting the performance of all other queries, so I won't discuss it.


How to set minimal token length for to_tsvector() function?

Tokens are initially produced by the full text search parser bound to the text configuration. The default text search parser that ships with PostgreSQL is not configurable (it can be replaced by a custom parser, though).

After being output by the parser, tokens can be filtered out with dictionaries. It's relatively easy to create a dictionary that filters out short words, but it takes the form of a couple functions written in the C language.
As an example, here's a blog post explaining how to write a custom dictionary to filter out long words: Text search: a custom dictionary to avoid long words.

If there are only a reasonable number of short words that you want to filter out, another option would be to enumerate them all in a stop-words file and use the built-in simple dictionary. From the documentation:

  The simple dictionary template operates by converting the input token
  to lower case and checking it against a file of stop words. If it is
  found in the file then an empty array is returned, causing the token
  to be discarded. If not, the lower-cased form of the word is returned
  as the normalized lexeme


How to restore MySQL databases from database files?

This is what I do to start the mysql daemon manually.

I've also included my own my.cnf. By specifying the correct corresponding paths in your system, you should be able to do the same thing. I would also point out that this is a source install, but again, it should apply generally.

Start the daemon like this:

  ./bin/mysqld --defaults-file=./my.cnf

And start the client with:

  ./bin/mysql -S ./mysql.sock -u root -pdba

Both mysqld and mysql client are launched from the basedir. This can be scripted easily of course.

This is my my.cnf - it's a bit of a mess, but hopefully it should help. I stress that this is not a production server!

[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.pid
socket      = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64
datadir     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/logfile.txt

#log_error   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
log-error   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log

slow_query_log_file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.sock
host            = 127.0.0.1
user        = pol
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1 -- ignore this, it's a setting for abrowse, an external programme.

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
#err-log        = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#pid-file   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
#log_error  = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#log-error  = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log


sqlldr running slow after indexes created

Since no other sessions are accessing your table now, the lock shows as NOT BLOCKING. As soon as an other session tries to manage that table, it suddenly feels it is BLOCKED. The index is used so you can not manage it as long as other sessions are using it.

Having indexes on havily loaded tables is for sure like putting a brake on the loader process. You could help yourself by checking out the docs. See Conventional and Direct Path Loads

Maybe partitioning can help you. Than you can prepare a single table, load that, finish it's indexing and perform partition exchange. Just don't create global indexes on such a table.


How to re-write this query or restructure my table for SPEED

For a relatively simple query like that there isn't a lot you can do to optimise it as there is little room for change (there isn't a simple way of asking for that data).
You can probably reduce the amount of pages being touched as it runs significantly by having an index on datetime and service. This way the data it needs to group by will already be available in what it has read to perform the filter on the date. This will increase the amount of data on disk as the index will be larger, and slow down writes a touch for the same reason. You would probably want to replace the existing index on datetime with the new composite index instead of just adding it, for those reasons.
A little more detail on why this will be faster:
With the index just on the datetime, it will need to read the base table data pages to get the service column for each matching row which as well as being extra page reads just because of referencing the other structure it will possibly be many more page reads because those pages contain less rows each due to the larger data per row (they include the URL column and any other properties that you add, or might already have, for the services).
If that still isn't fast enough...
You may need to look at some form of caching for the counts. There are several options here:

a materialised view may work if they are intelligent enough in mysql
a little denormalising, by including a counts table updated by trigger when the main table is updated (this is essentially a more "manual" version of a materialised view)
something in the application layer, if you can live with the result not necessarily being 100% up-to-date every time (the fastest option by far if done right, but obviously with that key disadvantage)
if the data is big enough that the query is reading from storage rather than RAM each time and you don't want to use one/more of the three options above: throw hardware at the problem and buy oodles of RAM! (this is usually not a good solution, though sometimes can be)


Create row level trigger for Partitioned table

I don't know about Aurora, but on recent PostgreSQL your trigger will work just fine if you define it to fire AFTER rather than BEFORE the DML operation. That should work just as well.

Triggers on partitioned tables were introduced in v11 - is upgrading an option?


"Login failed for user" when using admin user in SSMS

Pretty sure the problem is in the username. When you connect to an Azure SQL Database instance, foo@bar implies that you're connecting as the login "foo" to the instance "bar.database.windows.net".

The credentials you use when logging into the Azure management portal won't work when you're using SSMS.

Verify that you've set up a SQL Database login, and use that login. The admin account is set up in the Azure portal - you can verify the login name by clicking "Properties" on the server instance.

Regular logins/users are set up as 1) logins on the database server, or 2) as contained users in each database (if you have enabled containment), or 3) in Active Directory (if you've configured one).


Display one row over another if there are two

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
  Employees.ID,
  Employees.FName,
  Employees.LName,
  Address.City,
  Address.State,
  Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1


I've found this article to be quite useful. Assuming you know how PostgreSQL HBA generally works (i.e., how pg_hba.conf and postgresql.conf work), you do the following:

start up certbot and have letsencrypt generate the cert where it
typically does create a script to let letsencrypt copy renewed
certs and keys to $PGDATA make sure postgresql.conf and
pg_hba.conf are configured properly restart Postgres. done


Two databases of same size, backups sizes are very different

As noted in comments, the tables do not have clustered indexes therefore you can not logically defragment them (rebuild indexes) completely. That is, the indexes are defragged but not the actual data.

Also, DELETEs from heaps (a table without a clustered index) do not deallocate space. See http://msdn.microsoft.com/en-us/library/ms189245(v=sql.105).aspx for more

Finally, not having clustered indexes is generally a bad thing except for staging tables that are truncated. However, GUIDs are not good clustering keys

Solutions?

Add clustered indexes for a long term fix (subject to regular index maintenance)
Run DBCC SHRINKFILE with NOTRUNCATE to compact data. YMMV of course


Adding a check constraint on a new column with default value on a large table

It doesn't seem to be that smart, no, from my trying it in psql with \timing on. Increasing the size of the table causes ALTER TABLE to get slower, and it seems linear, so I assume it's still doing a scan. You can verify this pretty easily interactively:
testdb=# \timing on
Timing is on.
testdb=# create table target(a bigint);
CREATE TABLE
Time: 4.176 ms
testdb=# insert into target select x from generate_series(1, 15000000) x;
INSERT 0 15000000
Time: 8661.110 ms (00:08.661)
testdb=# begin; alter table target add column y bigint default 0 check(y>=0); rollback;
BEGIN
Time: 0.128 ms
ALTER TABLE
Time: 1130.251 ms (00:01.130)
ROLLBACK
Time: 0.106 ms
testdb=# insert into target select x from generate_series(1, 15000000) x;
INSERT 0 15000000
Time: 8948.767 ms (00:08.949)
testdb=# begin; alter table target add column y bigint default 0 check(y>=0); rollback;
BEGIN
Time: 0.139 ms
ALTER TABLE
Time: 1955.840 ms (00:01.956)
ROLLBACK
Time: 0.129 ms
testdb=# insert into target select x from generate_series(1, 15000000) x;
INSERT 0 15000000
Time: 8836.126 ms (00:08.836)
testdb=# begin; alter table target add column y bigint default 0 check(y>=0); rollback;
BEGIN
Time: 0.114 ms
ALTER TABLE
Time: 2751.619 ms (00:02.752)
ROLLBACK
Time: 1.010 ms

I think your planned approach is fine.


SQL Server 2012 Replication, how to ignore action with specific trigger?

I think I solve it successfully in the easiest way:

CREATE TRIGGER [dbo].[LotController]
   ON  [dbo].[Store]
AFTER DELETE
AS
BEGIN
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debug]') AND type in (N'U'))
   BEGIN
       SET NOCOUNT ON;
       INSERT INTO [Debug](data,value)
       VALUES ('Time',GetDate())
   END --Table not exists
END --Trigger


have_partitioning variable is removed from MySQL version is 5.6.29. That's why it is not showing.Instead of it you can use show plugins for knowing partition is enabled or not.If status value is Active then it is enabled.You can also disable partition by adding skip-partition=True while starting mysql server.


SQL Server Audit Changes in user permission

That is exactly what SQL Auditing is for :-)  Now you can finally know WHO did what with a user's access and many more objects in the server or at the database level.  However, Auditing does need to be setup first before it can record the results.

Specifically speaking of Database level audits:
"The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification. Audit events are the atomic actions that can be audited by the SQL Server engine. Audit action groups are predefined groups of actions. Both are at the SQL Server database scope. These actions are sent to the audit, which records them in the target."

You can read more here https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15


(I don't have an SSRS server handy at the moment to test/confirm this, so I'm going a bit off-the-cuff here, and might not be 100% spot-on)

I've never had luck with using the ReportManager URLs when I want to pass in specific parameters. Try using the ReportServer URLs instead.

BOL gives this example format for passing dates using the ReportServer URLs:
http://myserver/ReportServer/Pages/ReportViewer.aspx?%2fProduct_and_Sales_Report_AdventureWorks&SellStartDate=7/1/2005


Adding 4th node in MySQL group replication stuck at RECOVERING

Finally, I found the issue and fixed it.
The details are provided below:

1) The issue was happening because of the GTIDs executed in the current set of group replication nodes and the binlog configurations. Be default, the binlog expiration days in the current MySQL config was set to 0 and it means, it can store lot of binlog files which are basically used for row transactions and group replication.
I purged the old binlog files and set the binlog expiration days to '3'. The commands used for that purpose.

mysql> PURGE BINARY LOGS TO 'binlog.000105';
mysql> SET GLOBAL expire_logs_days = 3;

2) After running the above commands on the MASTER server, I deleted all the old binlog files and now the replication should take long time. However, after purging the old binlog files, the MASTER cannot set the other nodes as the node connections to MASTER events were being purged which were in the older binlog files. Hence, we need to do some manual tasks.

3) Take the mysqldump of all databases from the current MASTER and restore it on the 4th mysql node you want to add.

# mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=mysql_port --user=username --password=password > dump.sql
# mysql -u root -ppassword < dump.sql

4) Once the restore gets completed, make sure that replication configs are already done in mysql configurations of all the nodes and then restart the node.

5) Restart replication on the 4th node and then replication should gets complete in few minutes or hours (Depends on the database size)

mysql> START GROUP_REPLICATION

6) Once the 4th node shows ONLINE, the node is part of the group replication cluster and can be used for other purposes.


Optimization problem: compound clustered keys, flag conditions and index-merge

This pains me...

I've had to use temp tables with InnoDB before. Load them with filters, create an index, join these temp table.

The problem as I reckon is if that InnoDB only has Nested Join algorithm: the grown-up RDBMS query optimisers have more to use. This is based on trying to run Data Warehouse type loads on InnoDB.

Temp tables drags the overall complexity down the MySQL query optimiser's level...


On second thoughts I'm thinking it'd be a bit redundant anyway as the database connection credentials will need to be stored on disk for the rails app anyway!

You've essentially summarised your problem.

Even if you could encrypt the MySQL store - "all" they have to do is compromise your web server, and they get your DB credentials and can happily dump your database. The fact really is that if they can get the same level of access as you at any point - all your security efforts have gone down the drain.

Here are some general tips, but they don't replace your professional security advice:

Minimise access. Never have a user that can access the database server from any IP address, you're just begging for a brute-force attack. Instead, tweak the external firewall (e.g. the one EC2 called "Security groups") so only your web server can even connect to your DB server. The less people that know your VPS is a DB server, the better.
Make sure everything else is secure. That means your email passwords, security answers, and so on. If they can get access to your EC2 account or your email account, you've just completely wasted your time securing the instance. Social engineering attack are all the rage these days, don't let yourself be next.
Ensure your VPS instance is bare-metal. Don't have software on there that doesn't need to be, that even goes for sendmail (this is just an example of how, back then, you might have though "sendmail can't hurt to leave on!"). Anything that is there, keep updated frequently and watch out for security notices.
Try to avoid having any services in general that are public facing, especially something like SSH. If you must, disable password authentication and put SSH on a non-standard port (then connect to it like ssh -p <port> user@host as normal.

Having said all of this, it won't make your case to the client any better. You'll still say "it's as secure as it can be" but not "the data is encrypted on disk".


How to get the row number where its ID is 'n' and rows are ordered by some column?

I imagine you'll probably need a secondary ordering column, as Col1 is not a unique value, however this should get you started.

WITH sampledata AS
(
  SELECT
    ROW_NUMBER() OVER(ORDER BY Col1) as row_num,
    ID,
    Col0,
    Col1,
    Col2
  FROM Table1
)

SELECT
  row_num
FROM sampledata
WHERE ID = 263;


Is it possible to take the table level backup in mysql using any of the script?

To dump only specific tables from a database, name them on the command line following the database name:

mysqldump my_database table1 table3 table5 > dump.sql

To dump only specific tables from multiple database, use mysqldump again, but append to the end of the previously created file:

mysqldump my_database2 table1 table3 table5 >> dump.sql Note the >> sign

Reference:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html
https://stackoverflow.com/questions/8228116/mysqldump-tables-from-different-databases


Update multiple Ids when SET = (Sql Query)

Instead of specifying the same IN list for both Y and A2, specify it only for A2 and make the subquery correlate with T1 on T2.Y = T1.A2, like this:

UPDATE
  dbo.T1
SET
  A1 = (SELECT TOP 1 T2.X FROM dbo.T2 WHERE T2.Y = T1.A2)
WHERE
  A2 IN (ID1, ID2, ...)
;

This way the subquery will pick the topmost row from T2 that matches the key of the T1 row being updated.

Please note also that using TOP n without ORDER BY will result in an arbitrary row being selected. If there can be two or more distinct values of X per Y in T2, it might be a good idea to make the results deterministic by introducing an ORDER BY clause with specific enough set of sorting criteria.

On the other hand, if X stays the same per Y, you are probably storing redundant information in T2. Perhaps, in that case you should move the X column to T1, based on the facts that a) T2.Y appears to be a reference to T1.A2 and b) A2 is the primary key of T1.


cloud computer to speed up sql big data queries?

You've asked a question which often results in real projects (and I freely admit to being a consultant in this area).

I'm just going to give you some things to think about:

If you are getting your results from the cloud, you have to get your data there.  This may involve additional lags.
How real-time is real-time?
Do you have programming expertise in the group/company your work for?  For complex data manipulation tasks, Hadoop (and its derivatives) is a cloud-based technology that works well.
Once you've found something of interest, do you have a way to "productionize" it, so updating the results does not waste analysts' time.
The cloud (such as AWS) can provide very scalable hardware.  However, hardware is pretty cheap.  Part of the question is the balance between expenses versus capital investment.
Are you tied to MySQL?  Other databases have better functionality for complex queries on large data sets.


If you mean what I think you mean, no, you can't say something like USE [LinkedServer]; in an SSMS query window. There are alternatives:

You can change the current connection to a different server by right-clicking the query window, Connection, Change Connection. That will allow you to change connections to instances you can see, which may not be the same as the set of the linked servers the target can see, and it doesn't provide a way to do so programmatically.
Same issue with right-clicking a group of Registered Servers (Ctrl + Alt + G) and choosing New Query - those are registered servers on your client workstation, not linked servers set up in the target instance.
You can use SQLCMD mode and use the :CONNECT directive, but that presents the same issues as above.

I don't think either of those is what you're after. You can build the set of commands you would need to make the target instance execute a command against multiple linked servers this way:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  EXEC ' + QUOTENAME(name)
    + N'.master.sys.sp_executesql
      N''CREATE LOGIN [domain\user] FROM WINDOWS;
         ALTER ROLE [rolename] ADD MEMBER [domain\user];
         -- ...'';'
  FROM sys.servers
  WHERE is_linked = 1;

PRINT @sql;

You can use additional filters to remove certain linked servers using other criteria, or otherwise modify the commands, then copy and past them to execute. You can also just say the following if you trust it:

EXEC sys.sp_executesql @sql;

Since the PRINT command will only support 8k, which means a limit on the number of servers (actual number will depend on the length of the command and the length of the names of the linked servers).


The aggregate is a scalar aggregate (no group by clause). These are defined in SQL Server to always produce a row, even if the input is empty.

For a scalar aggregate, MAX of no rows is NULL, COUNT of no rows is zero, for example. The optimizer knows all about this, and can transform an outer join into an inner join in suitable circumstances.

-- NULL for a scalar aggregate
SELECT MAX(V.v) FROM (VALUES(1)) AS V (v) WHERE V.v = 2;

-- No row for a vector aggregate
SELECT MAX(V.v) FROM (VALUES(1)) AS V (v) WHERE V.v = 2 GROUP BY ();

For more about aggregates, see my article Fun With Scalar and Vector Aggregates.