Rotate table for year-over-year comparison I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_year int,
    condition number(10,2)
);

insert into road_insp (insp_id, road_id, insp_year, condition) values ( 1, 100, 2009, 5);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 2, 100, 2011, 3);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 3, 102, 2009, 9);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 4, 102, 2010, 7);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 5, 102, 2013, 5);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 6, 103, 2009, 10);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 7, 103, 2011, 8);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 8, 103, 2012, 6);
insert into road_insp (insp_id, road_id, insp_year, condition) values ( 9, 103, 2013, 4);

commit;

select
    insp_id,
    road_id,
    insp_year,
    condition
from
    road_insp;

   INSP_ID    ROAD_ID  INSP_YEAR  CONDITION
---------- ---------- ---------- ----------
         1        100       2009          5
         2        100       2011          3
         3        102       2009          9
         4        102       2010          7
         5        102       2013          5
         6        103       2009         10
         7        103       2011          8
         8        103       2012          6
         9        103       2013          4

I want to compare the condition of roads, year-over-year.

I can do this by joining to sub-selects for each year:

select
    a.road_id,
    y09.condition condition_2009,
    y10.condition condition_2010,
    y11.condition condition_2011,
    y12.condition condition_2012,
    y13.condition condition_2013
from
    road_insp a
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2009
    ) y09
    on a.road_id = y09.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2010
    ) y10
    on a.road_id = y10.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2011
    ) y11
    on a.road_id = y11.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2012
    ) y12
    on a.road_id = y12.road_id
left join
    (select
        road_id,
        condition
    from
        road_insp
    where
        insp_year = 2013
    ) y13
    on a.road_id = y13.road_id
group by
    a.road_id,
    y09.condition,
    y10.condition,
    y11.condition,
    y12.condition,
    y13.condition
order by
    road_id

   ROAD_ID CONDITION_2009 CONDITION_2010 CONDITION_2011 CONDITION_2012 CONDITION_2013
---------- -------------- -------------- -------------- -------------- --------------
       100              5                             3
       102              9              7                                            5
       103             10                             8              6              4

However, this method is quite cumbersome. Is there a more concise way to rotate a table for year-over-year comparison?

Yes, more concise ways of pivoting exist for your scenario.
Option 1
For instance, you could use the PIVOT operator:
SELECT
  *
FROM
  (
    SELECT
      road_id,
      insp_year,
      condition
    FROM
      road_insp
  ) derived
  PIVOT
  (
    MAX(condition)
    FOR insp_year IN
    (
      2009 AS condition_2009,
      2010 AS condition_2010,
      2011 AS condition_2011,
      2012 AS condition_2012,
      2013 AS condition_2013
    )
  )
;

PIVOT implies grouping, which is why you can see the MAX() aggregate function applied to condition rather than simply using condition.
The implicit grouping is also the reason you often need to use a derived table before applying the PIVOT operator: all columns not mentioned in the PIVOT clause actually participate in grouping, so you need to exclude those that are not supposed to be grouping criteria. For your specific example you do need a derived table, because you need to exclude insp_id.
Option 2
As an alternative to the PIVOT operator, you could try the old-school conditional aggregation method:
SELECT
  road_id,
  MAX(DECODE(insp_year, 2009, condition, NULL)) AS condition_2009,
  MAX(DECODE(insp_year, 2010, condition, NULL)) AS condition_2010,
  MAX(DECODE(insp_year, 2011, condition, NULL)) AS condition_2011,
  MAX(DECODE(insp_year, 2012, condition, NULL)) AS condition_2012,
  MAX(DECODE(insp_year, 2013, condition, NULL)) AS condition_2013
FROM
  road_insp
GROUP BY
  road_id
;

In this case there is no need to derive a table, as columns you want to group by can be explicitly specified in the GROUP BY clause.
—————————
Both methods produce the same output, identical to the one that your joining method generates, as can be seen in this demo at dbfiddle.uk.


Approach for a 2,000 table migration What might be the best approach when converting or migrating a database containing over 2,000 tables? Where would you begin to tackle such an issue?  Steps to take in the first weeks of designing? Risks?  Assuming there is hardly any information about the old (current) database?

The approach I take is broadly as follows (and this is by no means complete):

Requirements
You need to know the goals of the to be situation.

specifics of platforms (hardware, software), versions of these. There may be dependencies in terms of feature availability
features used in "from" situation and needed in "to" situation. Think of

queueing
clustering
high availability
backup and recovery

Analyse current situation for

requirements missing from above
DBMS or platform specific features being used

Design for an entirely automated and repeatable approach. This makes it

testable
able to run overnight/weekend in case of a long process
allows for an iterative solution where the database becomes more and more complete during the life of the project

Determine dependencies between systems both on the "from" side and on the "to" side. This will affect the "flow" of the migration.
Decide how the data will be migrated. Amongst others, choices are:

dump and reload, probably via some database agnostic format

if reloading, it may be easier to go via "temporary" tables and then populate the real target tables from the temporary ones. I've had good experience with this approach.
this also makes it easier to allow for "fallout" which can be manually corrected after cutover (but you need to keep this under control too).

"suck" the data from the "from" database into the "to" database using some database connection technology (eg Oracle Gateway, PostgreSQL foreign data wrappers). This approach is usually also best when wanting to perform a migration where the target DBMS is the same as the source.
decide whether to use scripting (SQL scripts mainly, but possibly also Perl/awk etc) or a tool (eg Oracle used to have a product called Migration Workbench or an ETL tool can be used for this purpose) or even "programmed" solutions in Java/C# etc. This decision will largely be driven by the skillset at hand. My preference is to use SQL wherever possible.

Build it.

If the target data model is different, determine mappings. An ETL tool can be handy in these cases, but well written SQL can be just as clear. Like all code, try to keep the documentation on the mapping with the code rather than in a separate document.


Delete query does not commit I need to delete about 900m rows from a table that has 6b rows. Since big deletes cause big problems for the transaction log, I'm deleting the data in small batches. What puzzles me is SQL Server does not like to commit each chunked delete.

Let's say I delete it in batches of 1m rows:

DECLARE @N int = 1000000

DELETE TOP (@N)
    FROM    my_table
    WHERE   ...

In a separate query, I monitor the status of the delete:

-- Before executing the first query
sp_spaceused 'my_table' -- row_count = 6b

-- During execution
sp_spaceused 'my_table' -- row_count = decreasing from 6b -> 5.999b

And then it just hung! I can tell that 1m rows have been removed from my_table by peeking at the row count, but the first query just keeps going on and on.

What could cause this behavior? Another big table has a Foreign Key relationship referencing my_table.ID. Could FK validation be the culprit here?

Database is SQL Server 2012.

Yes, it will definitely add overhead if you have foreign keys pointing to the table that you are trying to clear records from. This is because it needs to ensure that the constraint is not violated once the data has been removed. I've personally seen deletes take days with foreign key checks, but hours if you remove the foreign keys before performing the DELETE.

Disabling the foreign keys will not help in your scenario as you can only disable a foreign key check for INSERT and UPDATE statements. I recommend that you remove any dependent data from the related tables, then remove the foreign key (if possible), and then remove the data from the base table. Once the data has been completely removed you can re-add the foreign key relationship.


Mysql query to find last entry for each of the last 7 days I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be able to extract the data for the end of each of the last 7 days including the most recent log today. I have two different queries that almost achieve this but not quite:

SELECT *
FROM WaterDepth
WHERE (date >= DATE(NOW()) - INTERVAL 7 DAY) AND (HOUR(date)+MINUTE(date) = 68)
ORDER BY date ASC LIMIT 7

This captures the last 7 days at 23:45, but misses any days that don't have an entry at precisely 23:45 and misses today.

SELECT a1.*
FROM WaterDepth a1
INNER JOIN (SELECT max(date) as max
            FROM WaterDepth
            GROUP BY date(date)) a2 ON a1.date = a2.max

This captures the last entry for each day regardless of what time that is and includes today, but I'm not sure how to limit it to the last seven days. So I guess I'm looking a blend of the two.

Thanks in advance

SELECT a1.*
FROM WaterDepth a1
INNER JOIN ( SELECT MAX(`date`) as `max`
             FROM WaterDepth
             WHERE `date` >= CURRENT_DATE - INTERVAL 7 DAY
                 -- or 6 day? 7 day gives 8 different dates...
                 -- or today record not needed? if so add
                 -- AND `date` < CURRENT_DATE
             GROUP BY DATE(`date`) ) a2 ON a1.`date` = a2.`max`


Left join not working like expected I need to do a table join to find missing translations in a translation system.

Let's say we have the following simplified table (id is the identifier of the string, the actual translation is not included in the example for simplicity's sake):

+------+-------+
| lang | id    |
+------+-------+
| en   | hello |
| en   | world |
| fr   | hello |
| de   | world |
+------+-------+

My initial thought was to do a simple left join:

select a.*, b.* from trans a left join trans b using (id) where a.lang = 'en';

I thought I would get this (so I could add where b.id is null to find the missing translation):

+------+-------+------+-------+
| lang | id    | lang | id    |
+------+-------+------+-------+
| en   | hello | en   | hello |
| en   | world | en   | world |
| en   | hello | fr   | hello |
| en   | world | NULL | NULL  |
| en   | hello | NULL | NULL  |
| en   | world | de   | NULL  |
+------+-------+------+-------+

What I actually get is this:

+------+-------+------+-------+
| lang | id    | lang | id    |
+------+-------+------+-------+
| en   | hello | en   | hello |
| en   | world | en   | world |
| en   | hello | fr   | hello |
| en   | world | de   | world |
+------+-------+------+-------+

What mistake have I made in my way of thinking, and what is the correct query to get the desired output?

If you run the statement without the where clause you'll see why:

lang | id    | lang | id
-----+-------+------+------
en   | hello | en   | hello
en   | hello | fr   | hello
fr   | hello | en   | hello
fr   | hello | fr   | hello
en   | world | en   | world
en   | world | de   | world
de   | world | en   | world
de   | world | de   | world

The join on the "id" column works like this:

