I am trying to write a search query on MYSQL to find the users I am using MYSQL for a given data model where I have and entity 'concerts' connected to the entity 'Attended' which is connected by the entity 'People'.

Here I am given a one to many and many to one relation respective to the entities I mentioned. I am asked to find the People.Firstname who haven't attended a single concert. I tried implementing this using the INNER JOIN of Concerts and Attended to People.
My Search Query is as follows :

Select People.FirstName from People
    INNER JOIN Attended
        on People.Id = Attended.Customer
    INNER JOIN Concert
        Where Attended.Concert != Concert.Id
            GROUP BY People.FirstName

I get bunch of First names out but Idk for sure if the query I made gives the right output.

People who didn't attend any concert should not be reference in your Attended table by definition.

So you could maybe try to do your requests with a left or right join and put a criteria on records with null id, maybe something like this:

SELECT People.FirstName from People
LEFT JOIN Attended on Attended.Customer = People.ID
WHERE Attended.Customer is null;

This is how I thought my query :

By using a left join, I'll get all records from left table (here People), with matching record from right table (here Attended).

The missing records (i.e. records on which there is no match with given on clause) are replaced by null value - thus, if I can't find an Attended record for my people ID, I'll get Null for Attended Customer column.

My WHERE clause filter all Attended record which are not null, leaving me with only people who don't have matching Attended record - these are people who don't go to concert.


how to delete the file which is open in bcp.exe I have run the bcp command in query window to generate the tsv file. In between I cancelled the query. I tried to kill the SPID. But it is killed/rollback.

I tried to delete the tsv file in server. It is throwing error as 'the file cant be deleted because it is open in bcp.exe'. please assist to resolve

The easiest way is to restart the machine however that can't be recommended since it is a server and not PC. I would suggest to find the process in the task manager in the process tab and look for anything starting with or similar to BCP and you can do "End Process" or if that doesn't work then "End Process Tree". Once you killed this process, file would be released and will be ready for any I/O operation.

Please let us know in case this helps.


Find exact and partial matches in the tables I have two tables products and users. I need to find exact and partial matches in these tables.

For example, there is a user with the username cat, and there is a product with the name cat. And there are products with the names of a good cat, red cat.

I want to get exact matches and partial matches. That is, get a user and three products.

I tried to do so, but the query returns an empty body.