Take the first hello from the table and look for all rows that contain hello - that yield two rows for the first hello. The same happens with the second hello, so you wind up with 2x2 rows for the join on hello. And the same for world

The outer join does not play any role, because there is a match for each id (actually: two matches).

You can never get your first (intended) result because that implies that all rows in the "a" table have lang='en' (which is of course not true).

To get the missing translations you need to first create the combination of all languages and ids:

select distinct a.id, b.lang
from trans a
  cross join trans b;

Now you need to find all rows that are not in that result:

select *
from (
  select distinct a.id, b.lang
  from trans a
    cross join trans b
) ac
where not exists (select 1
                  from trans mt
                  where mt.id = ac.id
                    and mt.lang = ac.lang);

You can achieve this with an outer join as well. I simply prefer the not exists because it documents more clearly the intention (and because I hardly ever work with MySQL which is known to perform poorly with sub-queries like that)

select ac.*
from (
  select distinct a.id, b.lang
  from trans a
    cross join trans b
) ac
  left join trans mt on mt.id = ac.id and mt.lang = ac.lang
where mt.id is null;

Here is an SQLFiddle: http://sqlfiddle.com/#!2/9804d/6

Edit

after testing the peformance with larger tables, it seems that Sean's version of the cross join is much more efficient than mine.

So this statement should be faster than the ones above:

select at.*
from (
    select lang_code, label_code
    from (
        SELECT distinct lang_code
        FROM translations
    ) as translang
      cross join (
         SELECT distinct label_code
         FROM translations
      ) as transid
) at
  left join translations mt
         on mt.lang_code = at.lang_code
        and mt.label_code = at.label_code
where mt.lang_code is null;

Edit 2

And another version to be tested (SQL-Fiddle):

SELECT a.lang, a.id,
       l.lang AS blang
FROM trans a
  CROSS JOIN
    ( SELECT DISTINCT lang
      FROM trans
    ) l
  LEFT JOIN trans b
    ON  b.id = a.id
    AND b.lang = l.lang
WHERE a.lang = 'en'
  AND b.id IS NULL ;


Methods of speeding up a huge DELETE FROM  with no clauses Using SQL Server 2005.

I am performing a huge DELETE FROM  with no where clauses.  It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE.  The problem is the table is huge - 10 million rows, and it takes over an hour to complete.  Is there any way of making it faster without:

Using Truncate
Disabling or dropping indexes?

The t-log is already on a separate disk.

Any suggestions welcome!

What you can do is batch deletes like this:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

Where xxx is, say, 50000

A modification of this, if you want to remove a very high percentage of rows...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable


Is it Possible to Recover Data from a BLACKHOLE table? I had created table with engine BLACKHOLE  basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result.

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam. but i had tried that also but unable to get the result. Can any one pl help me on this issue to fix it.

mysql> CREATE TABLE test1(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test1 VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test1;
Empty set (0.00 sec)

mysql>  CREATE TABLE test_recovery as select * from test1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_recovery ENGINE = innodb;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_recovery;
Empty set (0.00 sec)

You said

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam

Whoever told you that should have told you to enable binary logs as @jynus commented.
The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups.
EXAMPLE #1 : Star Topology
Some have Star Topologies to write data to a MySQL Replication Master. The Master has nothing but BLACKHOLE tables. All Slaves have InnoDB or MyISAM. That way, if the Master gets hacked, there is no local data. Such a Master is known as a Distribution Master.

Apr 28, 2011 : MySQL in star topology (ServerFault)
Mar 11, 2011 : MySQL in star topology (StackOverflow)

EXAMPLE #2 : Single Slave, Multiple Masters
This involves setting up Circular Replication with three servers. Two Masters with a mix of BLACKHOLE tables and a Slave with all real tables. This technique predates multisource replication.

Apr 18, 2013 : Single slave - multiple master MySQL replication
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB? (ServerFault)

EXAMPLE #3 : Increase Write Performance
I knew of a Web Hosting Client who went to Percona for performance issues on one table. Since the client had MySQL Replication with Multiple Slaves, Percona converted the problem table to BLACKHOLE. This increased write performance on the Master. The data had to be read from the Slaves.
I once suggested this in a post for recording audit information (Performance of a Trigger vs Stored Procedure in MySQL)
EPILOGUE
While some of the comments remind me of StarTrek DS9, the BLACKHOLE Storage Engine is what its name says: NO DATA IS EVER STORED !!! If you had binary logs enabled, that would have been your only chance of having data.


Speed up slow postgres query with indices (~5 minutes) I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so:

CREATE TABLE users(
  id     bigserial  PRIMARY KEY,
  uname  text       NOT NULL
);

-- a couple million rows
CREATE TABLE games(
  id     bigserial  PRIMARY KEY,
  ended  boolean    NOT NULL DEFAULT FALSE
);

-- around a hundred million rows
CREATE TABLE bets(
  id       bigserial  PRIMARY KEY,
  game_id  bigint     NOT NULL REFERENCES games(id),
  user_id  bigint     NOT NULL REFERENCES users(id),
  wager    float      NOT NULL
);
CREATE INDEX bets_user_id_idx ON bets(user_id, id);
CREATE INDEX bets_game_id_idx ON bets(game_id);

This is the query that runs slow:

-- find the 50 most recent bets
SELECT bets.wager, g.ended
FROM bets
JOIN LATERAL (
    SELECT * FROM games
    WHERE id = bets.game_id
    ORDER BY id DESC
    LIMIT 50
) as g ON TRUE
WHERE user_id = 1
ORDER BY game_id DESC
LIMIT 50;

This is how everything looks like:

Problematic index scan when running explain:

  {
    "Node Type": "Index Scan",
    "Parent Relationship": "Outer",
    "Parallel Aware": false,
    "Scan Direction": "Backward",
    "Index Name": "bets_game_id_idx",
    "Relation Name": "bets",
    "Schema": "public",
    "Alias": "bets",
    "Startup Cost": 0.57,
    "Total Cost": 5077159.99,
    "Plan Rows": 12302,
    "Plan Width": 32,
    "Actual Startup Time": 5200.174,
    "Actual Total Time": 265417.040,
    "Actual Rows": 50,
    "Actual Loops": 1,
    "Output": ["bets.wager", "games.ended"],
    "Filter": "(bets.user_id = 1)",
    "Rows Removed by Filter": 106898896,
    "Shared Hit Blocks": 44257825,
    "Shared Read Blocks": 1566663,
    "Shared Dirtied Blocks": 1,
    "Shared Written Blocks": 117,
    "Local Hit Blocks": 0,
    "Local Read Blocks": 0,
    "Local Dirtied Blocks": 0,
    "Local Written Blocks": 0,
    "Temp Read Blocks": 0,
    "Temp Written Blocks": 0
  }

WHERE user_id = 1
ORDER BY game_id DESC

You probably need an index on bets (user_id, game_id).  That would let it tackle the above in a very efficient way, rather than the very inefficient it is doing it now:

"Filter": "(bets.user_id = 1)",
"Rows Removed by Filter": 106898896,

If that doesn't do it for you, please provide a complete text-formatted "explain (analyze, buffers)".  Neither of the ways you presented it is very effective.  Especially since we don't even know what tool generated the image one, and the two things seem to have come from different executions plans.


Is this table in third normal form? I have a table L(A, B, C). Functional dependencies are: A->B, A->C, B->A, B->C. Is this table in 3nf?
My thoughts: A and B are keys of this table. Table is in 3nf if there are no transitive dependencies between keys and non-prime attributes. There is a transitive dependence A->B->C, so it's not in 3nf? Or it does not work in this way, because B is a key? Hopefully I didn't confuse you too much. Thanks.

Here's one attempt (this is not a day to day activity for me, so I may be doing some weird errors below):

A and B are clearly candidate keys of R. Therefore C is the only non-prime attribute of R

R is in 3NF iff:

a) R is in 2NF

b) Every non-prime attribute of R (C) is non-transitively dependent on every superkey of R.

The superkeys of R is (A, B), (A, C), (A, B, C)

By reflexivity (A, C) -> C AND (A, B, C) -> C
By composition of A->C and B->C we get (A, B) -> (C, C), i.e. (A, B) -> C

C is therefore non-transitively dependent on every superkey of R, and hence R is in 3NF.


How to store short stories for access to individual sentences? I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the fact that the user will access text bodies in two ways:

1) access the entire body of the story on click of the story name.

2) the user can input a word or phrase into a search bar, which will return all sentences (not the whole stories) in which the word/phrase is found (meaning that it could potentially return many sentences from many stories).

There will be a great ("infinite") number of stories, and about 40 sentences per story, although it is free text so some stories will contain a few hundred sentences.

My initial DB design was to have a Story model (I'm using Ruby on Rails) with a story_id, story_title, and author_id_fk, and then to have a Storyline model with storyline_id, storyline, and story_id_fk.

However, I'm now doubting myself and think that maybe the best way to do it is to slap the body of the story onto a 4th column in the Story model called story_text, where I will store an array of strings (aka, the original text parsed into its corresponding sentences), and then the Storyline model can either simply not exist (in which case the appropriate item from the array would be called when needed - less normal, but also perhaps more efficient..?), or to keep the Storyline model, but have it contain a reference to the appropriate storyline as opposed to the actual text itself.

Any thoughts or suggestions would be much appreciated!

The best solution heavily depends on the definition of "best". You may be looking for simplicity, reliability, performance or disk space optimization or other things.
Specifications

infinite amount of stories, and about ~40 sentences per story

Few sentences, possibly many stories.

user can input a word or phrase

We need phrase search. (Maybe across sentence boundaries?)
Also assuming:

"Sentences" are substrings simply separated by dot and space('. ' ).
Resources (RAM, disk space) may be a limiting factor.
Retrieving the whole body and searching for individual sentences are equally frequent tasks.

Solution
Postgres 9.6 introduced exactly what you need. The release notes:

Full-text search can now search for phrases (multiple adjacent words)

The latest Postgres release and a text search index will go a long way for performance in either case.
You already considered to:

slap the body of the story onto a 4th column in the Story model

But an array adds 24 bytes + 1 or more bytes per sentence and makes indexing much more complicated. (As well as pretty much all other operations.) I would exclude the array solution.

Indexing an array for full text search

An additional storyline table with one row per sentence makes indexing simple again, but adds ~ 40 bytes per sentence. Finding and retrieving sentences is simple. But all write operations are more complicated.
About storage size in Postgres:

Configuring PostgreSQL for read performance

Consider a single table with body as plain text column. Splitting the string into sentences on the fly is fast (with a simple definition of "sentence"). Smaller total storage size may be more important for performance with big tables (fewer pages to fetch). And we only need to index one column. In short:

dead simple storage and handling
minimum disk space
still a hot contender for best performance

Table:
CREATE TABLE story (
  story_id  serial PRIMARY KEY
, author_id int REFERENCES author  -- NOT NULL?
, title text                       -- NOT NULL?
, body  text                       -- NOT NULL?
);

FTS index, assuming English language:
CREATE INDEX story_body_english_fts_idx ON story USING GIN (to_tsvector('english', body));

Query to return whole story:
SELECT *
FROM   story
WHERE  to_tsvector('english', body) @@ phraseto_tsquery('english', 'Lewis Carroll');

Query to return sentences:
SELECT story_id, sentence_nr, sentence
FROM   story, unnest(string_to_array(body, '. ')) WITH ORDINALITY x(sentence, sentence_nr)
WHERE  to_tsvector('english', body)     @@ phraseto_tsquery('english', 'Lewis Carroll')
AND    to_tsvector('english', sentence) @@ phraseto_tsquery('english', 'Lewis Carroll');

The query demonstrates a phrase example. But it works for single words as well.
The repeated WHERE condition is logically redundant but necessary to use the index on body. This retrieves only stories with qualifying sentences, then the recheck on unnested sentences (repeating the same condition) is cheap.
About unnest(string_to_array(body, '. ')) WITH ORDINALITY:

How to preserve the original order of elements in an unnested array?
What is the difference between LATERAL and a subquery in PostgreSQL?

Note: The check on the whole text body can find phrases across sentence boundaries - unlike searching individual sentences. But the recheck on sentence excludes such matches. You can fine-tune one or the other way ...
There are many more options with FTS: prefix matching, other languages, no language (no stemming or thesaurus), phrases with words in between ...

Get partial match from GIN indexed TSVECTOR column


There is any way to log all mongodb query into file in nodejs? There is any way to log all MongoDB query into a file

Enable the profiler in MongoDB Database.

https://docs.mongodb.com/manual/reference/method/db.setProfilingLevel/


Contacts table with multiple entries? Here is a part of our Database:

Table: Users
ID            INT
First Name    VARCHAR
Last Name     VARCHAR
Email Address VARCHAR
...

Table: Contacts
ID            INT
User_id       INT (Foreign Key)
First Name    VARCHAR
Last Name     VARCHAR
Email Address VARCHAR (Unique)
Phone Number  VARCHAR
Address       VARCHAR
...

A user can have multiple contacts (One-to-many relationship).

It is possible that a contact of one user is also a contact of another user. And the second user may try to enter different phone number/address (but same email address). I cannot allow duplicate email addresses.

One possible solution is to split contacts table into 2 tables:

Table: Contacts
ID            INT
Email Address VARCHAR
...

Table: Contact_attributess
ID            INT
User_id       INT (Foreign Key)
Contact_id    INT (Foreign Key)
First Name    VARCHAR
Last Name     VARCHAR

Is this the best way to reorganize my DB? Do you have other solutions?

user table
---------
USERID
EmailAddress

User Details
-------------
USerDetailID
USERID (FK)
FirstName
-----

USER_Contact_Mapping
--------------------
USERcontactMapID
UserID
contactID

User table hold info of both User and contact.This table can by utilise to hold other
info which won't be duplicated.

User Details table hold info of both User and contact.This table contain multiple detail of same user.
Example

USER_Contact_Mapping contain relation between USERID and ContactID.This table is must.


What is the best way to store large lists of user statistics? I am trying to figure out the best way to store user statistics data in a table. For each user property, there can be anywhere from one to a couple thousand integer values (realistically, these would would average to a number in the hundreds). I know that PostgreSQL allows columns to contain integer[] values, but is this really the best solution when I could potentially be storing a a couple hundred or a thousand integers in one cell?
Here is example table with a few of the user properties I might store in it:
| UserStatistics                                                                                |
|-----------------------------------------------------------------------------------------------------|
| user_id | user_song_likes | user_album_likes | user_artist_likes | user_followers | user_following  |
|-----------------------------------------------------------------------------------------------------|
| 1       | [12,56,34,11,7] | [1,154,32,33,84] | [5,14,54,43,10,2] | [55,63,23,2,4] | [43,78,2,4,22]  |
|-----------------------------------------------------------------------------------------------------|
| 2       | [1,2,3,4,87,89] | [62,71,4,6,12,2] | [13,17,35,76,1,2] | [91,17,7,87,9] | [21,17,79,98,1] |
|-----------------------------------------------------------------------------------------------------|
| 3       | [45,53,42,19,2] | [7,14,144,67,93] | [13,14,2,23,3,73] | [11,56,5,12,1] | [34,91,23,10,5] |
|-----------------------------------------------------------------------------------------------------|

In this example, the user_song_likes array will realistically hold hundreds of values (I scaled down the array for the sake of this example). So will the user_album_likes array. user_artist_likes will most likely come close to a hundred values, but it will definitely surpass that for some users. user_followers will average a between 20 and 80 values, but there are going to be a few select users with tens of thousands of followers.  user_following is going to average between 100 and 2,000 values for almost every user.
All this considered, I think it would be very inefficient to store user statistics with integer[] columns. What are my alternative options? What would be a reasonably efficient way to store this data?

As others have mentioned, I'd look to normalize the data in your UserStatistics table. You could do something stupid simple as a generic Likes table and a Follows table (don't take my poor names, I'm sure you can come up with better ones :).
The Likes table would have its own primary key, and a foreign key field referencing user_id, and it could have a column called like_id and a column called like_type. The like_id would store one row per ID from the user_song_likes, user_album_likes, and user_artist_likes columns of your previous schema, and the like_type would represent if it's of type song, album, or artist.
You could then implement the same idea for your Follows table (e.g. store one row per ID of each follower and following in a generic follow_id column with a follow_type column to determine which is which). Using a more normalized schema (even though this one is generic to keep things simple) is a more relational form of storing your data and will be more efficient for querying.


mongo user can not connect from bash on localhost In mongo shell, I ran the following commands:
use mydatabase
db.createUser( { user: "john", pwd: "mongopass", roles: [ "readWrite", "dbAdmin" ] });

Mongo reported a success response. So then from terminal, I typed mongo --username john --password, and when prompted for password, I typed mongopass.   But the result I got was:
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Error: Authentication failed. :
connect@src/mongo/shell/mongo.js:374:17
@(connect):2:6
exception: connect failed
exiting with code 1

How do I login to mongodb with a username and password?

This is my /etc/mongo.conf
# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true
#  engine:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile
  timeZoneInfo: /usr/share/zoneinfo

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1

#security:

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

MongoDB auth setting is false as a default.
First of all, you should create the root user.
//create new user with the root role (also, named root):
use admin
db.createUser({
  user: "root",
  pwd: "pass",
  roles : [ "root" ]
})

After that, you should start your mongod service with the auth option, it means authentication is enabled.
mongod --auth

Also, you can add your mongod.conf file this parameter :
security:
    authorization: enabled

And start your mongod with the command which is below.
mongod --config /etc/mongod.conf //your mongod.conf path

Check this out for more info.


Add user to Server Role in a transaction Situation

One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning database roles, etc.

Now that we are implementing another feature, we would like to assign each new user the pre-defined server role bulkadmin. For this, there exists a wonderful SP sp_addsrvrolemember.

When executing this, though, we get the error

The procedure 'sys.sp_addsrvrolemember' cannot be executed within a transaction.

The webapp that creates the other webapps uses transactions for each SQL query it executes and it would involve a lot of customizations and exceptions to handle this.

Question

Is it in another way (or in this way, with some modifications) possible to achieve this, i.e. granting a user the bulkadmin right?

The sp_addsrvrolemember has hard-coded that @@trancount should be 0, so that's not going to give me a solution. Google and SO have not been really helpful either, or I haven't been able to find it. Usually the answer is "don't use transactions", which is not an option in our case.

Of course, just after posting, I found it myself:

USE [master];
GRANT ADMINISTER BULK OPERATIONS TO [user]

It's that easy.


Mysql 8 - Count(*) - performance issue I have this table with 2 million records
CREATE TABLE `my_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USER` int(11) DEFAULT NULL,
  `BODY` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `CREATION_DATE` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID_USER` (`ID_USER`),
  KEY `CREATION_DATE` (`CREATION_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=2198623 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The first run of this select returns '1131' in 70 seconds
select count(*) from my_table where ID_USER=123 and CREATION_DATE>=2019-07-29

The second run returns '1131' in 60 milliseconds.
What do you recommend to improve performance?
More info:

ID as cardinality 2198623
ID_USER as cardinality 12788
CREATION_DATE as cardinality 11909

"Duct Tape" option: Remove the index on ID_User and replace it with an index on (ID_User,CREATION_DATE).
Best option: Get rid of the Id and find another suitable primary key such as (ID_User,<timestamp>).  This is preferable as it would: ensure uniqueness, remove the need for one index, and get rid of a useless column.


Dropping/creating procedure causes transaction log to empty Steps to reproduce:

Make sure database is in Full recovery mode
Run procedures (containing insert, updates) causing transaction log to grow
use DBCC SQLPERF(logspace) to check transaction log size
drop/create used procedures
Observe that the Log space used dropped (in my case from about 20% to 0.5% with Log size unchanged)

I am unable to determine what is going on.

NOTE: This is a dev machine, nothing escept management studio queries interact with the database

SQL Server acts like simple mode until you've established the log chain. This means that it will be happy to discard log information from already-committed transactions.

Paul Randal goes into a little more detail about the mechanics behind this here, but basically, this behavior will stop once you start proper backups:

New script: is that database REALLY in the FULL recovery mode?


Strategies for long term data growth optimization for a query with an index on a joined table Given two simple, contrived example tables
create schema test;

create table test.exchange (
  id serial primary key,
  created_at date not null
);
create index on test.exchange(created_at);

create table test.exchange_content (
  id serial primary key,
  content text not null,
  exchange integer not null references "exchange"(id)
);
create index on test.exchange_content(exchange);

with a lot fake data
INSERT INTO test.exchange(id,created_at)
SELECT id, NOW() + (random() * (NOW()+'360 days' - NOW())) + '30 days'
FROM generate_series(1,10000000) id;

INSERT INTO test.exchange_content(id,content,exchange)
SELECT id, md5(random()::text), id % 10000000 + 1
FROM generate_series(0,30000000) id;

Attempting this query...
select count(*) from exchange
left join exchange_content ec on exchange.id = ec.exchange
where created_at > '2021-10-14';

runs a sequential scan on exchange_content (for Postgres 9.6)
With these tables and large queries frequently being executed on them together, what are the strategies for ensuring that the queries will scale well, as the tables grow?
(Table partitions seem like the best option so far, is there any other options that I'm missing?)
Update: Based on comments it looks like upgrading to Postgres 12 would be best, but I'm still wondering if there are idiomatic strategies for designing tables with this structure and large amounts of data.

What have I considered:

Composite indexes but they don't work across multiple tables
Duplicating the created_at field over to exchange_content and giving it its own index
Using a materialized view with the created_at field and the data I need from exchange_content
It may be using Seq Scan because Seq Scan may be faster than using the index
Partial indexes, but I don't think they apply given my requirements
Using a table partition for date ranges

It is not inevitable that it will keep using the seq scan.  Perhaps at some point it will change (indeed, if the tables are vacuumed and analyzed, I don't get the seq scan in the first place, as the anonymous horse indicates.)
However, if your tables keep growing into the future (rather than into the past) your query will count more and more rows, and this will take longer and longer to do.
Since you are using fake data anyway, why not just use more of it and see what happens?  This is the key benefit of working with simulated data sets, you can simulate whatever you want.
With 9.6, I get a nested loop over two index scans as long as the exchange_content table is well-vacuumed enough that the index-only scan seems worthwhile.  So I would say that the main strategy you have overlooked is to design your indexes so that you can get index-only scans, and make sure to vacuum often enough. For insert only/mostly tables, it can be hard to get autovac to vacuum often enough, (until v13 introduces autovacuum_vacuum_insert_scale_factor) and so until then it might be best accomplished by "manually" VACUUMing from cron jobs.
Moving the created_at column to the other table would be an obvious winner if you were using an inner join rather than left join (at which point, you could drop the join with exchange altogether and just query exchange_content). But if the left join is really needed, it is not clear how this could work. Also, assuming that exchange_content is not where the column naturally belongs, then moving/copying it there would be a denormalization.  This generally comes at a high cost from a integrity and maintenance perspective even if it does pay off for SELECT performance.
It is not clear how partitioning could help here.  You can't partition exchange_content by created_at without having the created_at column exist in that table.  And if it did exist in that table, you wouldn't need partitioning you could just index it directly.


How can I tell how many Cores SQL Server, at peak, it is using? I've seen this question here (and elsewhere), but it doesn't really answer the question I'm trying to answer.

How can I tell how many Cores SQL Server is actually using?

I already know how many cores/CPUs are physically assigned to SQL Server. However, I want to know how many of those cores are, at peak, REALLY being used (ideally, I'd love to know percentiles, utilization %s per core, etc., but I'll take what I can get).

We'd like to migrate this instance to another machine and if we can reduce the number of cores assigned that could free up some core licensing for us. However, I don't want to "downsize" only to find out that our new SQL box falls over because it really needed those extra cores. We will be doing load testing, but the more info. we have around this, the better.

EDIT: I ended up just setting up some dashboard software which makes it easy to see/break out CPU usage (could have used PerfMon).

However, I want to know how many of those cores are, at peak, REALLY being used

This isn't really the way you'd want to figure out hardware size for a new server. You'd want to look at your server's wait stats.

If you want to do that for free, head over to firstresponderkit.org, and grab sp_BlitzFirst. It's a free tool written by the company I work for to help you figure out SQL Server bottlenecks.

There are a couple ways to run it that will be helpful to you:

EXEC master.dbo.sp_BlitzFirst @SinceStartup = 1;

That'll get you a whole bunch of server metrics since startup. Generally, the longer a server is up, the more valuable these metrics are.

Look at your top 5-10 waits here; the results may surprise you. There's a lot written about which resource different waits line up to, so searching is your friend.

If no waits are even close to your server's uptime (and you're not clearing wait stats), then congratulations, your server is bored.

You can also run:

EXEC master.dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

If you want to take samples of server metrics for a set amount of time.

I wrote a whitepaper about this for Google (which you can also download and read for free) recently using the same (free!) tools. While it's geared towards people who are sizing instances in the cloud, it can be applied to physical or virtual servers anywhere.

Hope this helps!


Do you define "different content" as row filtering via security settings? That is pretty standard - and pretty unusual (as in: standard for high security, not all databases support this thinking)... SQL Server for example is just adding this with version 2016 due this year. In most cases... people do not bother as the filtering of what data is visible is done on the application layer (possibly in a server obviously).


How to solve log file sync wait in oracle 12c I have performance issue with Oracle instance.
There is some java process that makes massive inserting into database. And the top wait event is "log file sync". I tried to change some Oracle database parameters, but result still not satisfied users...
I'm newbie in Oracle, so please be patient :)

Settings that I changed:

MEMORY_TARGET = 34359738368
MEMORY_MAX_TARGET = 34359738368
OPTIMIZER_MODE = FIRST_ROWS
OPTIMIZER_USE_PENDING_STATISTICS = TRUE
COMMIT_WRITE = BATCH, NOWAIT
REDO LOG FILES = 3 x 1000MB
_use_single_log_writer = TRUE
commit_logging=batch
commit_wait=nowait

Things that I can't change (unfortunately):

RAID 5
Java code
count of commits
SQL code
Also I can't disable logging

If your archiver is not keeping up. you should probably create smaller online redo log files, hence they will be faster to archive, and more online redo log groups. Try having 10 250MB online redo log groups and see if that works better.

You may want to check the IO sub system to see what kind of throughput you are getting. In 11gR2 and 12cR1 Orion is already installed. You should be able to take a copy of a datafile and trash it with Orion to find out what the reads and writes look like.

Using Orion Calibration to check I/O numbers

As a separate thought, can you create a new volume for the temp and online redo logs with mirroring? It could be that no one here can help you if your issue is related to your storage sub system, or code that commits way too often. You should look into changing the things that you said that you can't change, since that could be where the issue is.

I would not say that you can't do anything. You can look for poor performing SQL and see if you can add an index. You can look at the large insert statements and see what constraints, indexes and most likely triggers exist and see if there is something there that is causing an issue. How many tables are getting inserts, and do they have row by row triggers. If so start tuning there.


How to avoid split in output in sqlplus I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script.
Unfortunately, few PDB NAMEs are split/broken in the output(for example, P  DB1P016, PD B1P032 etc) so the variable is not assigning proper/correct value.
Any idea to fix this?

SQL> select LISTAGG(a.name, ' ') WITHIN GROUP (ORDER BY name) as pdbs from v$pdbs a;

PDBS
-------------------------------------------------------------------------------------------------------------------------------------------------
IOC1P002 PDB$SEED PDB1P001 PDB1P002 PDB1P003 PDB1P004 PDB1P005 PDB1P006 PDB1P007 PDB1P008 PDB1P009 PDB1P010 PDB1P012 PDB1P013 PDB1P014 PDB1P015 P
DB1P016 PDB1P017 PDB1P018 PDB1P019 PDB1P020 PDB1P021 PDB1P022 PDB1P023 PDB1P024 PDB1P025 PDB1P026 PDB1P027 PDB1P028 PDB1P029 PDB1P030 PDB1P031 PD
B1P032 PDB1P033 PDB1P034 PDB1P035 PDB1P036 PDB1P037 PDB1P038 PDB1P039 PDB1P040 PDB1P041 PDB1P042 PDB1P043 PDB1P044 PDB1P045 PDB1P046 PDB1P047 PDB
1P048 PDB1P049 PDB1P050 PDB1P051 PDB1P052 PDB1P053 PDB1P054 PDB1P055 PDB1P056 PDB1P057 PDB1P058 PDB1P059 PDB1P060 PDB1P061 PDB1P062 PDB1P063 PDB1
P064 PDB1P065 PDB1P066 PDB1P067 PDB1P068 PDB1P069 PDB1P070 PDB1P071 PDB1P072 PDB1P073 PDB1P074 PDB1P075 PDB1P076 PDB1P077 PDB1P078 PDB1P079 PDB1P
080 PDBCUS12

As with many things database-y, it's probably easier to extract them as rows and then reformat using your favourite shell-based text processor or, if you still want to try and get them all on one line, make sqlplus's "line" as big as it will go:

set linesize 32767


Mysql can't write to filesystem I need to output some queries as csv files.  The directory is in the webroot and I want it to be writable by apache too, so I'm trying to add mysql to the www-data group (on Ubuntu).

I thought this would work, but I've missed something:

Add mysql to www-data:

ubuntu@168:~/viewer/files$ sudo usermod -a -G www-data mysql

Restart mysql:

ubuntu@168:~/viewer/files$ sudo /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld                       [ OK ]
ubuntu@168:~/viewer/files$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                       [ OK ]

Verify permissions:

ubuntu@168:/var/www/html/viewer/files$ pwd
/var/www/html/viewer/files
ubuntu@168:/var/www/html/viewer/files$ ls -al
total 640
drwxrwxr-x 2 ubuntu www-data   4096 Jun 10 18:50 .
drwxrwxr-x 3 ubuntu www-data   4096 Jun 10 14:31 ..

Verify group:

ubuntu@168:~$ id mysql
uid=106(mysql) gid=112(mysql) groups=112(mysql),33(www-data)

I've restarted my session - no good.  A day later and my sql query still isn't able to write out:

mysql> SELECT id, title FROM recipes WHERE id IN ('R225037', 'R225046', 'R226412') INTO OUTFILE '/var/www/html/viewer/files/ingredient_caps_6-10-14.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1 (HY000): Can't create/write to file '/var/www/html/viewer/files/ingredient_caps_6-10-14.csv' (Errcode: 13)

On Ubuntu apparmor doesn't let mysql write to directories other than listed in /etc/apparmor.d/usr.sbin.mysqld

apparmor is enabled by default, but on dedicated MySQL servers I don't see much sense in it.

From https://help.ubuntu.com/14.04/serverguide/apparmor.html:
"AppArmor can be disabled, and the kernel module unloaded by entering the following:

    sudo service apparmor stop
    sudo update-rc.d -f apparmor remove

"


How do I know if a AWS Server instance has license SQL Server Standard or SQL Server Web? I'm looking my AWS billing and this doubt has come up. How do I know if a specific instance is running SQL Server 2008-R2 Standard or SQL Server Web? (using the AWS Console or SQL Server Management Studio)

The Management Studio About shows the following:

Microsoft SQL Server Management Studio 10.50.2806.0
Microsoft Analysis Services Client Tools 10.50.2806.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 2.0.50727.5456
Operating System 6.1.7601

Looking for version 10.50.2806.0, I've saw that it may be related for both licenses (link), so I don't know how to distinguish them.

Run SELECT @@VERSION; in a query window, with results to text. You will see something like this (minor details will vary) in the Messages pane:

Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
    Aug 13 2014 11:36:34
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.4 <X64> (Build 9841: )
----^^^^^^^^^ edition will be here

Help > About in Management Studio is just about the client tools, so may not even reflect the same version as the server, never mind that Management Studio no longer has any edition differentiations.


Optimizing Queries HAVING COUNT and INSERT...ON DUPLICATE KEY UPDATE There are two main queries I'm tying to improve, one question is much more conceptual and learning about databases and another is much more straightforward.

Both cases deal with the same table
It has 2 relevant columns as far as the question is concerned, they make up the primary key of the table. Each of the elements of the primary key is hypothetically the key to another table n their own right. The first row is an int (this is an item) an the second row is a VARCHAR (this is a user) limited to 200 characters.

The first issue comes in trying to find an intersection of users. I am looking to find an intersection of users that share a number of items, 5 in the example

SELECT userid
FROM   (SELECT b.userid,
               b.showid
        FROM   ratings AS A
               JOIN ratings AS B
                 ON A.userid LIKE :user
                    AND B.userid NOT LIKE :user
                    AND A.showid LIKE B.showid) AS C
GROUP  BY userid
HAVING Count(*) > 4

On a fast run this takes up about 2/3 of the runtime of my program (appearing twice, one nested in another query taking little longer than this one). I've tried looking at it but I don't see any obvious way to restructure the query to save time, but queries operating over many more rows are coming up with runtimes of less than 1 second while this takes 2 seconds.

The second issue is more conceptual. As mentioned before I have a composite primary key. I've found elsewhere that with InnoDB tables this can make inserts take up a lot more time. This is exactly whats happening with an INSERT ... UPDATE ON DUPLICATE KEY query that I'm running. What is the best way to go about reducing this slowdown.

This is what the insert looks like, it only runs if the show has been rated

INSERT INTO shows
            (
                        showid,
                        showname,
                        showimage
            )
            VALUES
            (
                        :id,
                        :name,
                        :image
            )
on duplicate KEY
UPDATE showimage=:image,
       showname= :name;
INSERT INTO ratings
            (
                        userid,
                        showid,
                        rating
            )
            VALUES
            (
                        :user,
                        :show,
                        :rating
            )
on duplicate KEY
UPDATE rating=:rating

Shows

CREATE TABLE "shows" (
 "ShowId" int(11) NOT NULL,
 "ShowName" varchar(400) NOT NULL,
 "ShowImage" varchar(400) NOT NULL,
 "ShowAgreement" int(11) NOT NULL DEFAULT "0",
 PRIMARY KEY ("ShowId")
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Ratings

CREATE TABLE "ratings" (
 "UserId" varchar(200) NOT NULL,
 "ShowId" int(11) NOT NULL,
 "Rating" int(11) NOT NULL,
 PRIMARY KEY ("UserId","ShowId"),
 KEY "ShowId" ("ShowId"),
 CONSTRAINT "ratings_ibfk_1" FOREIGN KEY ("ShowId") REFERENCES "shows" ("ShowId") ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

For a single test case the INSERT takes an additional 8 seconds over the UPDATE for 160 entries.

Should I simply run the conditional part of the query codeside and removing the primary key.
Should I drop the primary key and replace it with a unique constraint?
Should I create a new table for the UserId that creates a VARCHAR -> INT  identity for userId's and give all existing tables the INT version of the id rather than the CHAR?
Should I do something else entirely that hadn't occurred to me?

I'm guessing a bit on what your query is supposed to do. I assume you are looking for all users that share 5 or more items with a certain user:

SELECT a.userid
FROM ratings AS a
WHERE EXISTS (
      SELECT 1
      FROM ratings AS b
      WHERE b.userid = :user
        AND a.userid <> b.userid
        AND a.showid = b.showid
)
GROUP BY a.userid
HAVING COUNT(1) > 4

Is that about right?

For the second part of the question, as I understand it the tables are defined as:

create table shows
(   showid  int not null primary key
,   showname    varchar(10) not null
,   showimage   varchar(10)
,   x int default 0 not null );

create table ratings
(   userid  varchar(200) not null
,   showid  int not null
        references shows(showid)
,   rating  int not null
,       primary key (showid, userid) );

If that's the case I see no particular reason why the insert in ratings is slow, btw what is slow?

Some ideas for trying to pinpoint the problem, make a copy of the ratings table, say ratings2, drop the foreign key and see how that affects the insert performance


SQL Anywhere 17 - Database server connection limit exceeded I set up a new SQL Anywhere 17 Database in Sybase Server. I've got a lot of clients that should be able to connect to the Database over ODBC. The ODBC works fine but as soon as the third user tries to connect i get the following message:

I've tried select @@max_connections and got 32'766.
Also when I execute the folowing statement I get the following result:
SELECT
PROPERTY('ServerEdition') as ServerEdition,
PROPERTY('IsNetworkServer') as IsNetworkServer,
PROPERTY('LicenseType') as LicenseType,
PROPERTY('LicenseCount') as LicenseCount,
PROPERTY('MaxConnections') as MaxConnections
FROM dummy

Does anyone have any idea what I can cause this problem?

You need to use the dblic.exe utility to apply your licence key to the dbsrv17.lic file. See the docs for details.
Max connections is nothing to do with how your server is licenced. You could in theory set up thousands of connections from one network location, and that would use one licence - you want connections from lots of different network seats, so you need more licences. Alternatively you can buy a core based licence.


Create a table with all the columns of inner join of two other tables without copying data I have two tables Table1 and Table2 with 10,000 and 15,000 rows each. I want to create a table Table3 that can contain all the data of inner join of Table1 and Table2, yet is empty. I tried something like this:

CREATE TABLE Table3
(LIKE Table1 INNER JOIN Table2 ON Table1.col11 = Table2.col21);

What is the best solution I can have?

CREATE TABLE your_table AS your_select WITH NO DATA;

https://www.postgresql.org/docs/current/static/sql-createtableas.html


Repeatedly emptying a table, keeping the table itself I have been using "delete from table_xyz" to delete everything in the table. Would it be better to instead use truncate? What difference does it make?

My short answer is:

Use truncate if you do not see any errors (because truncate cannot be used in many scenarios, such as the table is referenced or the table is in replication, but you will get error in such cases)

The advantages of truncate (over delete esp. in your repeated deletion case) are:

fast as it does not involve big overhead of transaction logging.
much smaller footprint on database log.

Hope this helps !


Script component source won't load when click "Edit Script..." I'm working on an SSIS package in Visual Studio 2013. For any script component throughout the package, if I double-click on it and then click the "Edit Script..." button, Visual Studio 2012 opens but no editor window loads. It's just an empty window like you closed all documents. The Solution Explorer shows items, but notably "ScriptComponent" is an empty folder:

If I try to open any of the files (BufferWrapper.cs, ComponentWrapper.cs, main.cs) in the Solution Explorer, I get a popup error box saying:

Anyone have any clue what's going on here? FWIW, I have tried closing all instances of Visual Studio and reopening my solution with the SSIS package. The same behavior persists.

I stumbled across this blog post, which while the author didn't have the exact same problem as me, our issues seemed similar enough that his fix might work for me to. I downloaded the SSDT-BI 2012 package and installed it, and lo and behold, things work fine again. I'll just not even consider that somehow things worked fine for a year or more without downloading and installing SSDT-BI 2012, so I might be able to retain some portion of my sanity.


How to shrink large LDF I have a client with a SQL2016 DB with a 100Gb MDF and a 4Gb LDF. The DB is set to Simple.
I can't get it done to shrink the LDF to a minimum. How can I shrink the LDF to a normal level?
Does this setup and file sizes effects performance?

Before attempting to shrink the logfile, you need to understand if it's a problem or not. It probably isn't.
The log is that size most likely because it needed to grow to that size, meaning that if you shrink it, it will likely grow back to that size in the future - and that log growth will momentarily slow whatever transaction is causing it to grow.
With it only being 4GB and in SIMPLE recovery, I would recommend simply leaving the log as it is, and perhaps adjust the auto growth to fixed 1GB chunks.
If you simply must shrink the log, you can do so with DBCC SHRINKFILE, using the TRUNCATEONLY option. Example:
DBCC SHRINKFILE (Database_log_file_name, 1);
This will truncate the file all the way down to the last open transaction which, if the database is idle or mostly idle, will be the whole thing. But first make sure you've set your auto-growth as described above, and you might even want to re-size the log to what you think you need.
But again...4GB on a 100GB database is not a big deal. I'd just let it stay there.


MySQL InnoDB restoration from hardware failure One of my server went down due to old hardware failure on a old Dell server that's over 10 years old. I was able to recover the files and got it to a local machine.

I want to restore a MySQL instance from InnoDB storage engine.

I have ib_logfile0, ib_logfile1, ibdata1 and *.frm files. How would I go about it? It would conclude restoring the databases, including the mysql database and all the other databases that were already there? how would I go about it?

I did it the easy way of starting a Windows MySQL 5 instance and coping the data files over. I was able to mysqldump to get what I needed.


Am I Setting Up My Database Correctly? Or Am I Thinking About Things The Wrong Way? So I'm having a little self-doubt as to whether or not I am setting up this database I am working on correctly or not.  This is the first large-scale database project I have ever worked on, and I'm doing I've been tasked with doing it alone.  So you know, no pressure at alllllllll.

This question isn't about whether or not I am setting up my tables correctly.  I at least have enough confidence in myself to say I know how to create proper relational database tables, and how to normalize them properly.

What I am concerned with are in facts things like Views, and Stored Procedures.

Quick synopsis of what the project is.  I am retrofitting the company database, updating it to a new relational model.  I need to set up an entirely new database structure from scratch for all the data, and then alongside the database create a VB.Net program that employees can use to interact with the database in various ways.

You see I've basically set up the database so that the Tables act as where all the data is stored, and the Views act as where all the data is retrieved.  I figured that setting it up this way makes it so that it is easier to select data that needs to be shown to people using the .Net app since Views already contain all the proper Joins for all the data.  Sure if you need to select just one piece of data from a table, you can do a simple table query, but for large data sets I figured Views were the way to go.  Was I correct in thinking this?

And then there are the Stored Procedures.  Essentially if the VB.Net project is doing anything variable related for data collecting, I am just setting up a stored procedure within the database to take those variables and work with internal SQL script.

For example, let's say I need to select everything from my PartsData View that has a specific supplier.  Instead of creating a large query string in VB.Net and then passing that String as an SQL Command, I am instead just passing the supplier name as a variable to a 'PartSupplier' Procedure on the database which spits back the information I need.

To me this feels like it just makes more sense to do it this way, as opposed to just constructing long String queries in VB.Net and then throwing them at the database.  Part of what isn't helping my confidence in my approach is that my boss seems to think I'm wasting time and being stupid for doing things my way. :\

I know this isn't exactly something anyone can give me a 100% answer on because I know that you can't give me something like that without knowing all the information.  But am I on the right track here?  Are there some obvious flaws with my logic you can easily see?

Views: Your approach is a very widely-accepted practice. The benefits are well-documented: https://www.c-sharpcorner.com/blogs/advantages-and-disadvantages-of-views-in-sql-server1

Stored Procedures: Also, as you described it, widely-practiced (the disadvantages are notable, too) http://sqltechtips.blogspot.com/2015/11/stored-procedure-benifits.html

In my experience it's common to see views and stored procedures used for the reasons stated, and they behave as advertised.

Whatever method you choose, I strongly advise consistency, as it can be frustrating to have to look in many places for business-rule implementation: code with embedded SQL, stored procedures, views, ORM...  try to apply some systematized implementation to limit the number of places you have to look for constraints, for example - it's possible to put them in any/all of the places I just listed.


How to accomplish local joins between facts and dimensions? A fact table typically contains a composite primary key made up of the keys to all the dimensions that define its granularity.  if we distribute the table by this key, it cannot possibly accomplish a local join to any of the dimensions to which it is bound because those tables are distributed by their own PKs, which won't match the hash generated for the fact.

I'm guessing that if dimensions are small it won't matter much, but if the dimension is large e.g. a security master table with millions of CUSIPs and other identifiers, how can local joins be accomplished?

what is the standard approach to selecting a distribution key for facts?

Your distribution approach by the composite primary key will create a distribution with low or no skew. That's great but, as you point out, it comes with some costs in other areas. In practice even distribution is often a secondary concern. Distribution tends to be by the largest commonly used join or the most common aggregation. In the case you describe this sounds to be the security descriptor dimension.

Data distribution in a MPP database aims to optimise two metrics: minimising the movement of data and making the use of the all the available hardware performance in the cluster. Minimising data motion is achieved by co-locating data for large join and aggregate operations. Using all the hardware to get the best performance for a query requires that the data is evenly distributed and that typical queries will not be working on data on a single node. So when picking a distribution key to minimise data motion distribution it's not only important to minimise skew but also to avoid distributing by a field which is common used in query predicates.


VARCHAR NULL storage I read somewhere online and in a Microsoft Press book there is a field declaration attribute that can be used to indicate NULL VARCHAR values should not consume space at the leaf-level instead of consuming a few characters per leaf.

Could someone help me identify where that attribute is?

I cant remember what the Attribute keyword is but I remember seeing it because of the value of saving the empty 2 bytes and make it a 0 byte storage for no data.

I believe you are talking about SPARSE columns.


How do DELETE / TRUNCATE affect table / tablespace? I am piggybacking off of this question, but asking it from the Oracle perspective.

Specifically, how does a DELETE/TRUNCATE affect the size of the table and/or tablespace?

From Oracle Truncate by Dan Morgan:

Note: Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.
Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table (if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course).
By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.

Note that by default, TRUNCATE drops storage even if DROP STORAGE is not specified.


how to overcome these type of issues where null value inserted into primary key not null column in table how to overcome these type of issues where null value inserted into primary key not null column in table

As was stated in the comment

  You're not inserting a NULL value, you're inserting ""

If you want to prevent an empty string ("") and NULLs from being used as ai_name, you need a trigger. In your particular case, you need a BEFORE INSERT trigger:

DELIMITER $$

CREATE TRIGGER check_for_blank_ai_name
BEFORE INSERT ON actionitems
FOR EACH ROW
BEGIN
  DECLARE dummy INT;
  IF ( IFNULL(NEW.ai_name,'') = '' ) THEN
    SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
  END IF;
END $$

DELIMITER ;

The line of code

SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';

will break. That's by design. This will prevent the INSERT from occurring.

I have written triggers that break midstream before in my other posts:

Jun 09, 2013 : BEFORE INSERT trigger in MySQL
Dec 11, 2012 : Comparing dates in a BEFORE INSERT trigger
Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

Give it a Try !!!


PostgreSQL: Another seq scan where an index scan is expected Here's the query:

SELECT DISTINCT ON(points.track_id)
  participants.contest_number,
  points.track_id,
  points.lat,
  points.lon,
  points.alt,
  points.timestamp,
  points.h_speed,
  points.v_speed,
  points.distance
FROM
  tracks_competitiontrackpoint points
  JOIN tracks_competitiontrack tracks ON points.track_id=tracks.track_ptr_id
  JOIN competitions_contestparticipant participants ON tracks.participant_id=participants.id
WHERE
  tracks.task_id = 24 AND points.timestamp <= 1409663400 AND tracks.online = true
ORDER BY
  points.track_id, points.timestamp desc, points.id;

Query plan:

Unique  (cost=103411.24..104217.95 rows=527 width=38) (actual time=2507.814..2558.081 rows=113 loops=1)
  ->  Sort  (cost=103411.24..103814.60 rows=161342 width=38) (actual time=2507.805..2541.076 rows=82022 loops=1)
        Sort Key: points.track_id, points."timestamp", points.id
        Sort Method: external merge  Disk: 3984kB
        ->  Hash Join  (cost=69.66..85041.85 rows=161342 width=38) (actual time=2253.401..2408.140 rows=82022 loops=1)
              Hash Cond: (points.track_id = tracks.track_ptr_id)
              ->  Seq Scan on tracks_competitiontrackpoint points  (cost=0.00..73678.24 rows=2581475 width=34) (actual time=0.711..1398.816 rows=2575197 loops=1)
                    Filter: ("timestamp" <= 1409663400)
              ->  Hash  (cost=69.10..69.10 rows=45 width=8) (actual time=1.831..1.831 rows=122 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 5kB
                    ->  Hash Join  (cost=12.60..69.10 rows=45 width=8) (actual time=0.522..1.738 rows=122 loops=1)
                          Hash Cond: (participants.id = tracks.participant_id)
                          ->  Seq Scan on competitions_contestparticipant participants  (cost=0.00..48.74 rows=974 width=8) (actual time=0.008..0.679 rows=976 loops=1)
                          ->  Hash  (cost=12.04..12.04 rows=45 width=8) (actual time=0.383..0.383 rows=122 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 5kB
                                ->  Bitmap Heap Scan on tracks_competitiontrack tracks  (cost=5.30..12.04 rows=45 width=8) (actual time=0.194..0.297 rows=122 loops=1)
                                      Filter: (online AND (task_id = 24))
                                      ->  Bitmap Index Scan on tracks_competitiontrack_online  (cost=0.00..5.29 rows=139 width=0) (actual time=0.144..0.144 rows=159 loops=1)
                                            Index Cond: (online = true)
  Total runtime: 2559.586 ms

Or highlighted results: http://explain.depesz.com/s/ENi

I use DISCTINCT ON to query the latest result for each tracks_competitiontrackpoint.track_id ordered by tracks_competitiontrackpoint.timestamp. Index is enabled on tracks_competitiontrackpoint.track_id, tracks_competitiontrackpoint.timestamp, tracks_competitiontrack.participant_id, tracks_competitiontrack.task_id - pretty much everything that. Still there'a sequential scan for two tables, one of which is taking a lot of time. Could you please explain why?

Since you are on 9.3, can you try this rewriting:

SELECT
    participants.contest_number,
    points.track_id,
    points.lat,
    points.lon,
    points.alt,
    points.timestamp,
    points.h_speed,
    points.v_speed,
    points.distance
FROM
    ( SELECT track_ptr_id, participant_id
      FROM tracks_competitiontrack
      WHERE task_id = 24
        AND online = true
    ) AS tracks
  LEFT JOIN
    competitions_contestparticipant AS participants
      ON tracks.participant_id = participants.id
  JOIN LATERAL
    ( SELECT p.*
      FROM tracks_competitiontrackpoint AS p
      WHERE p.track_id = tracks.track_ptr_id
        AND p.timestamp <= 1409663400
      ORDER BY p.track_id, p.timestamp desc, p.id
      LIMIT 1
    ) AS points ON TRUE
ORDER BY
    points.track_id ;

I think that with indexes on tracks (task_id, online, track_ptr_id, participant_id) and on pooints (track_id, timestamp desc, id), it would preform better, assuming that the condition (WHERE task_id = 24 AND online = true) restricts to a small number of rows.


How do you figure out what planner cost constants to use in Postgres? I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I am not an expert at all in this stuff.

I've noticed that the manual mentions a few times that a good way to improve performance is to use better cost constants for the query planner, but it also says that there is no simple way to figure out what cost constants to use.

I think that the constants might be problematic right now, since estimated costs don't seem to be a steady multiple of actual execution time - it varies from 30x to about 600x, even after running VACUUM ANALYZE recently. (I don't know  whether this is a valid way to check try to see if the constants are set well, correct me if I'm wrong on that)

So, I'd like to set better constants for the query planner. How do I go about doing this? Should I just tweak things up and down at random until it seems faster, or is there anything I should do that's more formal? Are there any guidelines based on hardware or anything?

By the way, if the answer is "Don't worry about it, other things will be way more important to improve first," that's fine - for my actual real-world case, I'll work on other stuff. But for the sake of others, it would still be good to know how one would improve the constants if everything else has been improved.

First, the costs are not supposed to directly relate to the execution time.  They're strictly relative; a plan which costs more should take longer to actually execute.  You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time.

For 99% of users, there's only three cost constants they care about at all:

effective_cache_size: set this to 75% of available RAM on your system.  Done.

random_page_cost: if you're using high-end SAN/DAS, lower this to 2.0.  If you're using SSDs or on Amazon, lower it to 1.5.  Otherwise, leave it alone.

effective_io_concurrency: if you're on Linux and have a drive array, raise this to the number of drives (or mirrored pairs) the database is stored on, maximum 4.

Tinkering with the other cost constants is not recommended unless you have a full performance test setup to determine if the result is really beneficial or not.


How set balancing as main task for sharder MongoDB cluster? I have a big collection (over 400 millions records) which I want to divided between 4 shards. I created infrastructure (4 shards, config server, mongos) and all work well (balancer is working and data is being moved). It has being worked greater than 15 hours and working now. My shards have 2662, 77, 77, 77 chunks. It is too slow!
As I know balancer try to keep mongo available. But now nobody use db and my main task is to finish balancing asap.
Could anybody know any command which set balancing as the most task?

My shards have 2662, 77, 77, 77 chunks.

If you are creating a new sharded collection and want to minimise the time to re-balance a large amount of data, the recommended approach would be to pre-split chunks in an empty collection before inserting the data. In your example, you would have pre-split based on the data distribution so each shard would have an equal number of chunks representing about 1/4 of the data.

Assuming you have chosen a suitable shard key for data distribution going forward, there should not be a significant number of regular chunk migrations unless you are adding or removing shards.

  Could anybody know any command which set balancing as the most task?

If your shards are backed by replica sets you can make the balancer more aggressive by disabling _secondaryThrottle, which reduces the write concern used for documents migrating between shards. By default, _secondaryThrottle is true which is equivalent to a {w:2} write concern: each document move during chunk migration propagates to at least one secondary before the balancer proceeds with the next document. In MongoDB 3.0+, there is also an option to configure an explicit writeConcern for the secondaryThrottle operation.

For example, to disable _secondaryThrottle from a mongos shell:

db.getSiblingDB('config').settings.update(
   { "_id" : "balancer" },
   { $set : { "_secondaryThrottle" : false } },
   { upsert : true }
)

Balancer setting updates will not take immediate effect if there is currently a balance round / migration in progress, so you may want to disable & re-enable the balancer.

For normal usage the default _secondaryThrottle behaviour is recommended to ensure documents have propagated and reduce the impact of balancing. If your replica sets have more than 3 members, you could also consider increasing the writeConcern to majority (see: Write Concerns for Replica Sets).


primary_key problem in MySql view I have 3 tables. and I want to create a view. There is no problem about generating SQL query and creating a view.

But when I want to use this view in my web app, it says me (LIKE THIS):

  Your view's column doesn't have a primary key

So I want to set a PK to a column in my view.

Please help. Thanks.

You can't have a PRIMARY KEY on a VIEW. See here, here . You have to put PRIMARY KEYs and INDEXes on the underlying tables. A VIEW is a stored query - you can't have KEYs on a query.


How to create a function in MySQL that will return a random bool So far I've got this:

CREATE FUNCTION IS_ROLEMEMBER (@Rolename VARCHAR(255), @Username VARCHAR(255))
RETURNS BINARY AS
RETURN (
 SELECT (FLOOR(RAND() * 10) % 2) AS BINARY
);

But that gives me "wrong syntax by 'RETURN ( SELECT (FLOOR(RAND() * 10) % 2) AS BINARY)'"

ignore the paramters and function name, I want this for testing

It looks like you had a couple of errors. Try

CREATE FUNCTION IS_ROLEMEMBER (Rolename VARCHAR(255), Username VARCHAR(255))
RETURNS BINARY
READS SQL DATA
RETURN
(
    SELECT (FLOOR(RAND() * 10) % 2)
);

The main errors I saw were:

@Rolename and @Username - no need to use @

RETURNS BINARY AS - no need for AS

I included the READS SQL DATA line as this caused an error on my side otherwise. I believe this is related to replication, so depending on your set up you may not need it.


How to join SQL tables where value is Between min and max value I am currently looking to join two tables together based on an IP and IP range. Both the IP and Range are currently stored as BIGINT.

For example we have a table MasterData with IPs in integer format [IPInteger] and we have another table called IPInformation which has all IP data but also has [start_ip_int] and [end_ip_int] range.

What I would like to to is join all the data in MasterData and All the data in the IPInformation based on the IPInteger being within the range of [start_ip_int] and [end_ip_int] on the IPInformation table.

Thanks in advance for any assistance you can provide.

Following is the SQL Server version in steps to work on IPs Range this might help you to convert in SqL

Step 1: Create function [usp_stringpart] in your Database

Create By: Aasim Abdullah
Description: Function takes a string and section number of string which
is required and separator, which separates different
sections of given string

Create FUNCTION [dbo].[usp_stringpart]
    (
      @InputString NVARCHAR(1000),
      @Section SMALLINT,
      @Separator NCHAR(1)
    )
RETURNS NVARCHAR(1000)
    BEGIN
        DECLARE @StartPosition INT,
            @EndPosition INT,
            @Cycle INT,
            @ResultString NVARCHAR(1000)
        SELECT  @Cycle = 0, @StartPosition = 0
        WHILE @Cycle < @Section - 1
            BEGIN
                SELECT  @StartPosition = CHARINDEX(@Separator, @InputString,
                                                   @StartPosition) + 1
                IF @StartPosition = 1
                    SELECT  @Cycle = @Section
                ELSE
                    SELECT  @Cycle = @Cycle + 1
            END
        SELECT  @EndPosition = CHARINDEX(@Separator, @InputString,
                                         @StartPosition)
        SELECT  @ResultString = LTRIM(RTRIM(SUBSTRING(@InputString,
                                                      @StartPosition,
                                                      CASE @EndPosition
                                                        WHEN 0
                                                        THEN ( LEN(@InputString) + 1 )
                                                             - @StartPosition
                                                        ELSE ( @EndPosition - @StartPosition )
                                                      END)))
        RETURN @ResultString
    END

Step 2: Add Column to save integer value of IPs

ALTER TABLE TableContainIPs ADD IPIntegers BIGINT

Step 3: Convert IPs to Integers

UPDATE TableContainIPs
SET  IPIntegers  = CAST(
                        (CAST( TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),1,'.')AS BIGINT) *100000000000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),2,'.') AS BIGINT) *100000000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),3,'.') AS BIGINT) *100000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),4,'.')AS BIGINT) *100)
                       AS BIGINT)
 FROM TableContainIPs