SELECT P.*, U.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.STORE_ID)
            WHERE
                P.PRODUCT_NAME = 'cat' OR P.PRODUCT_NAME LIKE ('%cat%')
                AND U.USERNAME = 'cat' OR U.USERNAME LIKE ('%cat%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID

How do I get a user and products for the word cat? Partial and exact matches.

use OR instead of AND, also 'cat' is LIKE '%cat% so you do not need to check with = also

SELECT P.*, U.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.STORE_ID)
            WHERE P.PRODUCT_NAME LIKE ('%cat%')
               OR U.USERNAME LIKE ('%cat%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID


Can I install Oracle 12c and NOT use pluggable databases? I've just installed Oracle 12c.   I did not select any of the fancy new options, I just wanted an Oracle instance.   I'm fairly new to Oracle but I was used to 11.2.0.4.

Every command I try now fails and it's always something like "Cannot do [x] on pluggable database", etc.

I just want a normal oracle instance and I want to be able to create users and import my database but every turn I take has some frustrating error having to do with pluggable databases.  I don't care at all about them, and I specifically tried to avoid any install option that looked like it would make a "pluggable" database but apparently it still did.

Is there any way I can just keep life the same in 12c or am I stuck with this entirely clumsy pluggable paradigm?
Thanks very much.

Yes it is possible to use single tenant..
here you go --> Single Tenant Configuration


How to clean up duplicate objects in SYS/SYSTEM I have a sql query like below to check duplicate objects in SYS/SYSTEM.

select OBJECT_NAME, OBJECT_TYPE from
DBA_OBJECTS where OBJECT_NAME||OBJECT_TYPE
in (select OBJECT_NAME||OBJECT_TYPE from
DBA_OBJECTS where OWNER='SYS') and
OWNER='SYSTEM' and OBJECT_NAME not in
('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES',
'DBMS_REPCAT_AUTH');

I have a list of large duplicated objects. My question / problem is : how to get rid of the objects? What is the best way to approach this?

It seems you have run one of cat*.sql scripts as SYSTEM instead of SYS. Not cool.

Alternatives:

Contact support.
...or determine what exactly has been done, and then having the SQL script(s) revert the actions one by one. There is nothing in Oracle to help you, you need to determine by your own means (some old notes? old screenlogs? clues in old .bash_history?).
...or schedule downtime, expdp everything what is relevant in the entire database, re-create, impdp (of course don't impdp SYSTEM schema).
...or if you are unable to proceed with either 1, 2, 3, don't perform DROPs by any heuristics/guesswork. Continue as you are. Nothing really wrong with your database - just some extra objects in SYSTEM (not in SYS, I hope). They could stand in a way of some future action, but they might not, nobody knows. Normally perform cold backup before upgrade. Chances are the upgrade will be successful. Schedule steps 1,2,3 for later.


How to disable case sensitivity in SQL Server 2005 for object name (Table name)? I have created a new database in SQL Server 2005 and execute generated script from the old database.

In that I have a table called MstCity

When I try to execute the following query which has the table name in uppercase MSTCITY

SELECT City FROM MSTCITY WHERE City = 'Junagadh'

I get an error

  Invalid object name 'MSTCITY'

When I execute this SELECT statement it works fine

SELECT City FROM MstCity WHERE City = 'Junagadh'

In the old database both SELECT statement work fine. And both database are in SQL Server 2005 only.

Check the collation on the database. Then consider (but not flippantly) if it should be a CI (Case Insensitive) rather than a CS one.

Also look at the system databases. You may need to do a system rebuild to get it working right if you have built a system to migrate the database to. If your tempdb collation is different from the old box to the new one, you might find yourself with collation errors before too long.


Add New Cluster node I have a Windows Server 2012 Failover Clustering Instance, hosting a SQL Server 2012 Enterprise Cluster, via NODE1 and NODE2.

I need to add a new read-only Replica to this cluster: NODE3, which is a Windows Server 2012 R2.

I've activated SQL Server AlwaysOn to both FCI NODE1 and Node2.

I've tried to add the new node, NODE3, to FCI cluster services, however I'm seeing this error

  cluster node could not join the cluster because it failed to
  communicate over the network

What do I need do to add NODE3 as a read-only replica in FCI?

The terms are being confused sadly which is causing an issue.  There's WCFS (Windows Clustering Foundation Services) which FCI (Failover Cluster Instance) runs on and requires shared disks.  An AlwaysOn AG can also run on WCFS but it uses non shared storage and provides live read only replicas.

In other words A FCI is a shared storage architecture and you fail over from node to node.  FCI doesn't in any way provide a 'read only' solution out of the box.  Usually you will do mirroring+snapshot/log shipping or something else if you only have FCI.  Right now what you are doing is simply adding another failover node, not an AlwaysOn AG.

This is what you are looking for (from the link).  Note the multi node cluster with shared storage and the non shared storage AG:

Right now this is what you're doing. You're simply adding a 3rd node to the shared storage cluster:

This guide works well if you already have a FCI and want to add AG to it.  Just look for how to install an AG.  I would go through the steps for you but there's a lot of steps and you're better off going with an actual guide with screenshots and such.  Do you have somewhere to test this in? Have you tried an Azure instance to test with if not?  I would not recommend doing this live to prod.  Here is some good documentation from Microsoft.  Are you also sure that your code is AG compliant?

If you use MSDTC or cross db transactions you could create a serious problem.

Now the bigger question is are you the person who will be supporting this and is this prod?  It seems like it's important because you're utilizing both FCI and AGs.  I would highly suggest you research FCI and AGs in depth before deploying to prod.  There are tons of resources out there.  I would highly recommend not attempting to push this through on prod with the current understanding.  You might set yourself up for major failure and your HA solution can bring you down.


Recovering mysql innodb table from table.ibd backup file Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server).

Now for a scenario, where some how a table has been accidentally dropped, I thought recovery would be as simple as -

copy .ibd and .frm file from backup to mysql data dir.
alter table tbl_name import tablespace;

On issuing a select, DB throws an error - table does not exist. I understand this might have something to do with ib_logfile?

Any ideas how to go about restoring table in this situation?

InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is not enoght.

Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

Have a look at  Recovering an InnoDB table from only an .ibd file.

Also have a look at  This Detailed explanation  from  @RolandoMySQLDBA.


How to use a table when the user identifier can be one of two fields Recently we've started developing Github like organisation system for our service.

We have both free and payed plans.

There are users. Also there should be the owner for organisation and the owner has to register his credit card for the payed plan.

Owners can invite any users.

We cost each users and owners separately.

For example,

ManA registered with his email ManA@gmail.com
ManA also opened up his organisation named "OrgA"with his email OrgA@gmail.com
I'm struggling with how I can architect the table like Github.

I thought "Pivot table" would be great but it's okay as long as the email is the same. (ManA also opened up his organisation named "OrgA"with the same email as he uses for his personal account. ManA@gmail.com)

Payment info is also same as the email. ( Any users can register another billing info for his own organisations.)

I just wonder how I could implement the good entity relations.

NOTE: This is as the same as I posted on https://stackoverflow.com/questions/38096718/how-to-use-a-table-when-the-user-identifier-can-be-one-of-two-fields.

But maybe here is the right place to post, so as soon as I get some answers, I will delete posts in stackoverflow.

##### Update  ######

Scenario

I am new to databsae design, and I decided to develop to make Github like organisation billing system.
However I am having trouble modeling and normalizing it.

The databse is meant to store data about Credit card info (Actually it will be token though.), Users, Organization, Billing

Business rules

A User can have multiple billing information
A User can join any organizations
A User can be an organizer.
A User has to pay for his own usage.
A User can separate  his creditcard billing information
An Organization always has an organizer
An Organizer has to pay for usages made by users in the group
An Organization has a name and an emai.

The issue

I'm struggling to apply any relations between tables efficiently.
Also the problem is that if the credit_card has changed the billing history will be gone with this implementation.
So I'd love to put Billing relations to Person & Organization, however it will be mess.
Any ideas?

Model

##### Update  2016 Jul 8th######

##### Update  2016 Jul 8th (2)######

When doing this sort of design, I think first about the "things", and then about the "relationships".
The things are:

the people
the credit cards and
the organisation.

The relationships are:

Who owns the organisation.
Who is a member of the organisation.
Which credit card is the owner using to pay the bill

You need an entity for each "thing".
Depending on whether it is a "to one" relationship or a "to many" relationship, you can either define the relationship within an entity or you will need a new entity for the relatonship.

Who owns the organisation: If only one owner, you can put the owner in the organsation entity, otherwise you would need a separate entity.
Who is a member of the organisation: You can have many members of an organisation, therefore you will need a separate entity.
Which credit card is the owner using to pay the bill: Only one credit card would be used therefore you can put the reference to the credit card card in the organisation entity.

So you would have four entities:
Person (a "Thing")
Credit Card (a "Thing")
Organisation (a "Thing", with two relationships)
Organisation_Member (a relationship)

Here are some example columns you could use for the entities

Person Columns:

person_id (primary key, identity)
name
email

Credit Card Columns

person_id
billing_id (start from 1, don't make it an identity)
+credit card information columns (I expect you would encrypt the data, so not store the actual card numbers as text)

(The combination of person_id and billing_id in the above forms the primary key)

Organisation Columns:

org_id
org name
person_id (of the (one) owner - defining one relationship mentioned)
billing_id (of the (responsible) owner - defining another relationship mentioned)

Organisation_Member:

org_id
person_id (of the member)

One more point about email addresses. If you want there to be a separate email address for the organisation, it can be included in the organisation table. I'm not sure I understand why you would need this. Perhaps my answer would be different if I understood what it is for.


What kind of checks should I perform while dealing with stored procedures? CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin

IF (SELECT is_activated from USERS where mat_emp = @mat_emp) = 1
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end
else
print 1
end

or

    CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end

Which one of these queries is considered better (from different aspects) and why? sometimes I feel like doing some check before making the update is not necessary, like in this case for example.

First one will check If the account is unlocked, If yes, it locks it.

While the second one will lock it regardless of the current state of the account.

So, for the user who is using the application that execute this procedure, does it really matter?

You should perform checks to guarantee code correctness, follow the coding standards of your organization, and to make your code understandable to other programmers. I will give you my initial thoughts on your code as an outsider who doesn't know anything about your application. Starting with this one:

CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin

IF (SELECT is_activated from USERS where mat_emp = @mat_emp) = 1
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end
else
print 1
end

What happens if the value of is_activated changes after your SELECT query is run but before your UPDATE query runs? The code pattern that you have here isn't safe under concurrency. It's also not clear what print 1 means here and if it's okay to mix that information with information about row counts (SET NOCOUNT ON isn't present). Perhaps you're looking for an output parameter?

For this code:

CREATE PROCEDURE [dbo].[LockAccount] (@mat_emp int)
as
begin
UPDATE USERS
SET IS_ACTIVATED = 0
where MAT_EMP = @mat_emp
end

In most code around locking I see some kind of error handling or information returned to the caller if a lock can't be taken. For example, if you're taking a lock in order to perform some type of operation that requires exclusive access to the user then allowing many concurrent processes to take locks could cause data integrity issues. Does the caller need to know if a row exists for the @mat_emp input parameter? Should the stored procedure time out if it can't get a lock quickly enough on the USERS table?

It's true that updating a column in a row to the same value does have some subtle effects, but I think that's the wrong way to look at it except for extremely high performance applications. If you want to develop a habit I think that it's best to avoid non-updating updates (when practical) just to avoid surprises. However, the code (including comments) of the stored procedure needs to make the expected outcome of the operation clear. As someone without any knowledge of your application, I can't say if either approach is safe.


Even when user has 'bulkadmin' role, query says user does not have role - SQL Server 2012 I am facing a weird issue with SQL Server 2012.

I have a user (say RS\sqluser) which has bulkadmin role.

Now when i run this query

Select IS_SRVROLEMEMBER('bulkadmin', 'RS\sqluser')

output is 0. But when i execute this query

sp_helpsrvrolemember 'bulkadmin'

I can see RS\sqluser present in the list.

Can someone please help me understand how is the output differing or is it BUG in SQL Server 2012? (Can't be a BUG as same query with different user works fine)

You could have problems whereby the DC is not available, the user is actually a Windows group, or you could possibly have UAC issues. It may even be a permissions issue in AD as regards the account (run xp_logininfo 'RS\sqluser' and see if you get an 0x5 error).

I prefer to directly query the system tables to get that sort of information:

SELECT  1
FROM    sys.server_principals sp
        INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
        INNER JOIN sys.server_principals sp2 ON srm.role_principal_id = sp2.principal_id
WHERE   sp2.name = 'bulkadmin'
        AND sp.name = 'RS\sqluser';


SQL Select taking too much time to execute It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table.

In code:

SELECT
    TempTable.Col1,
    TempTable.Col2,
    TempTable.Col3,
    JoinedTable.Col1,
    JoinedTable.Col2,
    (
        SELECT TOP 1
            ThirdTable.Col1 -- Which is ThirdTable's Primary Key
        FROM
            ThirdTable
        WHERE
            ThirdTable.SomeColumn = JoinedTable.SomeColumn
    ) as ThirdTableColumn1,
    (
        SELECT TOP 1
            ThirdTable.Col1 -- Which is also ThirdTable's Primary Key
        FROM
            ThirdTable
        WHERE
            ThirdTable.SomeOtherColumn = JoinedTable.SomeColumn
    ) as ThirdTableColumn2,
FROM
    #TempTable as TempTable
LEFT JOIN
    JoinedTable
ON (TempTable.PKColumn1 = JoinedTable.PKColumn1 AND
    TempTable.PKColumn2 = JoinedTable.PKColumn2)
WHERE
    JoinedTable.WhereColumn IN  (1, 3)

This is an exact replica of my query.

If I remove the two sub selects, it runs just fine and quickly. With the two sub selects, I  get about 100 records per second, which is extremely slow for this query because it should return almost a million records.

I've checked to see if every table has a Primary Key, they all do. They all have Indexes AND statistics for their important columns, like the ones in those WHERE clauses, and the ones in the JOIN clause. The only table with no primary key defined nor index is the temporary table, but it's not the problem either because it's not the one related to the slow sub selects, and as I mentioned, with no sub selects it runs just fine.

Without those TOP 1 it returns more than one result, and raises an error.

Help, anyone?

EDIT:

So the execution plan told me I was missing an Index. I've created it, and recreated some of the other indexes. After a while, the execution plan was using them, and the query now runs fast. The only problem is I'm not succeeding in doing this again on another server, for the same query. So my solution will be to HINT which index SQL Server will use.

I think in a million records query, you have to avoid things like OUTER JOINS. I suggest you use UNION ALL Instead of LEFT JOIN.
As long as I think CROSS APPLY is more efficient than sub-query in the select clause I will modify the query written by Conard Frix, which I think is correct.

now: when I started to modify your query I noticed that you have a WHERE clause saying:  JoinedTable.WhereColumn IN  (1, 3). in this case, if the field is null the condition will become false. then why are you using LEFT JOIN while you are filtering null valued rows?
just replace LEFT JOIN With INNER JOIN, I guarantee that it will become faster.

about INDEX:

please note that when you have an index on a table, say

table1(a int, b nvarchar)

and your index is :

nonclustered index ix1 on table1(a)

and you want to do something like this:

select a,b from table1
where a < 10

in your index you have not included the column b so what happens?

if sql-server uses your index, it will have to search in the index, called "Index Seek" and then refer to main table to get column b, called "Look Up". This procedure might take much longer than scanning the table itself: "Table Scan".

but based on the statistics that sql-server has, in such situations, it might not use your index at all.

so first of all check the Execution Plan to see if the index is used at all.

if yes or no both, alter your index to include all columns that you are selecting. say like:

nonclustered index ix1 on table1(a) include(b)

in this case Look Up will not be needed, and your query will execute so much faster.


Multicolumn index and performance I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.

The scenario:

PostgreSQL 8.4, table with about one million rows
Values in column c1 can have about 100 different values. We can assume the values are evenly distributed, so we have about 10000 rows for every possible value.
Column c2 can have 1000 different values. We have 1000 rows for every possible value.

When searching data, the condition always includes values for these two columns, so the table has a multicolumn index combining c1 and c2. I have read about the importance of properly ordering the columns in a multicolumn index if you have queries using just one column for filtering. This is not the case in our scenario.

My question is this one:

Given the fact that one of the filters selects a much smaller set of data, could I improve performance if the first index is the most selective one (the one which allows a smaller set)? I had never considered this question until I saw the graphics from the referenced article:

Image taken from the referenced article about multicolumn indexes.

The queries use values from the two columns for filtering. I have no queries using just one column for filtering. All of them are: WHERE c1=@ParameterA AND c2=@ParameterB. There are also conditions like this: WHERE c1 = "abc" AND c2 LIKE "ab%"

Answer

Since you refer to the website use-the-index-luke.com, consider the chapter:

Use The Index, Luke › The Where Clause › Searching For Ranges › Greater, Less and BETWEEN

It has an example that matches your situation perfectly (two-column index, one is tested for equality, the other for range), explains (with more of those  nice index graphics) why @ypercube's advice is accurate and sums it up:

Rule of thumb: index for equality first — then for ranges.

Also good for just one column?

What to do for queries on just one column seems to be clear. More details and benchmarks concerning that under these related question:

Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?

Less selective column first?

Apart from that, what if you have only equality conditions for both columns?

It doesn't matter. Put the column first that is more likely to receive conditions of its own, which actually matters.

Consider this demo, or reproduce it yourself. I create a simple table of two columns with 100k rows. One with very few, the other one with lots of distinct values:

CREATE TEMP TABLE t AS
SELECT (random() * 10000)::int AS lots
     , (random() * 4)::int     AS few
FROM generate_series (1, 100000);

DELETE FROM t WHERE random() > 0.9;  -- create some dead tuples, more "real-life"

ANALYZE t;

SELECT count(distinct lots)   -- 9999
     , count(distinct few)    --    5
FROM   t;

Query:

SELECT *
FROM   t
WHERE  lots = 2345
AND    few = 2;

EXPLAIN ANALYZE output (Best of 10 to exclude caching effects):

Seq Scan on t  (cost=0.00..5840.84 rows=2 width=8)
               (actual time=5.646..15.535 rows=2 loops=1)
  Filter: ((lots = 2345) AND (few = 2))
  Buffers: local hit=443
Total runtime: 15.557 ms

Add index, retest:

CREATE INDEX t_lf_idx ON t(lots, few);

Index Scan using t_lf_idx on t  (cost=0.00..3.76 rows=2 width=8)
                                (actual time=0.008..0.011 rows=2 loops=1)
  Index Cond: ((lots = 2345) AND (few = 2))
  Buffers: local hit=4
Total runtime: 0.027 ms

Add other index, retest:

DROP INDEX t_lf_idx;
CREATE INDEX t_fl_idx  ON t(few, lots);

Index Scan using t_fl_idx on t  (cost=0.00..3.74 rows=2 width=8)
                                (actual time=0.007..0.011 rows=2 loops=1)
  Index Cond: ((few = 2) AND (lots = 2345))
  Buffers: local hit=4
Total runtime: 0.027 ms


Using SQL Server Reporting Services for monitoring database performance I'd like to know whether it is a good idea to use SSRS for db monitoring or not? I want to check different parameters of a db such as Deadlock, page splits, and page reads/sec to improve db performance.
Any comments would be appreciated.

SSRS (SQL Server Reporting Services) isn't a monitoring tool, it's a reporting tool.

You can absolutely use it to develop your own reports, but you'll have to be collecting the data, first. SSRS will not help you collect or analyze the data, but it will be able to present the data in whatever way you like and develop the reports to behave (such as varying parameters, etc.).


improve database design for monitoring application I need assistance with my DB design, I have a monitoring script that produce the following data every minute , and produce stats about virtual machine performance.

Array
(
    [timestamp] => 2015-05-05T18:11:00Z
    [vm_name] => i-2-20-VM
    [memory_internal_free] => 3516900
    [vbd_xvdd_read] => 0
    [vbd_xvdd_write] => 0
    [vif_0_rx] => 0
    [vif_0_tx] => 0
    [memory] => 4294967296
    [vbd_xvda_read] => 0
    [vbd_xvda_write] => 0
    [cpu0] => 0.000100
    [cpu1] => 0.000100
)
Array
(
    [timestamp] => 2015-05-05T18:11:00Z
    [vm_name] => i-2-24-VM
    [memory_target] => 4294967296
    [vbd_xvdb_read] => 0
    [vbd_xvdb_write] => 0
    [vbd_xvda_read] => 0
    [vbd_xvda_write] => 0
    [vif_0_rx] => 0
    [vif_0_tx] => 0
    [memory] => 4294967296
    [cpu0] => 0.000100
    [cpu1] => 0.000100
)

I'm trying to store the following info into my database, I have create single table

I have created table as follows

CREATE TABLE `md_metrics_status` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `vm_name` varchar(50) DEFAULT NULL,
  `memory_target` varchar(255) DEFAULT NULL,
  `vif_2_rx` varchar(255) DEFAULT NULL,
  `vif_2_tx` varchar(255) DEFAULT NULL,
  `memory` varchar(255) DEFAULT NULL,
  `vbd_xvda_read` varchar(255) DEFAULT NULL,
  `vbd_xvda_write` varchar(255) DEFAULT NULL,
  `vbd_xvdd_read` varchar(255) DEFAULT NULL,
  `vbd_xvdd_write` varchar(255) DEFAULT NULL,
  `vif_1_rx` varchar(255) DEFAULT NULL,
  `vif_1_tx` varchar(255) DEFAULT NULL,
  `vif_0_rx` varchar(255) DEFAULT NULL,
  `vif_0_tx` varchar(255) DEFAULT NULL,
  `cpu0` varchar(255) DEFAULT NULL,
  `cpu1` varchar(255) DEFAULT NULL,
  `vif_3_rx` varchar(255) DEFAULT NULL,
  `vif_3_tx` varchar(255) DEFAULT NULL,
  `memory_internal_free` varchar(255) DEFAULT NULL,
  `vbd_xvdb_read` varchar(255) DEFAULT NULL,
  `vbd_xvdb_write` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=162339 DEFAULT CHARSET=utf8

Now this table has grown so large, and searching for records between specific timestamps is very slow.

I'm unable to add an index on timestamp field, because I get many duplicate records, as you can see in the example.

What is wrong with this table design? how can I improve this setup?

I have full control over the database, and over the script that produces the data.

Your input is highly appreciated.

Thank you

Add an index, not a unique key:

ALTER TABLE md_metrics_status ADD INDEX (`timestamp`);

I believe you are using PHPMyAdmin to add the index, and in PMA, unique is the default choice when you want to add an index, and that is why you are getting the duplicate key error.


Performance tuning large mysql databases for zabbix I have been working on tuning a mysql database for zabbix for quite some time now. We do not actually have a DBA to assist with this.

We have a Zabbix server running 16k values per second.
Our database is MariaDB 5.5.38 running galera replication with only two nodes. I will be happy to provide any information needed to assist in tuning.

The issues we are seeing is our zabbix graphs are delayed and our dbsyncers are maxed out. According the the Zabbix performance tuning book and zabbix support personnel our database is not able to keep up with the amount of data being sent its way.

We have 512GB of RAM on the server, in RAID 10 with SSD's, and 32 Cores at 2.59GHz.

The mariadb, zabbix, and apache services are all running on this server.

What can I do to increase the performance of this server. It does not seem like the OS is being over utilized based on iostat, vmstat and other OS performance checking tools.

Thanks in advance.

"running 16k values per second" -- Do you mean "rows inserted per second"?  SELECTs per second?  Something else?

The most important setting for MySQL is innodb_buffer_pool_size, which should probably be 300G for your setup.  innodb_buffer_pool_instances=16.

innodb_flush_log_at_trx_commit is 1 by default.  1 is safer; 2 leads to less I/O.

If you are doing 16K INSERTs per second, there are several techniques to decrease the I/O (which you imply is 'too high').  Batching (doing multiple rows in a single INSERT), putting them a single transaction, using LOAD DATA, or "staging".  Please describe things more.

VARIABLES/STATUS analysis

There were some interesting things.  Here are the highlights:

( innodb_max_dirty_pages_pct ) = 30 -- When buffer_pool starts flushing to disk -- Why so low?  (Probably does not matter)

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((23406029 + 990332255) ) / 769924 = 1316 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?  Increase innodb_max_dirty_pages_pct?

( Created_tmp_tables ) = 300,386,596 / 769924 = 390 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 16,281,268 / 769924 = 21 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Select_scan ) = 22,715,682 / 769924 = 30 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Sort_merge_passes ) = 412,662 / 769924 = 0.54 /sec -- Heafty sorts
-- Increase sort_buffer_size and/or optimize complex queries.

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 1 (and turn on slowlog) -- would help in locating naughty queries.