Step 4: Get IPs

select P.IP, S.*
FROM  TableContainIPs P
inner JOIN IPLibrary S
ON P.IPIntegers BETWEEN s.IPasINTFrom AND s.IPasINTTo


Can't optimize a nested query using indexes There are two tables containing hundreds of thousands of records: client and task. It's necessary to query the following:

SELECT
  id,
  (SELECT status
   FROM task t
   WHERE t.client_id = c.id AND t.status != 2
   ORDER BY task_date DESC
   LIMIT 1) AS task_status
FROM client c;

This query takes about five minutes, which is unacceptable. So, I tried to create indexes in the following ways:

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (status, client_id);

  execution: 1m 1s 475ms, fetching: 21ms

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (status, client_id, task_date DESC);

  execution: 1m 3s 269ms, fetching: 34ms

As you can see, the result is not impressive. Another one approach isn't correct, but it helped to achieve the expected result:

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (client_id, task_date DESC)
  WHERE status != 2;

  execution: 229ms, fetching: 36ms

I can't use this version because similar subqueries used in our system will get an incorrect result because of WHERE status != 2 clause, which specific only for a single query.

I would be grateful for help on query optimization in such cases.

Thanks.

Your best simple index is probably going to be on (client_id, task_date, status).  client_id should go first, because it is the only one used with an equality operator.  Including status will only help for index-only scans, so it depends on how well vacuumed task is and your version of PostgreSQL.

The partial index will not lead to incorrect results.  Queries that cannot correctly use the partial index, will not use it.  So if WHERE status != 2 is the most commonly queried case, or the most time-sensitive one, go ahead and make the partial index.


Is there a way to use my Access accdb or accde file (Created on the desktop) on an ipad? It would need to be able to read [AND write]***redacted. I only need it to read info. I can store the database either on the network or on the cloud (Microsoft Azure). Is there an app or another way to be able to use my split accdb or accde file on the ipad?

Thanks.

As far as I know - NO.

On iMac you can use emulator software, but on iPad there is no such possibility.


Mongodb - Error after turning fork mode on I am working on installing mongodb on remote server I managed  to install everything and tested it.

But when I tried to enable fork mode to make it run in the background via the command

mongod --config  mongod.config

mongod.config

systemLog:
 destination: file
 path: "etc/mongod.log"
 logAppend: true
storage:
 dbPath: "/home/peter/data/db"
 journal:
  enabled: false
processManagement:
 fork: true
net:
 bindIp: 127.0.0.1
 port: 27017
setParameter:
 enableLocalhostAuthBypass: false

this command is run successfully but when trying to run mongo
I get this error

MongoDB shell version v3.4.9
connecting to: mongodb://127.0.0.1:27017
2017-09-30T01:31:07.048-0700 I -        [thread1] Invariant failure !driverName.empty() && !driverVersion.empty() && !osType.empty() && !osName.empty() && !osArchitecture.empty() && !osVersion.empty() src/mongo/rpc/metadata/client_metadata.cpp 350
2017-09-30T01:31:07.048-0700 I -        [thread1]