Threads_running = 45 -- likely to indicate that queries are stumbling over each other

Innodb_buffer_pool_pages_flushed / Questions = 0.49 -- Perhaps indicates buffer_pool is not being an efficient cache.

Select_range / Com_select = 22% -- High, but not necessarily bad.  (Select_range = 360/sec)

Opened_files = 86/sec.  I'm concerned about the table_open_cache, but there are not metrics to indicate whether it is "big enough".

Can you group writes into bigger transactions?  There is a lot of I/O; this would decrease it.
Com_begin and Com_commit are currently 257/sec.

A lot of the Writes are UPDATEs.  Please elaborate on what they are doing.  If they are incrementing counters, it may be worthwhile to collect them in some way.

innodb_io_capacity = 6000 -- I do not know if this is optimal for your system.

Innodb_x_lock_spin_waits = 5500/sec -- A huge number, but I don't have a good handle on what to do about it.

large_page_size -- I have not experienced this; how is it going for you?

slave_skip_errors = 1062 -- Maybe you need to use INSERT IGNORE or INSERT .. ON DUPLICATE KEY UPDATE..?

Slow log

Many of the queries have

    from  items t,hosts r
    where  t.hostid=r.hostid
      and  r.proxy_hostid=13242
      and  r.status in (0,1)
      and  t.type in (0,7,1,4,6,12,2,3,9,10,11,13,14,16,17,5)
    order by  t.itemid

Which needs these composite indexes:

items:  INDEX(hostid, type)  -- in this order
hosts:  INDEX(proxy_host_id, status, hostid)  -- this order; helps WHERE; 'covering'

My as well toss KEY hosts_3, since it will be redundant.

I see cases of redundant indexes, such as

KEY `c_items_4` (`interfaceid`),
KEY `items_6` (`interfaceid`),

Recommend DROPping one of them.

There are issues with many of the other tables, but I won't go into them; Zabbix should fix them.

more

UPDATE item_discovery ...
    WHERE itemid BETWEEN ... AND ...
       OR itemId IN (...);

This is probably very inefficient because of the OR.  The best solution (for MySQL; don't know about Zabbix) is to split it into two UPDATEs:

UPDATE item_discovery ...
    WHERE itemid BETWEEN ... AND ...;
UPDATE item_discovery ...
    WHERE itemId IN (...);

more 2

It should not take 6-10 seconds to insert 1000(?) rows into one partition of a table.  It might help if you limited these inserts to to 100 rows at a time.

more 3

Speeding up queries, as indicated above, will help regardless.  But I am now seeing a different problem.  That slowlog snippet from from one 30-minute period?  And that was some kind of spike that does not normally happen?  What this when you reached Max_used_connections = 523?  What happens in a case like that:  523 threads, all getting some tiny fraction of 32 cores, etc, is stumbling over each other.  I see a few queries taking more than 3 seconds, yet they should not take more than about 3 milliseconds.

What I recommend in such situations is to figure out how to limit the number of connections upstream, that is in Zabbix client.  Meanwhile, you could decrease max_connections to, say, 200.  This is likely to trip up the client; but that might be better than having a long period when nothing seems to be getting done.

Also, 5.5 is getting pretty old; start laying plans for an upgrade.

Nasty DELETE

@12:37 (and perhaps every xx:37) there is a 123-second query finishing:

DELETE FROM events WHERE source = 0 AND object = 0 AND clock < ...;

Although this is the optimal index for such

KEY `events_2` (`source`,`object`,`clock`)

It is apparently not good enough.  123 seconds of disk-intensive deletes, including building rollback stuff, can slow down other things -- which I think is what happened.

Cures?

Plan A:  Instead of doing the DELETE hourly, do it every, say, 5 minutes (with a different clock cutoff each time`).  This should speed it up to 10 seconds instead of 123, possibly faster.

Plan B:  Make it a PARTITIONed table and use DROP PARTITION, which is much less intensive on the system.  (But that adds a lot of complexity, and Zabbix probably will get in the way.)

Plan C:  "Continually" delete the stuff, using another process.  Perhaps

Loop:
    DELETE FROM events WHERE source = 0 AND object = 0
            AND clock < ...   -- update this cutoff as needed
        LIMIT 1000;
    sleep 1 second
repeat

Plan D:  See my Big Delete blog for other techniques (and some details on Plans A, B, C.)

Continue to pursue the other suggestions; you are probably near the breaking point -- That is, if the dataset doubles in size, nothing will bring the system back to life.  My various suggestions will delay, but not eliminate the doomsday.


Postgres 9.6.2 not stopping on Windows. Any idea? So, I've got Postgres 9.6.2 installed on Windows 10 64bit, and, when I start Windows, 2 things might happen:

The server starts normally. This is the most common one.
The server starts in an inconsistent way that hangs if you try to connect to it, like with psql. Stopping the service is not possible through the Services app. See screenshot.

When it starts inconsist, I can't stop the service. See:

It stays forever in a  Stopping state.

I'm not an experienced DBA. Any idea on how to solve this or find the reason?

Connect with psql and type this commmand

show log_destination;

this will display where logs are. If the output is EVENTLOG, then check Windows Event Viewer for any message from postgresql. I don't think log_destination is CSVLOG nor SYSLOG, so the last possible value is STDERR. In this case I don't think you may get the messages.
In order to change your configuration and use event log, see https://www.postgresql.org/docs/9.6/static/event-log-registration.html .

Once you know where the log is, check its content and you should be able to find the cause of your problem.


Query where condition take from the list of value /****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT
       (
         SELECT ForeignCurrencyBal
         FROM [InsightWarehouse].[dbo].[v_Account] AS subA
         WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]
               AND
               subA.AccountNum = STMT.ACCOUNT_NUMBER
       ) AS Balance,
       ACCOUNT_NUMBER,
       CASE WHEN STMT.CRF_TYPE='CREDIT'
            THEN STMT.AMOUNT_LCY
            ELSE NULL
       END as 'CreditAmount',
       CASE WHEN STMT.CRF_TYPE='DEBIT'
            THEN STMT.AMOUNT_LCY
            ELSE  NULL
       END as 'DebitAmount',
       STMT.BOOKING_DATE,
       STMT.VALUE_DATE,
       CRF_TYPE
FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT
     LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A
     ON a.AccountNum = STMT.ACCOUNT_NUMBER
WHERE STMT.MIS_DATE='2019-02-01'
      AND
      STMT.ACCOUNT_NUMBER='14498478'

At this line
WHERE BusinessDate=['2019-02-01','2019-01-31',etc..] this list can get by sub-query.

I want to put the condition where the previous day, it mean the second item from this list. something like this

BusinessDate='2019-01-31'

Something like this:

SELECT
    DISTINCT (
        SELECT ForeignCurrencyBal
        FROM [InsightWarehouse].[dbo].[v_Account] AS subA
            JOIN (
                SELECT TOP (1) dt
                FROM (
                    VALUES
                        ('2019-02-01')
                    ,   ('2019-01-31')
                    --,etc..
                )d(dt)
                WHERE d.dt < STMT.MIS_DATE
                ORDER BY d.dt DESC
            ) x ON x.dt = BusinessDate
        WHERE subA.AccountNum = STMT.ACCOUNT_NUMBER
    ) AS Balance,

    ACCOUNT_NUMBER,
    CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
        ELSE NULL
    END as 'CreditAmount',
    CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
        ELSE  NULL
    END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,

    CRF_TYPE

FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT
    LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER

WHERE STMT.MIS_DATE='2019-02-01'
    AND STMT.ACCOUNT_NUMBER='14498478'

UPD: Pay attention that your subquery has to return one row so perhaps you should use TOP (1) or some aggregation function to avoid possible errors.


PostgreSQL converting undefined JSONB fields into a row dynamically I have a table with a field of type jsonb, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance:

From

CREATE TABLE v(id,jsonb)
  AS VALUES
    (1,'{"a":"4", "b":"5"}'::jsonb),
    (2,'{}'),
    (3,'{"a":"8", "c":"9", "d":"9"}');

to

id  |  a   |   b   |  c  |  d

1   |  4   |   5   |     |
3   |  8   |       |  9  |  9

for this specific case one solution is

select * from table, json_to_record(optional) as x("a" text, "b" text, "c" text, d text)

As you can see the keys can vary and and in a big database is difficult put all the keys in my real problem I have 31 keys and  on other hand If I want to reuse this script in other table I have to fill manually the keys.

Is there any way to do a select all over the keys of jsonb without specify manually the keys?

My question : Is there any way to do a select all over the keys of jsonb without specify manually the keys?

No, because there is no way to have a query return an undefined result set. However, if the table is not accepting new queries you can generate a dynamic sql statement.

SELECT FORMAT(
  $$ SELECT * FROM %I.%I CROSS JOIN LATERAL jsonb_to_record(%I) AS rs(%s); $$,
  'public',
  'v',
  'jsonb',
  array_to_string(
                (SELECT ARRAY(SELECT DISTINCT col FROM v CROSS JOIN LATERAL jsonb_object_keys(jsonb) AS t(col) ORDER BY col)), ' text , '
  ) || ' text'
);

Then run that query, or run \gexec in psql.

                                                    format
--------------------------------------------------------------------------------------------------------------
  SELECT * FROM public.v CROSS JOIN LATERAL jsonb_to_record(jsonb) AS rs(a text , b text , c text , d text);
(1 row)

test=# \gexec
 id |             jsonb              | a | b | c | d
----+--------------------------------+---+---+---+---
  1 | {"a": "4", "b": "5"}           | 4 | 5 |   |
  2 | {}                             |   |   |   |
  3 | {"a": "8", "c": "9", "d": "9"} | 8 |   | 9 | 9
(3 rows)

You may or may not want to build some type inference with jsonb_typeof back to pg types: keep in mind, you can never go back to integer or something, but you should be able to store the numbers as double precision.


MySQL: Reducing amount of open files So, I currently have a problem with my mySQL server opening far too many file handles.
This requires me to restart to server each day, as otherwise, I get the infamous "errno 24, too many open files" error.
I have already tried raising the system maximum count to 500k, but I still get that error.
At the time mySQL stops working, it uses about 384k file handles, far too many in my books.
System information:

VPS running on a Intel Xeon E5-2670 v3
12GB of DDR4 RAM
240GB SSD
Debian 8 64-bit, latest updates

More than enough ram.
my.cnf
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 127.0.0.1

# Fine tuning
max_allowed_packet = 16M
thread_stack       = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP
max_connections = 384
key_buffer_size = 284M
tmp_table_size = 32M
max_heap_table_size = 32M

# Query cache
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M

# Join buffer
join_buffer_size = 600K
table_open_cache = 440

# InnoDB
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 32M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-error   = /var/log/mysql/error.log

# Other stuff
[isamchk]
key_buffer = 16M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

I am looking for a way to reduce the amount of file handles used, not raise it.
Thanks!

SHOW VARIABLES LIKE 'open_files_limit';

That will reflect the OS's ulimit -n.  In some OSs, that limit is set too low.  Change it.

Then set table_open_cache to be about one-third of it.


Trim a value in an xml column in MS SQL Server I have a database with an XML column. I am trying to update (trim) an xml value of the xml column. I am running on Windows, SQL Server 2008 Express.

I did not find any way to trim directly in Xquery so I tried by selecting the value. Here is the SQL

select RTRIM(LTRIM(col2.value('(/Root/Row/Rowid/text())[1]','varchar(20)')))
from   dbo.T1

But now I am not getting how to update this xml value.

Is there any way to Trim the xml value directly? If not how can I Trim the value?

I am not trying to update the single row in the table, I am trying to trim the xml value of all the rows in the table

You could do that using the sql:column function and a Common Table Expression (CTE), something like this:

CREATE TABLE #tmp ( col2 XML )

INSERT INTO #tmp ( col2 )
VALUES ( '<Root>
    <Row>
        <Rowid>trim this value    </Rowid>
    </Row>
</Root>' )

SELECT 'before' s, *
FROM #tmp

;WITH cte AS
(
SELECT col2, RTRIM( LTRIM( col2.value('(/Root/Row/Rowid/text())[1]','VARCHAR(20)' )))  trimmedColumn
FROM #tmp
)
UPDATE cte
SET col2.modify('replace value of (Root/Row/Rowid/text())[1] with sql:column("trimmedColumn")')

SELECT 'after' s, *
FROM #tmp

-- Cleanup
DROP TABLE #tmp

My results:


Why does SQL Server return some rows while still executing the query, and sometimes not? There are queries where when we hit "execute", it shows some rows and it keeps growing, but the query is not over yet. Yet sometimes, it waits until the end of the query.

Why does this happen? Is there a way to control this?

The answer, as usual (alright, most of the time), lies in the execution plan.

There are certain operators that require all rows to arrive at them before they can start processing those rows, and passing them downstream, for example:

Hash Join (on building the hash table)
Hash Match
Sort (Except Hash Flow Distinct)

They're either called blocking, or stop and go operators because of this, and they're often chosen when the optimizer thinks it'll have to process a whole lot of data to find your data.

There are other operators that are able to begin streaming, or passing any found rows along immediately

Nested Loops
Index supported Merge Joins
Stream Aggregates

When queries start returning data immediately, but don't finish immediately, it's usually a sign that the the optimizer chose a plan to locate and return some rows quickly using operators that have a lower start up cost.

This can happen because of row goals introduced either by you, or by the optimizer.

It can also happen if a bad plan is chosen for some reason (lack of SARGability, parameter sniffing, insufficient statistics, etc.), but that takes more digging to figure out.

For more information, check out Rob Farley's blog here

And Paul White's series on row goals here, here, here, and here.

It should also be noted that, if you're talking about SSMS, rows only appear once an entire buffer has been filled, not just willy-nilly.


Why do I have THREADPOOL waits even though I have threads available? We are seeing application time outs and with help of the First Responder Kit, I have captured details. One of the things that caught my attention was:

Poison wait detected: Thread Pool

So I ran below queries:
---get thread pool waits---thread pool wait query
SELECT count(*) FROM sys.dm_os_waiting_tasks
 where wait_type ='threadpool'

-- get threads available--Threads available query
 declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers',
    Getdate() as 'DateLogged'
from
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

When I run the "thread pool wait" query, thread pool waits are appearing in above result (sometimes, more than 20 results) for fraction of second and then disappearing when I run it again. But I could see more than 400 threads available  in second result set through the "threads available" query.
I am not able to understand why I am seeing THREADPOOL waits when I run the first query, but second query shows threads are still available. Can any one please explain?

Maxdop: 8
Max degree of parallelism: 50

Thread pool waits:

I am not able to understand why I am seeing THREADPOOL waits when I run the first query, but second query shows threads are still available. Can any one please explain?

I've observed that you can see lots of really brief THREADPOOL waits (a handful of milliseconds each) while SQL Server is acquiring threads from the operating system.
This might happen if, for example, SQL Server was idle for a while, and then several parallel queries are executed at once.  These queries will need to wait while the OS provides threads to the sqlservr.exe process, accumulating THREADPOOL waits during that time.
You can confirm this is happening by watching the "Process" -> "Thread Count" PerfMon counter.  Generally, whenever this value climbs rapidly, you might see these THREADPOOL waits.
There's a complete demo of this behavior on my blog (Unusual THREADPOOL Waits), here's the summary:

Going back to that docs link:

Occurs when a task is waiting for a worker to run on.

In the typical case, this is because we’ve reached the logical limit SQL Server has set for how many workers threads you can have at a time.
In the unusual case, the task is waiting for a worker because SQL Server is allocating the thread (requesting it from the OS) that is needed to back that worker.

To be clear, I'd be surprised if these waits were the cause of your application timeouts.


How much does an ordered query of an attribute costs in terms of operations when the attribute is a key or an attribute? How much does the following query costs in terms of operations when the attribute is a key or an attribute ?

SELECT Attribute
  FROM R
    WHERE Attribute = 50
      ORDER BY Attribute

We assume we have R(K,A,B,other) a relation with

Nrec(R)=100000,
Lr=100 byte
K, a key in    [1;100000]
A with integer values uniformly distributed in the range (1,1000)

R is stored with heap organization with data unsorted with both respect to K and A, in pages of size Dpag=1024 bytes each.

I have an array giving me the following costs for heap organizations :

My attempt :

I know that

SELECT Attribute
  FROM R
    WHERE Attribute = 50

Is an equality search. As far as there can be several records with this attribute, with an attribute search it costs Npage(R). Yet with the Key search I've been told to follow the given array (without understanding why...)...

But I don't know what does ORDER BY induces

Tips from my teacher

1. Attribute search

It should be the same costs for the attribute search as far as

RC = Csearch + Cwrite + Csort + Cread

Yet I merely understand this equation...

2. key search

"we need to estimate the number of bytes to store Erec" said he. where Erec = Nrec(R)/Lr= 100000/100 = 100. (I think there is a mistake here as far as first 100000/100 = 1000 and second it shouldn't have been Lr as the denominator but the number of files that actually have the attribute we are looking for => 100000/1000 = 100)

Furthermore, the size of Erec = 100 x 4 = 400 <1024. We would only need one page to order Erec. (Yet, I don't understand why is it multiplied by 4.)

References

This question comes more or less from a book written by Dario Colazzo and some others from Pisa University Relational DBMS Internals available in the link.

Not sure what your question is exactly, as the symbols you use seem a bit confusing, but the general idea I think is this:

Firstly, you have to visit all records in the heap to find those where Attribute = 50, so that would be, in pages, 100000 records times 100 bytes each divided by 1024 (page size) rounded up, which comes to 9766. This determines your I/O cost, which will be all you need to know for the query without ordering -- matching records can be returned to the client immediately.

The rest is impossible to evaluate without knowing the selectivity of your search criteria, that is, how many records match it. For the query with ORDER BY ultimately you will need to 1) store all matching records in memory, determining your memory cost, and 2) sort them, determining your CPU cost.

Edit:

With Attribute uniformly distributed across 1000 distinct values we can estimate that the equality predicate will fetch on average 100000/1000 = 100 rows, each 100 bytes long, which will require 10000 bytes to store. CPU cost to sort them will be proportional to log₂(100 rows).

Note that some smarty-pants optimizer might notice that there's no need to order the result set by the value that's on the right hand side of the sole equality predicate and ignore the ORDER BY clause altogether.


SDO_GEOM.SDO_INTERSECTION between a line and a polygon returns what? I'm confused as to what exactly the SDO_GEOM.SDO_INTERSECTION returns in Oracle. Documentation is here, but only gives examples of two intersecting polygons.

What if I have many lines, some of them are contained fully within the polygon and some intersect the polygon. Assume that I need the geometry of any line fully contained within the polygon (i.e. same as SDO_CONTAINS) AND the geometry of those parts of intersecting lines, which are inside the polygon.

Example image below. Assume I only need the geometries of the red lines, but not the green ones. Will SDO_INTERSECTION return all of the red ones or only those red ones, which also go outside of the polygon and have some green bits?

Someone answered this question for me and it is as I suspected.
SDO_INTERSECTION will return any lines fully contained within a polygon, as well as those parts of intersecting lines, which are within the polygon.
In other words, it will return the geometry of everything in red in the example image.


What does HY stand for in error code? I've seen those error codes with prefix HY like HYT00 in MS-SQL or HY000 in MySQL, but, what do they stand for? why are they similar?

what do they stand for?

Codes like HYT00 and HY000 are called SQLSTATE. The HY part is the Class.
I can't find any documentation that even hints at what it stands for. There is nothing to indicate that the individual letters have any meaning. Most other classes are numeric (hex), ranging from 00 to 46. And then we have HV, HW, HY, and HZ. That would seem to rule out the theory that HY was chosen as a tribute to Henny Youngman. Rather, it looks like they reserved class codes with a first character of I through Z for implementation-specific classes, and that left HZ as be the highest one defined in the ANSI spec. Then they worked backward through HY, HW, and HV for other conditions generally outside of SQL itself -- datalinks, foreign data wrappers, and call-level interfaces. (They skipped HX -- another mystery.)

why are they similar?

Because it is part of the ANSI SQL standard. Any RDBMS that wants to follow the standard will use the SQLSTATE format for their return codes.
According to this Teradata documentation, ANSI defines HY as a CLI condition.

Call-Level Interface condition

Note: The Call-Level Interface referred to is not the Teradata Database CLIv2, but rather an ANSI-standard CLI that is a dialect of the Microsoft Open Database Connectivity, or ODBC, specification.

IBM says:

'HY', is generated by the CLI or ODBC driver.

MariaDB says that HY

is the class used for builtin conditions which do not have a specific SQLSTATE class.

Firebird SQL uses HY for:

"CLI-specific Condition"

The MariaDB usage is an outlier, but the consensus is that HY is used for errors raised by the CLI. (Note that this does not stand for Command Line Interface, but rather for Call-Level Interface, which is the  API for embedding SQL code in a host program. See https://en.wikipedia.org/wiki/Call_Level_Interface.)


mysql, changing tables from myisam to ndb Gents,
  Converting a table from myisam to ndb via:

ALTER TABLE piwik_log_profiling ENGINE NDBCLUSTER;

This will fail with:

BLOB column 'query' can't be used in key specification with the used table type

Now, if the 'query' field is changed from text to varchar, then I get the following:

Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Original table:

| log_profiling_backup | CREATE TABLE `log_profiling_backup` (
`query` text NOT NULL,
`count` int(10) unsigned DEFAULT NULL,
`sum_time_ms` float DEFAULT NULL,
UNIQUE KEY `query` (`query`(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

Maybe you could try the following:

ALTER TABLE piwik_log_profiling RENAME piwik_log_profiling_old;
CREATE TABLE piwik_log_profiling SELECT * FROM piwik_log_profiling_old WHERE 1=2;
ALTER TABLE piwik_log_profiling ADD COLUMN query100 CHAR(100) NOT NULL AFTER query;
ALTER TABLE piwik_log_profiling ADD UNIQUE INDEX (query100);
ALTER TABLE piwik_log_profiling ENGINE=NDBCLUSTER;

If it works up to now, them do this

INSERT INTO piwik_log_profiling (query)
SELECT query,LEFT(query,100),`count`,sum_time_ms
FROM piwik_log_profiling_old;

Based on this, your queries will have to use query100 instead of query in its WHERE clauses.

Please let us all know if this worked !!!


Row with latest timestamp How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)?

My app records data from a data feed. Another process endlessly queries to get the latest freshest entry. Older data may appear on occasion from secondary sources. So the most recently inserted rows are usually, but not necessarily, the freshest data.

I am using this kind of SQL where when_ is a TIMESTAMP WITH TIME ZONE column:

SELECT *
FROM my_table_
ORDER BY when_ DESC
LIMIT 1
;

This code works (if no NULL values in data!). But with a possible couple million rows, and a query every 10 seconds, I'm concerned about performance.

Without any index on when_ column, does this statement require a full scan of all rows?

Does adding an index change the performance? Will Postgres automatically scan the index to locate the most recent row, or must I do something to make an index scan happen?

With an index on when_ column, should I change this SQL to use some other approach/strategy of query?

Is there some other way to gather freshly inserted rows? I'm using UUID rather than SERIAL types for my primary key, and may federate data amongst multiple database instances, so that rules out checking for ever-increasing integer numbers.

Basic answers

Since you select a couple of big columns an index-only scan is probably not a viable option.

  This code works (if no NULL values in data!)

While the column isn't defined NOT NULL, add NULLS LAST to the sort order to make it work in any case, even with NULL values. Ideally, use the clause in the corresponding index as well:

SELECT <some big columns>
FROM   my_table_
ORDER  BY when_ DESC NULLS LAST
LIMIT  1;

PostgreSQL sort by datetime asc, null first?

  Without any index on when_ column, does this statement require a full
  scan of all rows?

Yes. Without index, there is no other option left. (Well, there is also table partitioning where an index on key columns(s) is not strictly required, and it could assist with partition pruning. But you would typically have an index on key columns there, too.)

  With an index on when_ column, should I change this SQL to use some
  other approach/strategy of query?

Basically, this is the perfect query. There are options in combination with advanced indexing:

Advanced technique

Assuming a NOT NULL column. Else, add NULLS LAST to index and queries as suggested above.

You have a constant influx of rows with later when_. Assuming the latest _when constantly increases and never (or rarely) decreases (latest rows deleted / updated), you can use a very small partial index.

Basic implementation:

Run your query once to retrieve the latest when_, subtract a safe margin (to be safe against losing the latest rows) and create an IMMUTABLE function based on it. Basically a "fake global constant":

CREATE OR REPLACE FUNCTION f_when_cutoff()
  RETURNS timestamptz LANGUAGE sql COST 1 IMMUTABLE PARALLEL SAFE AS
$$SELECT timestamptz '2015-07-25 01:00+02'$$;

PARALLEL SAFE only in Postgres 9.6 or later.
Create a partial index excluding older rows:

CREATE INDEX my_table_when_idx ON my_table_ (when_ DESC)
WHERE when_ > f_when_cutoff();

With millions of rows, the difference in size can be dramatic. And this  only makes sense with a much smaller index. Just half the size or something would not cut it. Index access itself is not slowed much by a bigger index. It's mostly the sheer size of the index, which needs to be read and cached. (And possibly avoiding additional index writes, but hardly in your case.)
Use the function in all related queries. Include the same WHERE condition (even if logically redundant) to convince the query planner the index is applicable. For the simple query:

SELECT <some big columns>
FROM   my_table_
WHERE  when_ > f_when_cutoff()
ORDER  BY when_ DESC
LIMIT  1;

The size of the index grows with new (later) entries. Recreate the function with a later timestamp and REINDEX from time to time with no or little concurrent access. Only reindex after a relevant number of rows has been added. A couple of thousand entries won't matter much. We are doing this to cut off millions.
The beauty of it: queries don't change.

Implementation with function to update the partial index automatically:

Get latest child per parent from big table - query is too slow

More general advice:

Index optimization with dates


MySQL: UNION query to get a line for each date Table1
DATE        SKU     QTY
2015-06-20      ABCD    3
2015-06-20      ABCD    1
2015-06-21      ABCD    5
2015-06-23      ABCD    2
2015-06-20      EFGH    1
2015-06-21      EFGH    3

Table2
DATE        SKU     QTY
2015-06-21      ABCD    -1
2015-06-22      ABCD    -4
2015-06-23      ABCD    1
2015-06-20      EFGH    -2
2015-06-20      EFGH    1
2015-06-21      EFGH    2
2015-06-23      EFGH    3

Table3
SKU         CATEGORY
ABCD        cat1
EFGH        cat2

I want to know how many pieces I have, basically a daily running total per SKU - like the following:

DATE        SKU     CATEGORY    QTY
2015-06-20      ABCD    cat1            4   (=[0]+3+1)
2015-06-21      ABCD    cat1            8   (=[4]+5-1)
2015-06-22      ABCD    cat1            4   (=[8]-4)
2015-06-23      ABCD    cat1            7   (=[4]+2+1)
2015-06-20      EFGH    cat2            0   (=[0]+1-2+1)
2015-06-21      EFGH    cat2            5   (=[0]+3+2)
2015-06-22      EFGH    cat2            5   (=[5])
2015-06-23      EFGH    cat2            8   (=[5]+3)

basically:
- QTY of item = QTY of item of day before + sum of qty in both tables
- I need a line for all days - even if there is no lines for that day/sku in table1 and table2

I have tried:

SET @lastItem := 0, @lastValue := 0;
SELECT t6.date, t6.SKU, t6.Category, t6.qty FROM
(
SELECT
    t1.date as date,
    t1.sku as sku,
    t3.category as Category,
    @lastValue :=  if( @lastItem = t1.sku, if(@lastValue + ifnull(t1.qty,0)<0,0,@lastValue + ifnull(t1.qty,0)), ifnull(t1.qty,0) ) as qty,
    @lastItem := t1.sku
    FROM
    (
        select date, sku, ifnull(sum(qty),0) as qty FROM(
            (SELECT date(date) as date, idItem, ifnull(Sum(qty),0) as qty
                FROM table1
                GROUP BY sku, date(date)
            )
            UNION
            (SELECT date(date) as date, idItem, ifnull(Sum(qty),0) as qty
                FROM table2
                GROUP BY sku, date(date)
            )
        ) t5
        group by date,sky
    ) t1
    LEFT JOIN table3 t3 on t1.sku = t3.sku
    order by t1.sku, t1.date
) t6

but that does not give me the a line if there is no entry in both table1 and table2

EDIT: Replaced Table1 on second table INSERT.

DDLs:

    CREATE TABLE `Table1` (
      `date` date NOT NULL,
      `sku` varchar(5) NOT NULL,
      `qty` int(5) NOT NULL,
      PRIMARY KEY (`sku`,`date`,`qty`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table1`(date, sku, qty)VALUES
    ('2015-06-20', 'ABCD', '1'),
    ('2015-06-20', 'ABCD', '3'),
    ('2015-06-21', 'ABCD', '5'),
    ('2015-06-23', 'ABCD', '2'),
    ('2015-06-20', 'EFGH', '1'),
    ('2015-06-21', 'EFGH', '3');

    CREATE TABLE `Table2` (
      `date` date NOT NULL,
      `sku` varchar(5) NOT NULL,
      `qty` int(5) NOT NULL,
      PRIMARY KEY (`qty`,`sku`,`date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table2`(date, sku, qty)VALUES
    ('2015-06-22', 'ABCD', '-4'),
    ('2015-06-20', 'EFGH', '-2'),
    ('2015-06-21', 'ABCD', '-1'),
    ('2015-06-23', 'ABCD', '1'),
    ('2015-06-20', 'EFGH', '1'),
    ('2015-06-21', 'EFGH', '2'),
    ('2015-06-23', 'EFGH', '3');

    CREATE TABLE `Table3` (
      `sku` varchar(5) NOT NULL,
      `CATEGORY` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`sku`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table3`(sku, CATEGORY)VALUES
    ('ABCD', 'cat1'),
    ('EFGH', 'cat2');

I have the following solution, which gives the desired result, except the values for the days that there is no entries for:

set @total=0;
set @sku_name='';
SELECT date, sku, total, CATEGORY FROM
   (
    SELECT date, sku, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM
        (
        SELECT date, sku, SUM(qty) qty FROM
        (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY sku, date) union_table GROUP BY sku, date
        ) summary_table
    ) final_table
    INNER JOIN table3 USING (sku) ORDER BY sku, date;

Result:

+------------+------+-------+----------+
| date       | sku  | total | CATEGORY |
+------------+------+-------+----------+
| 2015-06-20 | ABCD |     4 | cat1     |
| 2015-06-21 | ABCD |     8 | cat1     |
| 2015-06-22 | ABCD |     4 | cat1     |
| 2015-06-23 | ABCD |     7 | cat1     |
| 2015-06-20 | EFGH |     0 | cat2     |
| 2015-06-21 | EFGH |     5 | cat2     |
| 2015-06-23 | EFGH |     8 | cat2     |
+------------+------+-------+----------+

I would give credits to @RolandoMySQLDBA who answered my question, where I get the algorithm from.

EDIT: Introduced new variable old_total

If you want to show the rows for all dates, assuming that first date in the min among all dates in both table, and the max date in the max among all dates in both tables, you can use this method:

set @total=0;
set @old_total=0;
set @sku_name='';

create temporary table tmp_table as
    select distinct date, sku from
        (select date from table1 union select date from table2) u1,
        (select sku from table1 union select sku from table2) u2
    order by sku, date;

SELECT tmp_table.date, tmp_table.sku, ifnull(total, @old_total), CATEGORY FROM
tmp_table left join
   (
    SELECT date, sku, @old_total:=@total, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM
        (
        SELECT date, sku, SUM(qty) qty FROM
        (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY sku, date) union_table GROUP BY sku, date
        ) summary_table
    ) final_table using (date, sku)
    INNER JOIN table3 USING (sku) ORDER BY sku, date

The result:

+------------+------+---------------------------+----------+
| date       | sku  | ifnull(total, @old_total) | CATEGORY |
+------------+------+---------------------------+----------+
| 2015-06-20 | ABCD |                         4 | cat1     |
| 2015-06-21 | ABCD |                         8 | cat1     |
| 2015-06-22 | ABCD |                         4 | cat1     |
| 2015-06-23 | ABCD |                         7 | cat1     |
| 2015-06-20 | EFGH |                         0 | cat2     |
| 2015-06-21 | EFGH |                         5 | cat2     |
| 2015-06-22 | EFGH |                         5 | cat2     |
| 2015-06-23 | EFGH |                         8 | cat2     |
+------------+------+---------------------------+----------+


trigger to delete records with same value from another table after inserting record in another table i have two tables normal and abnormal,i want to create a trigger that will be able to delete records from normal table once i insert the same details in the abnormal table for example if a  person is sick and we insert his or her details in the abnormal table,the moment i submit the insert values in the abnormal table a trigger should delete the persons record in the normal table with a message deleted from normal table because this person is sick.

mysql> desc normal;
+----------+-------------+------+-----+---------+
| Field    | Type        | Null | Key | Default |
+----------+-------------+------+-----+---------|
| Id       | int(11)     | NO   | PRI | 0       |
| FirstName| varchar(20) | YES  |     | NULL    |
| lastName | varchar(20) | YES  |     | NULL    |
+----------+-------------+------+-----+---------+
3 rows in set (0.00 sec)

mysql> desc abnormal;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Id       | int(11)     | NO   | PRI | 0       |       |
| FirstName| varchar(20) | YES  |     | NULL    |       |
| lastName | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

lets say i do a select statement on normal table

mysql> select * from normal;
+----+----------+----------+
| Id | firstName | lastName|
+----+----------+----------+
|  1 | Micheal  | jacobs   |
+----+----------+----------+
|  2 | benjamin | clayto   |
+----+----------+----------+
|  3 | jackson  | boxer    |
+----+----------+----------+

then i insert values that exist in normal i insert then into abnormal table

insert into abnormal values('1','Micheal','jacobs', 'under treatment');
mysql> select * from abnormal;
+----------+---------+---------------+
|firstName | lastName| extra         |
+----------+---------+---------------+
| micheal  | jacobs  |under treatment|
+----------+---------+---------------+

the end result on normal table should not have the record for micheal it should be deleted

mysql> select * from normal;
+----+----------+----------+
| Id | firstName | lastName|
+----+----------+----------+
|  2 | benjamin | clayto   |
+----+----------+----------+
|  3 | jackson  | boxer    |
+----+----------+----------+

Try this:

DELIMITER $$
CREATE TRIGGER tgr_1 AFTER INSERT ON abnormal
FOR EACH ROW
BEGIN
  DELETE FROM normal where id = NEW.id;
END $$


emergency-mode repair failed, nothing to do? A production server got I/O disk error's I tried to fix it with dbcc but it got unexpected errors and crashed after a few minutes. so I took the file down and copied it over to a new machine.

I have no recent backup available fast, (its a online backup that takes very long to download).

File activation failure. The physical file name "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mydb_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
DBCC results for 'mydb'.

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3724880; actual 0:0). It occurred during a read of page (1:3724880) in database ID 5 at offset 0x0000071aca0000 in file '\mydb.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The emergency-mode repair failed.You must restore from backup.

The answer is - as many of you replied in the comment's - that there is no way around a backup is the only way to go. and I did find a good backup and it was restored. that you all for the help.


How to normalise a relation using functional dependencies G = {{A,B}→{C},{B,D}→{E,F},{A,D}→{G,H},{A}→{I},{H}→{J} }.

Which is a key attribute: {A,B} or {A,B,D}?

A simple way to check if a set of attributes is a key, is to compute the closure of those attributes with respect to the functional dependencies, and see if this contains all the attributes of the relation.

So:

{A B}+ = {A B C I}

{A B D}+ = {A B C D E F G H I J}

And so {A B D} is a key. You can also see that every key must contain A B D since those attributes appear only in the left parts of the dependencies, and since {A B D} by themselves are a key, there are no other keys.


Why is this MySQL proc using cursors failing to retrieve results? I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, and insert it into a few tables on my end. My cursor seems to only be iterating through once, though - here is the code.

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE;

    -- Declare Person variables --
    DECLARE person_id INT;
    DECLARE era_username VARCHAR(100);
    DECLARE last_name VARCHAR(50);
    DECLARE first_name VARCHAR(50);
    DECLARE email VARCHAR(100);
    DECLARE email_primary VARCHAR(50);
    DECLARE degree_id_1 INT;
    DECLARE degree_id_2 INT;
    DECLARE member_status INT;

    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR
        SELECT person_id, era_username, last_name, first_name, TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2, member_status
        FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor
        INTO person_id, era_username, last_name, first_name, email, email_primary, degree_id_1, degree_id_2, member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET user_id = SELECT (MAX(uid) + 1) FROM users;
        IF email_primary IS NOT NULL AND email_primary NOT LIKE '%null%' THEN
            SET user_email = email_primary;
        ELSE
            SET user_email = email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (user_id, era_username, SHA1(RAND()), user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`, `member_category_id`, `subscription_weekly_email`, `subscription_monthly_newsletter`)
        VALUES (user_id, first_name, last_name, phone_num, member_status, weekly_com, monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

It's as if the SELECT statement used to populate the cursor is returning no results. I've tested that independently, and it DOES return results correctly. I've tried putting a SELECT person_id directly below the first FETCH, and the returns NULL. So...although this query should return results, they're not making it into the cursor's result set. Am I missing something?

Try tagging all local variables with something like my_

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE;

    -- Declare Person variables --
    DECLARE my_person_id INT;
    DECLARE my_era_username VARCHAR(100);
    DECLARE my_last_name VARCHAR(50);
    DECLARE my_first_name VARCHAR(50);
    DECLARE my_email VARCHAR(100);
    DECLARE my_email_primary VARCHAR(50);
    DECLARE my_degree_id_1 INT;
    DECLARE my_degree_id_2 INT;
    DECLARE my_member_status INT;

    DECLARE my_user_id INT;
    DECLARE my_user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR
        SELECT person_id, era_username, last_name, first_name,
              TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2,
              member_status FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor
        INTO my_person_id, my_era_username, my_last_name, my_first_name,
              my_email, my_email_primary, my_degree_id_1, my_degree_id_2,
              my_member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET my_user_id = SELECT (MAX(uid) + 1) FROM users;
        IF my_email_primary IS NOT NULL AND my_email_primary NOT LIKE '%null%' THEN
            SET my_user_email = my_email_primary;
        ELSE
            SET my_user_email = my_email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (my_user_id, my_era_username, SHA1(RAND()), my_user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`,
              `member_category_id`, `subscription_weekly_email`,
              `subscription_monthly_newsletter`)
        VALUES (my_user_id, my_first_name, my_last_name, my_phone_num,
              my_member_status, my_weekly_com, my_monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = my_person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

In the book MySQL Stored Procedure Programming, all cursor examples give the local variable a unique name from its corresponding table column (if you have the book, see page 108 Example 5-15).

Sidenote : Where are weekly_com and monthly_com coming from ???


Dimensional Modeling and ETL in Redshift I have been researching Amazon's Redshift database as a possible future replacement for our data warehouse. My experience has always been in using dimensional modeling and Ralph Kimball's methods, so it was a little weird to see that Redshift doesn't support features such as the serial data type for auto incrementing columns.

There is, however, this recent blog post from the AWS Big Data blog about how to optimize Redshift for a star schema: https://blogs.aws.amazon.com/bigdata/post/Tx1WZP38ERPGK5K/Optimizing-for-Star-Schemas-and-Interleaved-Sorting-on-Amazon-Redshift

The question I have is about what is the best practice for loading a star schema in Redshift? I cannot find this answered in any of Redshift's documentation.

I'm leaning toward importing my files from S3 into staging tables and then using SQL to do the transformations such as lookups and generating surrogate keys before inserting into the destination tables.

Is this what others are currently doing? Is there an ETL tool worth the money to make this easier?

You are definately on the right track with Kimball rather than inmon for Redshift.

There are a number of patterns for this, I have used them all in different use cases

"ELT" pattern - Load the source tables to redshift fully, do not do any significant
transformations until the data has been loaded. For this you can
either load to s3, then use redshift copy command or I would
recommend using "AWS data migration services", which can sync a
source (e.g.mysql or postgres) to a target (e.g. redshift) Then, on
a regular basis run sql processes within redshift to populate dims
then facts. You can use third part cloud based tools to "simplify"
this process if you want to - such as Matillion (i do not recommend
using a third party tool)
"ETL pattern" - Transform the data in flight, using apache spark. and load the dims and facts into redshift spark->s3->redshift. I have used EMR for this which is good. this is also the approach taken if you use AWS Glue
Do not transform ! - similar to 1) but just use the tables that have
been loaded.

Note that Redshift sometimes works BETTER if you have a wide table with repeated values rather than a fact and  dimensions. The reason for this is that the columnar approach lets Redshift compress the different values down to a level that is pretty efficient. I do not have a formula for when to use many Dimensions vs a flat wide table, the only way is to try it and see!

Some links

AWS DMS for Redshift taret

AWS Glue


Postgres - caching of recently inserted data? Does Postgres cache recently INSERTed data? And if so, how can I increase the amount?

Here’s why I’m asking. I have a process that continuously inserts simple text messages into a table at an average rate of several million records a day (and contains several months of data). I have a second process that reads recent records from this table for processing. I’ve noticed that if I SELECT (for example) 100 records that have arrived in the last 6 hours it might take me 1 second. However if I select 200 record from the last 12 hours it might take (typically) 8 seconds and if I select 400 records from the last 24 hours it might take more than a minute.

Or in other words, there seems to be an almost exponential increase in the time to select data as it’s age increase. In my naiveite I had assumed there would be an approximately linear relationship between the period specified in the WHERE clause, and the time taken to retrieve the data.

For the record, here’s the table definition….

CREATE TABLE message
(
  messid SERIAL UNIQUE,
  rawmessage character(250), // raw, unprocessed message (ascii string)
  loadedtime timestamp without time zone, // Time this message was loaded
  deviceNumber   INTEGER     // unique identifier of device that generated message.
)

And here’s the sort of query I’m running (deviceNumber and loadedTime are both indexed)

SELECT * FROM message
WHERE deviceNumber = 1234567
AND loadedtime >='10-May-2018 21:00:00’
AND loadedtime <='10-May-2018 22:00:00’

The problem is that the process that reads the table occasionally falls behind schedule and needs to catch up, but because of the exponential time increase in retrieving slightly older data it has difficulty getting the backlog down.

My question comes in two parts….

Why is there such a discrepancy in the time taken to select recent
data and older data. Is it because recent INSERTS are also held in
memory? 2)
Is there anyway to improve the time to select data added
in the last 12 hours to be close to that achieved with data only one
hour old?

Thanks

Update - I'm will attempt to add a composite index on both loadedTime and deviceNumber as suggested (both already have individual indexes). This may take some time.
Once I've added a composite index, do I still need the individual indexes on those fields?

Caching by Postgres or the operating system isn't going to help much when Postgres doesn't know which of the millions of records in your table fulfill your query's condition(s). The only way to make sure this is going to be consistently performing as you expect is to use indexes.

An index on the combination of deviceNumber and loadedtime is what you need in your case.

In your question you note that a query can be relatively fast (like a second for recently inserted data), so I'm strongly suspecting you already have an index on at least one of these columns.


What problems will I get creating a database per customer? I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases.
We are just starting to develop a web app and have a similar problem to solve (lots of customers with their own isolated data).
What problems should I expect with using a database per customer? How can I solve them?
My Initial Thoughts
Advantages of a database per customer

Simpler database schema
Simpler backups - you can backup each customer in turn without it really impacting on other customers.
Makes it easy to export a given customers data.
Better cache performance - a write to one of the more active tables only impacts that single customer that performed the write.
Easier to scale across hardware. For example, when we need to go from 1 to 2 servers, we just move half our customers to the new server.

Disadvantages

Can MySQL cope with 5,000 databases? Would performance suck?
Changes to the schema can be hard to replicate out across all the databases. We would really really have to have an automated plan for this, such as versioning the schema and a script that understands how to take a database from one version to another.
Doing anything that is common to all our customers might be awkward or impossible
Similar to above, but any analytics we want to perform across all our customers might be impossible. How should we track usage across all customers for example?

This solution is called a multi-tenant design where each tenant (customer) has their own database. Given that, there are some other considerations to the alternative approach which is a single database:

With a single database, everyone must be on the same version no matter what. It isn't possible to upgrade some customers and not others. This can be problematic if a customer wants a hotfix of an application that isn't ready for wide release.
With a single database, when you do an upgrade, every client is down. If something goes wrong, every client is screwed.
With a single database, it is much more difficult to throttle resources. I.e., if one client is hammering the database, it is harder to give them more resources separate from everyone else.
It is much more difficult to allow users to host their own versions of your application. If you are building a solution that will be used by large enterprises, this is often a non-starter. Their IT department wants complete control over access to the system.
It is probably cheaper to scale out databases rather than scale them up. I.e., having to invest in faster hardware to host one database to rule them all is probably more expensive than being able to scale customers out to smaller, less expensive database servers. I can't say this one definitively because it depends greatly on the server software. If you stick with MySQL, this is probably true because the licensing costs are negligible. However, if you move up to SQL Server for example, scaling out becomes much more expensive unless you use a VPS environment and the cost-benefit of scaling up vs. scaling out changes. I can say, however, that once your database gets very large, management requires ever-greater levels of expertise. Very large databases require playing around with multiple filegroups and pushing certain indexes to different spindles to get better performance. In short, they get can complicated very quickly.

Having separate databases does mean you have to build an update mechanism which matches the database version with the application/site version. However, separate databases do provide superior isolation of data and IMO have a lower cost of hosting. It isn't a solution for all scenarios. If your system were never going to be hosted outside of your hosting and needed to scale up in customers rapidly and having all users on the same version of the application and database schema was desirable, then certainly having a single database is a better approach.


Resolve parametrized schema in PostgreSQL I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function.
Why am I looking this way ?

This functions will refer %ROWTYPE from multiple schema like

Var1 s1.emp%ROWTYPE ===> to refer the columns of emp tables as variable from s1 schema
var2 s2.stg_emp_d%ROWTPE; ===> to refer the columns of stg_emp_d tables from s2 schema as variable
var3 s3.emp_comp%ROWTYPE;===> to refer the colums of emp_comp tables from s3 schema as variable

s1, s2, s3 (schema names) are not same across different env eg:

IN DEV ENV s1, s2, s3
IN TEST ENV s1_t, s2_t, s3_t
IN PROD ENV s1_p, s2_p, s3_p

As we accept schema names as parameter in function, same function can be deployed without any changes across environments. It will inherit appropriate schema name set during run-time.

As we use %ROWTYPE of respective table, any changes to table structure will not impact these function. These changes are inherited via %ROWTYPE.

These functions need to get individual table columns name via %ROWTYPE with in BEGIN section like

if var1.emp_type = 'C' then ... do some thing ;
elsif  var1.emp_type = 'T' then ... do some thing ;

end if ;

Here is an example:
CREATE OR REPLACE FUNCTION get_list(in_code text[], p_schema text)
  RETURNS text  AS
$func$

DECLARE

  var1 user.emp%ROWTYPE;

BEGIN

SELECT q.id, q.title, q.code
FROM   questions q, emp e
WHERE  q.code <> ALL ($1)
and    q.emp_id = e.emp_id;

END ;

$func$ LANGUAGE sql;

The above getting created.
When I change var1 user.emp%ROWTYPE; to

var1 p_schema.emp%ROWTYPE; or
var1 $$ || p_schema || $$.emp%ROWTYPE;

Function is not getting created, but throwing error
ERROR : relation emp not found

Are there any limitations using parameterized items within DECLARE section?
I used these kinds of parameters with queries within BEGIN & END section. It did not throw any errors.

The short answer is that your use of variables in that manner in the DECLARE statement isn't going to work. I think the workaround you should shoot for is to use a RECORD type, but your example doesn't really explain the end goal, so it's hard to be 100% certain.


How group by affects aggregate functions I tried to understand the following scenarios:

Table name: family
+------+-------+
| id   | name  |
+------+-------+
|    1 | alice |
|    2 |  bob  |
+------+-------+

That table will be used for illustration purposes.

Server version: 5.6.20 MySQL Community Server (GPL)

Lets try:

SELECT id, name, COUNT(name) FROM family WHERE id = 50;

The query will result in:

+------+------+-------------+
| id   | name | COUNT(name) |
+------+------+-------------+
| NULL | NULL |           0 |
+------+------+-------------+

My first question is about how I got this result?, what I think is that:

The COUNT(name) gets called after the query(internally) resolved into:

+------+------+
| id   | name |
+------+------+
| NULL | NULL |
+------+------+

Now at this point the COUNT(name) is ignoring the NULL value and that is why I get this extra column:

+-------------+
| COUNT(name) |
+-------------+
|           0 |
+-------------+

I only want to understand the steps that build the result.

The following step is taken by me so I can get Empty set and not the table above.

So to get Empty set as the result I add:

SELECT id, name, COUNT(name) FROM family WHERE id=50 GROUP BY id;

At this point I pretty desperate, not just I couldn't understand the problem I'm lost with how the solution is even working.

My second question is about the solution, again my focus is on the process, the actual steps the database has taken to achive that result.

If anyone can walk me step by step I will be very thankful.

There are several aspects of your query. The first is that mysql by default will give you a random answer if there are several possible answers to a group by question. SQL92 demanded that all non aggregated columns in the select clause must be part of the group by clause. SQL99 loosened this restriction and requires that all non aggregated columns are functionally dependent of the group by clause. By default mysql ignores both of these and returns a random row if there are several possible ones. You can force the SQL92 behaviour by adding ONLY_FULL_GROUP_BY to @@sql_mode, most DBMS's use such implementation. A small example:

create table t (x int not null, y int not null);
insert into t (x,y) values (1,1),(1,2);

For a query like:

select x,y, count(*) from t group by x;

There are two possible answers:

1 | 1 |        2
1 | 2 |        2

Mysql will randomly return one of them. In some sense the result is undefined.

To see what happens when there is no group by clause at all involved, let's add another row to the equation:

insert into t (x,y) values (2,3);

select x,y, count(*) from t

Since we have no group by count(*) will be applied to the whole result, for x and y there are 3 possible combinations:

1 |    1 |        3
1 |    2 |        3
2 |    3 |        3

Once again mysql will shoot you in the foot by randomly returning one of these rows, say:

1 |    1 |        3

For an empty set the aggregate function count(*) should be 0 (WHERE is evaluated before GROUP BY), but there are no x and y:

select x,y, count(*) from t where x=4;
+------+------+----------+
| x    | y    | count(*) |
+------+------+----------+
| NULL | NULL |        0 |
+------+------+----------+

so they are shown as non-existing (NULL).

The short answer is that you get a non-sense answer because you asked a non-sense question ;-)

The scenario you describe is is probably the one thing that causes the most confusion in the mysql community. I wish that ONLY_FULL_GROUP_BY where the default behaviour and that there where a setting ALLOW_PARTIAL_GROUPING that could be used for backward compability.


pgsql slow query on table of 500 million rows I have a table that keeps measurements of latencies between nodes running MPI tasks in a large cluster. The table looks like this:

CREATE TABLE latency(
from_rank int,
to_rank int,
from_host varchar(20),
to_host varchar(20),
from_cpu varchar(20),
to_cpu varchar(20),
latency float8);

CREATE INDEX ON latency(from_host, to_host);

Now after a large experiment I collected over 500 million rows of data. I find querying these data painfully slow, below is an example of a SELECT COUNT(*)

psql (9.4devel)
Type "help" for help.

routing=# \timing
Timing is on.
routing=# SELECT COUNT(*) FROM latency;
   count
-----------
 522190848
(1 row)

Time: 759462.969 ms
routing=# SELECT COUNT(*) FROM latency;
   count
-----------
 522190848
(1 row)

Time: 96775.036 ms
routing=# SELECT COUNT(*) FROM latency;
   count
-----------
 522190848
(1 row)

Time: 97708.132 ms
routing=#

I am running both the PgSQL server and client on the same machine, which has 4 Xeon E7-4870s (40 cores/80 threads in total) and 1 TB of RAM. The effect of Linux file caching is obvious: the first query took well over 12mins while the subsequent ones took about 1.5min.

Is there anything I can do to make the query run faster, since 1.5min isn't exactly responsive.

Thanks.

For postgres, unlike other RDBMS, SELECT COUNT(*) ... from an unindexed table means a full scan of the table. So the first run is purely IO bound. If you want this query - consider adding some indexes to the table. (Without knowing the query I can not tell, what indexes you may need).

Another option - configure postgres to better utilize the RAM you have.


How do I corrupt an mdb intentionally for test purposes I'm creating a process to check for corrupt tables in an MDB on server.  There's no code, just tables.
I would like to corrupt an mdb intentionally for testing.  Has anyone ever done this?

How to launch the Detect and Repair Tool you may see below

Start MS Access 2007.
Click the MS Access button and click the PowerPoint options button.
Select the Resource Tab.
Click the Diagnose button.

But also you can get more resources to solve problem .mdb file

http://www.itninja.com/question/copying-1-2-gb-mdb-from-pc-to-network

Ultimate solution you may use only if previous methods didn't work, that denotes your .mdb corruption is severe, see and try it http://www.mdb.openfiletool.com/ MDB Open File Tool