***aborting after invariant() failure

2017-09-30T01:31:08.207-0700 F -        [thread1] Got signal: 6 (Aborted).

  Note: everything is working fine without fork mode

Any help will be appreciated.

update

log file

2017-10-01T08:07:05.786-0700 I CONTROL  [initandlisten] MongoDB starting : pid=488699 port=27017 dbpath=/home/peterw3/data/db 64-bit h$
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] db version v3.4.9
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] git version: 876ebee8c7dd0e2d992f36a848ff4dc50ee6603e
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] allocator: tcmalloc
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] modules: none
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] build environment:
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten]     distarch: x86_64
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten]     target_arch: x86_64
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] options: { config: "etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 $
2017-10-01T08:07:05.813-0700 I -        [initandlisten] Detected data files in /home/peterw3/data/db created by the 'wiredTiger' stora$
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten]
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTig$
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] Detected WT journal files.  Running recovery from last checkpoint.
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] journal to nojournal transition config: create,cache_size=15451M,session_max=2$
2017-10-01T08:07:05.914-0700 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=15451M,session_max=20000,eviction=(t$
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten]
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten]
2017-10-01T08:07:06.003-0700 W FTDC     [initandlisten] Error checking directory '/sys/block': No such file or directory
2017-10-01T08:07:06.003-0700 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory '/home/peterw3/d$
2017-10-01T08:07:06.005-0700 I NETWORK  [thread1] waiting for connections on port 27017
2017-10-01T08:07:10.193-0700 I NETWORK  [thread1] connection accepted from 127.0.0.1:33231 #1 (1 connection now open)
2017-10-01T08:07:10.209-0700 I -        [conn1] end connection 127.0.0.1:33231 (1 connection now open)

Use full path with path and dbPath. So, both "should" start with /. When you are in fork, there is no "base" directory.


How to use and optimize subquery on 100 million rows What I'm trying to do is running a job on more than 100 million domains which haven't processed before.

I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm getting a list of domains from domain table by checking their status on "domain_setting". At first it was working fine but now approximately 10 million domain processed and it's getting slower.

I know that my server is crappy for this kind of database, it has only 8 GB of memory and it's also shared with Apache web server (150 concurrent connections), some other small databases and some lightweight PHP applications. My innodb_buffer_pool_size is only 1 GB.

Anyway, I would like to learn if there is a better way or thoughts to write this query more efficiently or any other approach for doing this job and speed up the process. Here is my SQL query:

SELECT * FROM domain
WHERE domain_ID NOT IN (SELECT domain_ID FROM domain_setting) OR domain_ID IN (SELECT domain_ID FROM domain_setting WHERE is_keyword_checked = 0)
LIMIT 0,10000;

I must get domains which are not exist in domain_setting table OR has "is_keyword_checked = 0" value in domain_setting table. How can I optimize this query?

Thanks in advance :)

EDIT:
I can rewrite my query with LEFT JOIN as

SELECT *
FROM domain
LEFT JOIN domain_setting ON domain_setting.domain_ID = domain.domain_ID
WHERE domain_setting.is_keyword_checked = 0 OR domain_setting.is_keyword_checked IS NULL
ORDER BY domain.domain_ID ASC
LIMIT 0,10000;

but the main problem here seems full table scan. I can run it backwards by ORDER by domain.domain_ID DESC for recent added records and I can get them very fast. But still needs to scan full table if you didn't run it for a while and if recent records became more than millions of rows. So, is there any way to avoid full table scan for such work?

Since is_keyword_checked can take only two possible values, 0 or 1 (and it's not nullable), you can try the rewriting. There is no OR and the index on (is_keyword_checked) will be used:

SELECT *
FROM domain
WHERE domain_ID NOT IN
      ( SELECT domain_ID
        FROM domain_setting
        WHERE is_keyword_checked = 1
      ) ;

or with a NOT EXISTS subquery - which should have similar execution plan:

SELECT d.*
FROM domain AS d
WHERE NOT EXISTS
      ( SELECT *
        FROM domain_setting AS ds
        WHERE ds.is_keyword_checked = 1
          AND ds.domain_ID = d.domain_ID
      ) ;


Full Outer Join not working as expected I want to make a view from these tables. The first inner join works as expected and brings a new column with AuspraegungID  from which I want to connect to the  ArtikelVarianten table

At the moment I get a view with all ID that have a counterpart in ArtikelVarianten. But some articles dont have that ID (its set to zero, and no match in the target table) so they are ommitted in the current selection
But I want to include them, and I thought thats where the full outer join comes in.

Select Distinct [A].[Artikelnummer],
  [A].[Bezeichnung1],
  [S].[AuspraegungID],
  [A].[USER_SkuDe] As [Parent SKU],
  [AV].[USER_SkuDe] As [Varianten SKU]
From [dbo].[KHKArtikel] [A]
  inner Join  [dbo].[LBSysStock] [S]
    On [S].[Artikelnummer] = [A].[Artikelnummer]
  Full Outer Join [dbo].[LBSysArtikelVarianten] [AV]
  On [S].[AuspraegungID] = [AV].[AuspraegungID]
    And [AV].[Artikelnummer] = [A].[Artikelnummer]

Firstly, I'd encourage you to read the blog post Be Careful When Mixing INNER and OUTER Joins.

  If you use Derived Tables (or nested joins) the way you'd use
  parenthesis in a math or boolean equation to express your logical
  order of precedence, you can make your code cleaner and more readable
  and you can ensure that you get back the exact results you intended.

I don't know if this will solve your problem, as you did not post any table definitions or sample data, but I'd try changing your query to encapsulate the inner join into a derived table and then join the derived table using the FULL OUTER JOIN.  Here is an example (not tested):

SELECT DISTINCT [ij].[Artikelnummer]
    ,[ij].[Bezeichnung1]
    ,[ij].[AuspraegungID]
    ,[ij].[USER_SkuDe] AS [Parent SKU]
    ,[AV].[USER_SkuDe] AS [Varianten SKU]
FROM (
    SELECT [A].[Artikelnummer]
        ,[A].[Bezeichnung1]
        ,[A].[USER_SkuDe]
        ,[S].[AuspraegungID]
    FROM [dbo].[KHKArtikel] [A]
    INNER JOIN [dbo].[LBSysStock] [S]
        ON [S].[Artikelnummer] = [A].[Artikelnummer]
    ) ij --inner join
FULL OUTER JOIN [dbo].[LBSysArtikelVarianten] [AV]
    ON [ij].[AuspraegungID] = [AV].[AuspraegungID]
        AND [AV].[Artikelnummer] = [ij].[Artikelnummer]


FullTextSearch indexing/searching words with special characters I'm trying to query FullTextSearch indexed column for hashtags

SELECT Bio FROM Users
WHERE CONTAINS (Bio,'promoter')

SELECT Bio FROM Users
WHERE CONTAINS (Bio,'#promoter')

both results returns the same record

promoter of #Art

I tried also to put double quotes around the searched term "#promoter" but same result.

The same problem when I'm searching for '@something'

What I need to do in order FullTextSearch to index the special characters?

Hmm I'm afraid '#' counts as punctuation and SQL Server Full-Text Indexing was invented before Twitter.  There are however a couple of approaches:

1) Pre-processing

Use the full-text functions to fetch most of the data then refine it with Like, eg

SELECT Id
INTO #tmp
FROM dbo.Users
WHERE CONTAINS ( Bio, '#promoter' )

SELECT *
FROM dbo.Users u
WHERE u.Bio Like '%#promoter%'
AND EXISTS ( SELECT * FROM #tmp t WHERE u.Id = t.Id )

Whilst you could use CONTAINSTABLE to do the same thing in one query, you can't necessarily guarantee what order the plan will take - and why
would you, T-SQL is after all declarative.  I prefer to be explicit, but if you find performance acceptable and/or data volumes are low, combine the two, eg

SELECT *
FROM dbo.Users u
    INNER JOIN CONTAINSTABLE ( Users, Bio, '"#promoter"' ) ct ON u.Id = ct.[key]
WHERE u.Bio Like '%#promoter%'

I did some simple tests on a 1 million row table and it seemed to perform ok.  YMMV.

2) Tokenised version of column

Store an alternate version, eg a computed column where '#' is replaced with the literal text 'hashtag', something like this:

-- Add computed column
ALTER TABLE dbo.Users ADD cleanBio AS REPLACE ( Bio, '#', 'hashtag' )

-- Add the computed column to full-text index
ALTER FULLTEXT INDEX ON dbo.Users ADD ( cleanBio )
GO

SELECT * FROM dbo.Users
WHERE CONTAINS ( cleanBio, 'hashtagpromoter' )

Obviously there is some storage overhead here.

Consider trying these approaches with your data and let me know how you get on.