Advice on a basic design, first time database design I am taking a course to become a Java developer.

The course involves using databases but we never really design any, unfortunately.

Most of the time we get pre-made databases and we have to implement code on it to Insert, Update, Read or Delete data.

But when my final test comes, chances are I will be making something that involves a database and therefore I want to try designing a few smaller ones to get the hang of a designing since I noticed that a good database design makes a lot of difference when writing the code to work with it.

I hope these types of questions are allowed here and that it's not going to be too broad.

This is a small design I made for something a simple as clubs and members with addresses and phonenumbers.

  There will be multiple clubs.
  Each club will have multiple members (obvious)
  A member cannot be part of multiple clubs
  A member can have multiple phonenumbers and email addresses
  A member can only have one address
  An address can belong to different members (couples or siblings)

My biggest confusions:

  If I want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the
  same member listed twice?
  Should I put all my tables on the automatic increment of an id or would this be a bad idea? (benefits/downsides?)
  If I add foreign keys in the foreign keys tab, will these automatically correspond to the correct table or do I have to add
  these to the columns also? (see picture 2)
  And my probably noobiest question at all... Do I put the foreign keys of phonenumber and email in their respective table
  linking to a person_Id or should I put phoneNumber and email Id's in
  the person table?

(My apologies for the language in the pictures not being English, I hope this isn't too much of a problem)

Responses to your individual questions

If I want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the same member listed twice?

If —as stated in your specifications— a Person can be a Member of only one Club, and you are interested in storing this datum simply as true or false, one option is adding a BIT(1) or a BOOLEAN (TINYINT) column to the Person table, and you may wish to call this column IsClubOwner. In this way, you can register the fact that a determined person is a club owner exclusively one time. Apart from that, this method also allows the possibility of retaining several people as owners of the same club occurrence. You can see a logical level depiction of this approach in Figure 1.
However, you are looking for the best approach and, according to my experience, such approach entails the development of a much more expandable and versatile structure. In this respect, follow the progression of a modeling exercise for these and other points below, in the sections entitled “Covering your remaining specifications”, “Person as Member of multiple Clubs” and “Member and Owner as separate entity types”.

Should I put all my tables on automatic increment of an id or would this be a bad idea? (benifits/downsides?)

If you face an explicit requirement that indicates the definition of a table with a column of such characteristics, and that column has a valid contextual meaning or serves a particular purpose like being a surrogate for a wide natural PRIMARY KEY (PK), then yes, you should proceed that way.
Otherwise, if you do not have said requirement, I consider it would be unnecesary since you have to store and manage a meaningless extra column and (perhaps?) also an additional INDEX in your database.
As usual, you have to analyze each case along with its overall repercussions in order to decide how to carry on.

If I add foreign keys in the foreign keys tab, will these automatically correspond to the correct table or do I have to add these to the columns also?

In this regard, my advice for you is to create your database structure manually, to code your own DDL statements until you get a firm grasp of the subject. If you do so, it will be easier for you to understand the processes that graphical tools are performing “under the hood”.
To me, for instance, creating a statement like the following:
CONSTRAINT FK_PersonPhoneNumber_to_Person FOREIGN KEY (PersonId)
REFERENCES Person (PersonId)

Is much more instructive than using GUI tools to execute this kind of tasks, especially now that you are building your first designs.

And my probably most noobiest question of all... Do I put the foreign keys of phone_number and email in their respective table linking to a person_id or should I put phone_number and email ids in the person table?

Personally, I think that this and all your other questions are completely valid and well contextualized.
Returning to the technical aspects that concern us, this precise inquiry offers a good opportunity to review the two following assertions:

A Person can be reached through zero-one-or-many PhoneNumbers
A PhoneNumber can be used to reach one-to-many People

So, one can conclude that there is a many-to-many relationship between Person and PhoneNumber, therefore, I suggest the creation of an associative table named PersonPhoneNumber to represent said relationship in your database. The PK of this table should be composed by two different columns: PersonId (a FOREIGN KEY [FK] pointing to Person.PersonId) and PhoneNumber (a FK that makes reference to PhoneNumber.Number). For a logical level description of all of the above, see Figure 1.
On the other hand, let us examine the two propositions that follow:

A Person can be contacted via zero-one-or-many EmailAddresses
An EmailAddress can be used to contact exactly one Person

Then, yes, you should set a FK referencing to Person from the EmailAddress table, and this table should have a composite PK too, which would be comprised of the columns PersonId and Address. In this manner, you can ensure that the same combination of EmailAddress.PersonId and EmailAddress.Address can be inserted only once.
If you also wish to ensure that a given EmailAddres.Address can be stored in one sole row, you just have to establish a UNIQUE CONSTRAINT for this column.
Proposed logical data models
In order to expose my suggestions more clearly, I included four distinct IDEF1X[1] logical models that are shown in Figure 1, Figure 2, Figure 3 and Figure 4. I will provide an explanation of the most relevant features displayed in each one of them in the corresponding sections. You can as well download from Dropbox a PDF that integrates in a single model the majority of the elements under discussion.
Covering your remaining specifications
Relating People and Addresses
Let us inspect the two following (slightly reworded) assertions that are relevant to people and addresses:

A Person can only have one Address
An Address can belong to different People (couples or siblings)

So, to deal with these restrictions, you may opt to implement a data model similar to the one you can see in Figure 1.

Taking into account the referred model, you should follow the next steps:

Create a table called PersonAddress fixing a relationship between Person and Address. Set the columns PersonId and AddressId as the compound PK of this table.

Configure a UNIQUE CONSTRAINT for the PersonAddress.PersonId column to ensure that a specific value can be inserted in at most one row of said table. At the logical level, this circumstance implies that PersonAddress.PersonId has become an ALTERNATE KEY[2].

If the value of AddressId in a determined PersonAddress insertion attempt has not being already stored, then let the insertion continue, otherwise, when such value already exists in a row, you have to check that (a) the PersonId who has registered that AddressId is also registered as  the Marriage.WifeId if the PersonId is a male (datum derived via the Person.GenreCode) or (b) that the PersonId is the Marriage.HusbandId when the PersonId is a female (derived by virtue of Person.GenreCode, as well). If one of these conditions is met in the appropiate situation, then you should let the INSERT go on.

If the above conditions were not fulfilled, there is still a chance for a PersonAddress insertion try to be succesful. You have to check that the PersonId value that is involved in said insertion try shares at least one Progeny.ParentId with the PersonId that has already registered the PersonAddress.AddressId. If this condition is satisfied, then it means that they are stored as Siblings in the database, so this INSERT must succeed.

As in any relational database implementation, you should seriously consider executing your DML operations inside ACID Transactions so that you can protect the integrity and consistency of the data you are working with.
Attending the requirements you added in comments: Addresses and Phone Numbers serving equally Clubs and People
On condition that you want to give the chance for addresses and phone numbers to serve both people and clubs, you might make use of a supertype-subtype relationship. Here is an answer in which I give a more detailed treatment to this kind of structures, in case you are interested.
In the present scenario, you could define Person and Club as subtypes of a new entity named Party, a term commonly used in legal circles to stand for (a) a person or (b) a group of persons (as noted in sense no. 6). With this method, the relationships between Addresses (or PhoneNumbers) and People and Clubs would be defined through Party, the supertype. See Figure 2 for a depiction of this suggestion.

Party and Address
So we can read in this new model that:

A Party keeps zero-one-or-many Addresses
An Address is kept by one-to-many Parties

Thus, there is a many-to-many relationship involving Party and Address that is expressed by way of the PartyAddress entity.
Party and PhoneNumber
Furthermore, we can interpret that:

A Party is reached through zero-one-or-many PhoneNumbers
A PhoneNumber is used by one-to-many Parties

That is why I added the PartyPhoneNumber entity that describes the many-to-many association that takes effect between the Party and PhoneNumber entity types.
Party and Club or Person
Then, it can also be read that:

A Party is either a Club or a Person

Hence, Party supplies a connection from either Clubs or People to Addresses (or PhoneNumbers).
Person as Member of multiple Clubs
As @aldwinaldwin mentiones in his answer, if you want to provide the functionality for a person to be a member of multiple clubs, then you can include a table called ClubMember that would be acting as another many-to-many relaltionship, this time, naturally, interconnecting Person and Club.
I am going to add to the above that this table can also be useful in the goal of storing any concrete person as owner of multiple clubs, by means of the inclusion of the already mentioned IsClubOwner boolean column. In fact, one can say that this new table is the representation of an integral entity type in its own right.
As demonstrated in Figure 3, such table requires a composite PK made up of the columns ClubId and MemberId (a role name[3] given to PersonId), and these columns have to be defined also as FKs pointing, correspondingly, to Club and Person.

A more adaptable structure
Employing this setting you can also comply with the initial rule that states that a Person can be a member of only one Club, but you just have to add a UNIQUE CONSTRAINT to the MemberId column, so that a certain value can be entered in no more than one occasion. So, as you can note, this structure is much more adaptable that the one shown in Figure 1, since by dropping the UNIQUE CONSTRAINT (or INDEX) you would open the aforementioned functionality of permitting a person to become a member of different clubs at the same time.
Member and Owner as separate entity types
As you know, the storage several facts about the role carried out by a person as owner of a club —besides its mere existance in a true or false attribute— can be very advantageous. For example, you may want to keep the effective date in which a definite person became the  owner of a club, hence you can manage this situation by introducing a separate entity type called ClubOwner, as presented in Figure 4.

Once you construct a table based on this new entity type, you can add the fitting columns that represent the characteristics that come into play exclusively when a Person is the Owner of a Club. As depicted, this table would hold a PK composed of the FK columns referencing Person.PersonId and Club.ClubId, this way any combination of ClubOwner.OwnerId (or ClubOwner.PersonId, if you prefer) and ClubOwner.ClubId can be inserted in just one opportunity.
Of course, with this configuration you can still derive in boolean form if a Person is the Owner of a particular Club with the aid of a query that returns a scalar value which can be evaluated to true or false.

Notes
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical papers authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship theory, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.
2. An ALTERNATE KEY is an attribute (or a combination of attributes) that holds values that uniquely identify an entity occurrence but was not chosen as the PK of the pertinent entity type; each entity type can have zero, one or more ALTERNATE KEYS. In an IDEF1X model, they are are indicated as “AK” plus its respective number, e.g., AK1, AK2, etc. They are usually implemented in a SQL DDL structure via a UNIQUE CONSTRAINT (or a UNIQUE INDEX).
3. Role names are denotations (or aliases) assigned to FK attributes in order to express the meaning that they hold within the scope of their respective entity. Their usage is recommended since 1970 by Dr. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.


Select same data-set at different date ranges in a single query what I am trying to do is performing a query that has multiple results of the same query by just editing only a where clause.

The goal is to query a result of the same dataset in different date ranges, but instead of performing multiple separated queries, having one single query that queries the tables X times (depending on the selected date ranges).

For example:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total"
FROM database_two.announcements
INNER JOIN database_one.prospect_evaluations ON database_one.prospect_evaluations.announcement_id = database_two.announcements.id
INNER JOIN database_one.users ON database_one.prospect_evaluations.user_id = database_one.users.id
WHERE (database_two.announcements.deleted_at IS NULL
       AND (database_two.announcements.archived_at IS NOT NULL
            AND database_two.announcements.crmstate != 'Scartata'))
  AND (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) /* THIS IS THE DATE RANGE TO EDIT IN THE SUBQUERY */
GROUP BY database_one.prospect_evaluations.user_id
ORDER BY database_two.announcements.id DESC

What I'd like to have is something like this, in pseudo-sql:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       /* PSEUDO-SQL START { */
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
       /* } PSEUDO-SQL STOP */
FROM database_two.announcements
INNER JOIN database_one.prospect_evaluations ON database_one.prospect_evaluations.announcement_id = database_two.announcements.id
INNER JOIN database_one.users ON database_one.prospect_evaluations.user_id = database_one.users.id
WHERE (database_two.announcements.deleted_at IS NULL
       AND (database_two.announcements.archived_at IS NOT NULL
            AND database_two.announcements.crmstate != 'Scartata'))
GROUP BY database_one.prospect_evaluations.user_id
ORDER BY database_two.announcements.id DESC

Your pseudo-SQL is surprising close to the standard SQL clause FILTER, which has only been implemented by Postgres but unfortunately not by MySQL:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: FILTER
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
...

The good news is that in MySQL you can use equivalent (and standard SQL as well) CASE expressions:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: COUNT(CASE ...)
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
               THEN 1 END) AS "1 Month"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
               THEN 1 END) AS "3 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)
               THEN 1 END) AS "6 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
               THEN 1 END) AS "1 Year"
...

or the more compact but non-standard SUM() that uses the implicit conversions of boolean values (TRUE and FALSE) to 1 and 0:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
          ) AS "1 Month"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
          ) AS "3 Months"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)
          ) AS "6 Months"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
          ) AS "1 Year"
...

By the way, you could use aliases for the various tables in the FROM clauses so the query becomes shorter and usually more readable.

Also the announcements.id is ambiguous in the SELECT orr ORDER BY clause after a GROUP BY, because it is not in the group by list. Use MIN() or MAX() or some other ORDER BY instead.

The query with aliases - and assuming you choose the SUM() solution from above:

SELECT u.firstname,
       u.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR))  AS "1 Year"
FROM database_two.announcements  AS a
JOIN database_one.prospect_evaluations  AS pe
    ON pe.announcement_id = a.id
JOIN database_one.users  AS u
    ON pe.user_id = u.id
WHERE a.deleted_at IS NULL
  AND a.archived_at IS NOT NULL
  AND a.crmstate <> 'Scartata'
GROUP BY pe.user_id
ORDER BY MAX(a.id) DESC ;


Why is this simple join taking so long? I'm trying to run some reporting queries on a MySQL database, and I'm running into serious performance issues for some reason. One table, page_views, is extremely large (~70 million records), so some slowness is to be expected, but it's got lots of indexing, and all the queries are restricted to a certain date range on the started_at field. I'm seeing simple joins taking upwards of ten minutes. The query I've been tossing around lately (which I've stripped way down) looks like this:

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

It's taking about 11 minutes 30 seconds to run. DESCRIBE returns this analysis:

| id | select_type | table          | type | possible_keys        | key         | key_len | ref                                 | rows | Extra       |
+----+-------------+----------------+------+----------------------+-------------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | playpack_media | ref  | media_id,playpack_id | playpack_id | 4       | const                               |  594 |             |
|  1 | SIMPLE      | page_views     | ref  | itemId,started_at    | itemId      | 4       | db_playsmrt.playpack_media.media_id | 4236 | Using where |

It seems to bottleneck at the "copying to tmp table" stage. I've tried increasing tmp_table_size and max_heap_table_size to 256M and key_buffer_size to 64M - the latter change dropped the time from ~18 minutes to ~11, which is a big improvement but still terrible, and a further increase to 128M didn't do much.

One thing I noticed while hammmering at this is that page_views uses MyISAM and playpack_media uses InnoDB, which I'm reading can be an issue. However, changing page_views to InnoDB on my (much smaller) development server makes it dramatically slower. It's possible that it becomes worthwhile on larger tables, but just making the experiment would take a long time.

Does anyone have an idea what's going on here? What else should I be looking at?

Look at the original query

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

You should do a couple of things.

First, create to compound indexes

ALTER TABLE page_views     ADD INDEX started_at_itemid_ndx    (started_at,itemid);
ALTER TABLE playpack_media ADD INDEX playpack_id_media_Id_ndx (playpack_id,media_Id)

Try to refactor the query

SELECT COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId;

Perhaps breaking down the views by ItemID

SELECT IFNULL(A.media_id,'Total') Media,COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId
GROUP BY A.media_id WITH ROLLUP;


Master ip has changed in postgresql replication I have postgresql replication system with two machines, suppose I want to change the master ip,the streaming to slave machine will stop. Can I resume streaming with the new master ip instead of doing the replication from scratch.

Easy, just change host in primary_conninfo and restart the standby.
This replication parameter can be found in recovery.conf before v12 and in postgresql.conf after that.


Execute query cache on startup - Reasonable? I have a rather database intensive application for a server which is intended to be turned off and on throughout the day. The issue comes at first use of the application after startup; it is slower than intended due to the large volumes of queries.

Once the query cache has taken ahold it is much more efficient and user experience pleasing. Obviously for the user experience I would like this throughout and not after the queries have been cached.

Is it reasonable, in terms of performance and error handling, to run a cron script (php) on startup to execute some queries so they can be cached or is there a method to save the mysql cache and load on the service startup?

please refer to this link? this should answer your question

Yes, 100% reasonable to make use of the MySQL caching system!

How to "warm up" a cold DB server?

  Warm-up is needed because the best performance of a database is
  achieved when its various caches are populated with the most relevant
  data, since reading from memory is generally substantially faster than
  reading from disk.

  InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS
  cache. Then there's also the MySQL query cache. MySQL has the open
  table cache. Some or all of these structures contribute to the ideal
  performance of a server. When a server is handling traffic that is
  characteristic of its workload, it tends to have the most frequently
  used -- and therefore presumably the most relevant data -- already
  cached.

  Take the load off of an active MySQL server and you will not see it
  start closing tables and evicting data from its caches. If you leave
  it running, then come back to it tomorrow, you should find that it's
  still as warm as you left it.


Why restoring a large sql file via SSH seems to quit partially through the process I have a large (~5GB) .sql file I am attempting to restore via SSH, it seems to quit in the middle of the restore process.

When I come back hours later after issuing the command (the file has 7 tables), there are only 5 tables and nothing is being processed.

Is there a log file I can view to see why it quit half-way through? Is there a configuration option I am probably missing? Any ideas would be greatly appreciated. Thanks!

The command I am using:

mysql -u username -p database_name < file.sql

Either the problem is with MySQL connections being killed silently, in which case please set:

[mysqld]
net_read_timeout = 7200
net_write_timeout = 7200

Defaults are extremely low (30 seconds). These params set the number of seconds waiting upon a command before aborting a connection.

Or the problem is as you suggest with your SSH. In which case use screen. If you don't know about screen, you are missing on a major tool. In very brief, it splits your shell, and allows you to close the terminal without breaking ongoing processes. It allows you to re-connect at another time or place, share your connection with others, and more.


MySQL Connections slows down my website I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database.

I ran these queries

show status like 'Connections';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 78    |
+---------------+-------+
1 row in set (0.01 sec)

show status like "%thread%";

+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 3     |
| Threads_connected                        | 6     |
| Threads_created                          | 17    |
| Threads_running                          | 6     |
+------------------------------------------+-------+
8 rows in set (0.01 sec)

show variables like "%max_connections%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.02 sec)

Version :
+----------------------------------+
| version()                        |
+----------------------------------+
| 5.6.35-1+deb.sury.org~xenial+0.1 |
+----------------------------------+
1 row in set (0.00 sec)

What i found out is, when ever the # of connections above 20, the site becomes very slow to load.

I am not sure why this is happening, or whether this is normal. I tried to adjust the 'max_connections' variables but still no luck. Has anyone ever got this problem as well? How can i optimize this ?

Thanks for the help.

Here's a log related to Connections:

+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 1     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 48    |
| Max_used_connections                          | 6     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 6     |
+-----------------------------------------------+-------+
14 rows in set (0.01 sec)

You do not mention what language your application is in but if you are connecting on every page requested you could add a lot of speed simply by using a database connection pool. That way you application will not need so many connections and will not have to connect on every request, giving you extra speed simply because you don't reconnect every time and also reducing drastically the number of concurrent connections it uses, which seems to be your problem.

I have only seen problems like this you mention on Windows machines. Most of this problems are also highly attenuated simply by moving your db to GNU/Linux (or BSD).


Migrate data from MySQL to SQL Server, database names differ I'm using SQL Server Migration Assistant for data migration from MySQL to SQL Server. I would like to know whether it is possible to migrate data from databases of different names but tables inside databases have the same names.

Yes you can migrate database and rename database name.


Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases I'm looking for software tools to move database schema for Oracle, SQL Server and Netezza.  We use Oracle for our ODS, Netezza for our Data Warehouse (for it's bulk loading capabilities) and SQL Server for internal tool development.

When we move data schema from one environment to another, it's all done manually.  This takes a lot of time.  And we use Informatica to move the actual data.  To my knowledge, Informatica cannot move database schema.  What tools can I buy that can move database schema for all three database management systems?

I see Red Gate SQL Compare can move schema.  But it doesn't look like it can handle Oracle or Netezza.  If you don't know one single tool that can be purchased to move schema for all 3 DBMSs, can you give let me know what tools folks recommend for each one?

PS: I didn't see a Stack Exchange [tag] for Netezza.  But I believe Netezza is based on PostGres, so I included that in the tag list.

In the past, I have used ErWin from Computer Associates and PowerDesigner to move schema between RDBMSes. It's been many years since then, and I would evaluate their current feature set if I had to pick something today.


Can't select database for transaction log backup in maintenance plan I am running into a problem while trying to save a transaction log database backup maintenance task:

Add the 'Back Up Database Task' to the maintenance plan.
Edit the task.
Select backup type: 'Transaction Log'.
Select any database running with Full Recovery Model.
Close the task.
Re-open the task. Notice that the database I selected is not checked anymore.

I can select all databases, all user databases, specific databases...none of them ever save. Every time I re-open the task that field is switched back to < Select one or more > option.

I've connected as user sa and I've connected with Windows Authentication. Same result. Also, I've double-checked that the databases I'm trying to backup are in Full recovery model.

I'm running SQL Server 2005 SP2 on Windows 2003 R2 SP2.

EDIT: This happens even if the database had a full backup run against it.

See this Connect item, which is almost identical to the scenario you're trying -- they're close enough that I would say that it's highly likely to be the same issue. I would attempt to reproduce the exact same scenario presented in the bug report (I assume you will be able to).

While it's unclear in which release the fix for that bug landed, by the date of Microsoft's response, I believe this should be fixed in SP3.

If applying a service pack is not an option at this time, you'll want to go with a trusted 3rd-party solution, as mentioned in the other answer.


Postgres role does not inherit permissions of superuser role via membership in that role In the snippet below, the role writer is a member of a superuser role named admin. The admin role can insert rows into a table but the writer role cannot insert rows into the same table.  Why does the writer role not inherit permissions from the superuser role admin?

CREATE TABLE test_check_roles( id SERIAL NOT NULL );
CREATE ROLE admin SUPERUSER LOGIN;

-- Insert succeeds as the admin role.
SET ROLE admin;
INSERT INTO test_check_roles (id) VALUES (DEFAULT);

-- Insert fails as the writer role with:
--   ERROR:  permission denied for table test_check_roles
CREATE ROLE writer LOGIN INHERIT;
GRANT admin TO writer;
SET ROLE writer;
INSERT INTO test_check_roles (id) VALUES (DEFAULT);

Based on the documentation below (Postgres 12 - Role Membership), I expected writer to have all privileges of the admin user since the writer role has the INHERIT tag.

  Member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles.

Reproducible example

docker run --name pg-test -d --rm \
    --env POSTGRES_HOST_AUTH_METHOD=trust \
    -p 54321:5432 \
    postgres:12.2

psql -h localhost -p 54321 -U postgres -d postgres \
    -c "CREATE TABLE test_check_roles( id SERIAL NOT NULL )" \
    -c "CREATE ROLE admin SUPERUSER LOGIN" \
    -c "SET ROLE admin" \
    -c "INSERT INTO test_check_roles (id) VALUES (DEFAULT)" \
    -c "CREATE ROLE writer LOGIN INHERIT" \
    -c "GRANT admin TO writer" \
    -c "SET ROLE writer" \
    -c "INSERT INTO test_check_roles (id) VALUES (DEFAULT)"

docker stop pg-test

Related questions

Postgres role permission problem: Related to ALTER DEFAULT PRIVILEGES.
Why is a new user allowed to create a table? - Has a good answer describing that privileges aren't hierarchial.

Observations

If I run CREATE TABLE as the admin role instead of the postgres role, the example works. It seems the permission are somewhere lost between postgres owning the table, the admin having superuser, and writer being a member of admin. I don't follow why a table created by the postgres role is accessible by admin by not by writer.

SUPERUSER doesn't inherit; it's an attribute of a role rather than a permission.

  The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute.

See https://www.postgresql.org/docs/current/role-membership.html


Incredibly long execution time for a update query? I am using MySQL 5.6.

In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value.

UPDATE invoices
SET twoWeekAlert = DATE_ADD(now(), INTERVAL 2 WEEK)
WHERE state = 6;

There are only 205 records that have state =6 and 3,500 total records.

After 5 minutes I canceled the query, made an index on the state column, and tried again.  After 10  minutes I canceled that one.

What is going on, is this a known issue with MySQL updating using a datetime calculation function or something?  I worry because I know I will have to run similar type updates in the future and I can't have it take that long.

The EXPLAIN statement I believe is telling me it IS using my index:

selectType   table     type   possible_keys     key        key_len    ref    rows   extra
SIMPLE       invoices  range  stateIndex        stateindex  2          const  205 using where

There were no warnings in my explain statement.

The CREATE statement of my table

CREATE TABLE `invoices` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parentSOId` int(11) DEFAULT NULL,
  `parentProjectId` int(11) DEFAULT NULL,
  `status` int(1) DEFAULT '1',
  `active` int(1) DEFAULT '1',
  `dateEntered` varchar(45) DEFAULT NULL,
  `dateDue` varchar(45) DEFAULT NULL,
  `individualId` int(11) DEFAULT '-1',
  `amount` decimal(11,2) DEFAULT '0.00',
  `margin` decimal(11,2) DEFAULT '0.00',
  `comment` varchar(500) DEFAULT '',
  `custContactId` int(11) DEFAULT '-1',
  `custBuyerId` int(11) DEFAULT '-1',
  `taxable` int(11) DEFAULT NULL,
  `taxAmount` decimal(11,2) DEFAULT NULL,
  `totalAmount` decimal(11,2) DEFAULT NULL,
  `paymentTerms` int(11) DEFAULT '-1',
  `type` int(11) DEFAULT '-1',
  `shipVia` int(11) DEFAULT '-1',
  `manTax` int(1) DEFAULT '0',
  `state` tinyint(4) DEFAULT '0',
  `sentToContNotNeeded` int(1) DEFAULT '0',
  `sentToAcctNotNeeded` int(1) DEFAULT '0',
  `twoWeekAlert` datetime DEFAULT NULL,
  `threeWeekAlert` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `invoiceNum` (`number`),
  KEY `invoiceName` (`name`),
  KEY `fk-listprojects_idx` (`parentProjectId`),
  KEY `soIdIndex` (`parentSOId`),
  KEY `stateInd` (`state`),
  CONSTRAINT `fk-listprojects` FOREIGN KEY (`parentProjectId`) REFERENCES `listprojects` (`idx`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3604 DEFAULT CHARSET=latin1;

Things I've also tried:

Doing it by primary key ie WHERE idx IN (list of ids)

Instead of using datetime, just using date since it is enough for my needs and using DATE_ADD(curDate(), INTERVAL 2 WEEK)

I've tried both ways: NOW() with a datetime type and curDate() with a date type.  Both had the same issue.

Running it on a much stronger machine. Still the same issue.

Luckily I was just told to not retroactively affect old records so I will only be doing one record at a time, which seemed to work.  I am still very curious why this seems to take forever especially when I am the only connection on a dev database.

My server is local, in office, running on a machine in the closet. So, bare metal, not cloud or ESX.

SELECT IFNULL(state,'Total') state_value, COUNT(1) rowcount
FROM invoices
GROUP BY state WITH ROLLUP;

Results:

-1  14
0   3217
2   5
4   54
5   9
6   205
Total   3504

SHOW INDEX ON invoices results:

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

Your problem is simple
PROBLEM #1 : KEY DISTRIBUTION
The MySQL Query Optimizer evaluates the best course of action and publishes it in the EXPLAIN plan. Note that the EXPLAIN plan says selectType SIMPLE while using the index on the state. That is an index scan.
I have a basic rule-of-thumb I always go by : If an index has to read more that 5% of a table while perusing an index, it starts doing scans. You are basically at a tipping point. Why ???

205 rows to be updated
3504 total rows
5.85% of the table rows being read/updated

I am not just coming up with this. I have always mentioned this in the DBA StackExchange (Se my post from Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? and the other posts mentioned in it)
PROBLEM #2 : CONFIGURATION OF HARDWARE
The table is way too small to say it's just indexing. If the hardware you are using is small (1 vCPU), don't expect performance of any kind with MySQL 5.6. There are corner cases where MySQL 4.1 would outperform MySQL 5.6 in a single CPU environment. You are probably in that corner right now.
See my posts

Apr 26, 2012 : Is the CPU performance relevant for a database server?
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

WHERE TO GO FROM HERE ...
You best bet at this point would be to run OPTIMIZE TABLE invoices;. This will shrink that table (eliminating fragmentation) and refresh the index stats. In a high-write production environment, you could run ANALYZE TABLE invoices; to refresh index stats only and run OPTIMIZE TABLE invoices; during a maintenance window.
Also, try setting up more that 1 CPU for test servers.
GIVE IT A TRY !!!


Mongodb list current connection I am using a db.currentOp() to check the current connection in Mongodb.
How can I get the total number of all record and how can I get all the "client" IP in a one column?
Thanks

db.currentOp()
{
        "inprog" : [
                {
                        "desc" : "WT RecordStoreThread: local.oplog.rs",
                        "threadId" : "1068",
                        "active" : true,
                        "opid" : 77300,
                        "secs_running" : 712,
                        "microsecs_running" : NumberLong(712734301),
                        "op" : "none",
                        "ns" : "local.oplog.rs",
                        "query" : {

                        },
                        "numYields" : 0,
                        "locks" : {

                        },
                        "waitingForLock" : false,
                        "lockStats" : {
                                "Global" : {
                                        "acquireCount" : {
                                                "r" : NumberLong(1),
                                                "w" : NumberLong(1)
                                        }
                                },
                                "Database" : {
                                        "acquireCount" : {
                                                "w" : NumberLong(1)
                                        }
                                },
                                "oplog" : {
                                        "acquireCount" : {
                                                "w" : NumberLong(1)
                                        }
                                }
                        }
                },
            {
                    "desc" : "conn52",
                    "threadId" : "4692",
                    "connectionId" : 52,
                    "client" : "10.10.50.218:60909",
                    "active" : true,
                    "opid" : 101277,
                    "secs_running" : 2,
                    "microsecs_running" : NumberLong(2883686),
                    "op" : "getmore",
                    "ns" : "local.oplog.rs",
                    "query" : {
                            "getMore" : NumberLong("15696192304"),
                            "collection" : "oplog.rs",
                            "maxTimeMS" : NumberLong(5000),
                            "term" : NumberLong(117),
                            "lastKnownCommittedOpTime" : {
                                    "ts" : Timestamp(1501842736, 18),
                                    "t" : NumberLong(117)
                            }
                    },
                    "planSummary" : "COLLSCAN",
                    "numYields" : 0,
                    "locks" : {

                    },
                    "waitingForLock" : false,
                    "lockStats" : {
                            "Global" : {
                                    "acquireCount" : {
                                            "r" : NumberLong(2)
                                    }
                            },
                            "Database" : {
                                    "acquireCount" : {
                                            "r" : NumberLong(1)
                                    }
                            },
                            "oplog" : {
                                    "acquireCount" : {
                                            "r" : NumberLong(1)
                                    }
                            }
                    }
            },
....
...
..

This will help to aggregate the information -

Connection Count by ClientIP, with Total

For Mongo Shell:

db.currentOp(true).inprog.reduce((accumulator, connection) => { ipaddress = connection.client ? connection.client.split(":")[0] : "unknown"; accumulator[ipaddress] = (accumulator[ipaddress] || 0) + 1; accumulator["TOTAL_CONNECTION_COUNT"]++; return accumulator; }, { TOTAL_CONNECTION_COUNT: 0 })

Formatted:

db.currentOp(true).inprog.reduce(
  (accumulator, connection) => {
    ipaddress = connection.client ? connection.client.split(":")[0] : "unknown";
    accumulator[ipaddress] = (accumulator[ipaddress] || 0) + 1;
    accumulator["TOTAL_CONNECTION_COUNT"]++;
    return accumulator;
  },
  { TOTAL_CONNECTION_COUNT: 0 }
)

Example Output:

{
    "TOTAL_CONNECTION_COUNT" : 331,
    "192.168.253.72" : 8,
    "192.168.254.42" : 17,
    "127.0.0.1" : 3,
    "192.168.248.66" : 2,
    "11.178.12.244" : 2,
    "unknown" : 41,
    "3.100.12.33" : 86,
    "11.148.23.34" : 168,
    "81.127.34.11" : 1,
    "84.147.25.17" : 3
}

(the 192.x.x.x addresses are internal Atlas monitoring)


SQL Server 2005 Express on Windows 8 I need to install SQL Server 2005 Express on Windows 8 Home Edition x64.

But I get a compatibility error!

I have tried SP1, SP2, SP3 of SQL Server 2005.

Visual Studio 2008 is working fine.

Any suggestions?

According to this Microsoft KB article, SQL Server 2005 (in any form, even with all the Service Packs installed) is not supported on Windows 8.

SQL Server 2008 is supported, as long as you install Service Pack 3.
SQL Server 2008 R2 is supported, as long as you install Service Pack
1.
SQL Server 2012 is supported with any version.


FOREIGN KEY Constraint Failed: SQlite3 I have two tables, created as below,

sqlite> CREATE TABLE Artists(
   ...> ArtistId INTEGER PRIMARY KEY,
   ...> ArtistName TEXT NOT NULL
   ...> );
sqlite> CREATE TABLE Albums(
   ...> AlbumId INTEGER PRIMARY KEY,
   ...> AlbumName TEXT NOT NULL,
   ...> Year TEXT NOT NULL,
   ...> ArtistId INTEGER NOT NULL,
   ...> FOREIGN KEY(ArtistId) REFERENCES Artists(ArtistId)
   ...> );

.table returns the following,

sqlite> .table
Albums   Artists

foreign key pragma is set to ON

sqlite> PRAGMA foreign_keys;
1

However, When I try to insert data as below results in FOREIGN KEY constraint failed.

sqlite> INSERT INTO Albums VALUES (NULL, 'Killers', '1981', 7);
Error: FOREIGN KEY constraint failed
sqlite>

I am a database learner. Could someone point out what is that I am doing wrong?

You try to insert Albums.ArtistId value (7) which is absent in Artisis.ArtistId values list now. So insertion fails.

You cannot insert album of <unknown>, FK claims this is impossible, and server prevents this.

Insert artist with ArtistId=7 first, then insert his albums.


Monitoring replication status from the subscriber I have two databases that are populated by our vendor using replication from their databases. I do not have access to their distributor db. But I want to monitor from our end whether the data has been refreshed as per schedule.

Is there a way to do this? All the blogs/posts I have seen require access to the distribution db.

You can use the canary method to monitor replication status on the subscriber. Check it out here in the "Easy Replication Monitoring: Alert on Latency with Canary Tables" section.

With this method, you would be writing to a table on the publisher that has just one column. Update the table every minute with CURRENT_TIMESTAMP. Monitor the table on the subscriber and fire off an alert if the difference between the table and the current time has exceeded your threshold.


Removing AG and clustering - make nodes stand-alone I am wanting to make a 2-node Always On cluster no longer a cluster (make them (2) stand-alone machines).  I am trying to upgrade Sql and the wizard keeps getting stopped before it does it checks because it is detecting it is a cluster and the two machines no longer are able to talk to each other.  I have already deleted the AG and removed the Always-On check from the properties of the instance. The application is already coded to connect to the Primary so this is not an issue.  I am now thinking I will need to break (delete) the cluster and remove it but I want to keep one of the nodes from being affected (they are prod servers) and up and running with no outage.  Again, this is a 2-node Always-On cluster without shared storage (each server has it own local attached).

Has anyone ever done this?  After the cluster is deleted, do the individual machines stay okay (up) and functional during it (deleting the cluster)?

Thanks

Yes, the machines stay up and running when the cluster is destroyed and it does not require a reboot.  However, I would still recommend doing this after business hours if possible because things don't always work as designed.


Can't get SQL Server 2012 RTM LocalDB working on Server 2008/x86/ SQL Server 2008 machine I have installed SQL Server 2012 and LocalDB on my development machine, and am able to connect to LocalDB using both the SQL Server Management tool 2012 and from within my winform application using a newly modifed connection string Shown below:

Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;Connect Timeout=60

The problem arises when I attempt to port my application onto a Windows 2008 Server which is running SQL Server 2008, (I know I could skip the LocalDB installation and just make use of SQL Server 2008, but I am just testing the installation of my application on this machine.. )

I have installed both SQL Server 2012 LocalDB and updated .net 4.01 to .net 4.02 using the kb article located at  http://support.microsoft.com/kb/2544514

I keep getting getting an error when I attempt to connect to (LocalDB)\v11.0 from both my application and from the SQL Server Management 2008 Tool, I have also manually started up (LocalDB)\v11.0 from cmd->SQLLocalDB -s "v11.0" and then attempted to connect once again using the NP

i.e np:\\.\pipe\LOCALDB#8B2C7490\tsql\query

When I use the named pipe everything connects fine, so I am assuming that as this works then the installation of LocalDB is also correct.

What do you suggest I do to resolve this situation?

I am also concerned as to the proper implementation moving forward with the deployment of my application, am I supposed to now include .Net 4.03 as a prerequisite? If so is there already a clickonce bootstrap manifest available for this or do I need to create one.. so that my app works correctly moving forward, I'm really concerned that LocalDB might break it.. as so far I'm not having much luck..

I guess if all else fails I can go back to using SQL Server Express 2008 as this implementation worked correctly before..

Thanks,

From what I could see, it appears that Microsoft doesn't support LocalDb out of the box in Windows Server 2008. I just got the Web Platform Installer and tried searching for localdb and no entries were found. I went to the download site and grabbed only the LocalDb.msi installer and installed it in Win Server 2008. Created a default instance:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqllocaldb.exe c "v11.0" -s

Then tried to connect to it in my ASP.NET MVC 4 app but it didn't work. This works in Windows 8 RC.

I simply decided to go with SQL Express 2008 since this one the Web Platform Installer found!


How to pass parameter value for ExecuteReader() - powershell I am trying to run following powershell script. How Can I pass parameter value for @P0= '2019-11-22' and @P1 = '123456789' during runtime?

$sqlconnection = New-Object System.Data.SqlClient.SqlConnection
$sqlconnection.ConnectionString = "server = Servername;Database=TestDB;Integrated security=true"
$sqlconnection.OPEN()

$sqlCmnd = New-OBJECT SYSTEM.DATA.SqlClient.SqlCommand
$sqlCmnd.Connection = $sqlconnection
$sqlCmnd.CommandText = "select distinct table1.column1, ..........  where table2.column='value' and table2.coulumn=@P0 and table2.column=@P1 order by table1.Column1"

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@P0",[Data.SQLDBType]::varchar, 8000))) | OUT-NULL
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@P1",[Data.SQLDBType]::varchar, 8000))) | OUT-NULL

$sqlCmnd.ExecuteReader()
$sqlconnection.close()

You can use PowerShell variables or script parameters to update in your script:

$P0 = "Value 0"
$P1 = "Value 1"

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("P0",[Data.SQLDBType]::varchar, 8000))).Value = $P0
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("P1",[Data.SQLDBType]::varchar, 8000))).Value = $P1


Why can't I obtain OBJECT_NAME() for a temporary table? According to this MSDN article http://msdn.microsoft.com/en-us/library/ms190328.aspx

  When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: SELECT OBJECT_ID('tempdb..#mytemptable').

But when I am actually creating a temp table in a database other than tempdb the function is not working as specified:

use beta1
create table #t(id int)
select object_name(object_id('tempdb..#t')) as from_beta1
use tempdb
select object_name(object_id('tempdb..#t')) as from_tempdb
drop table #t

This is what I see:

from_beta1
----------
NULL

from_tempdb
---------------
#t_________________...______0000000000B9

What am I missing or doing wrong?

You have to tell object_name that it needs to look in the metadata of tempdb.

select object_name(object_id('tempdb..#t'), db_id('tempdb')) as from_beta1


Is there a better way to summarize a table containing events of different types by identifier? I have this table definition for sensor events:
                      Table "public.sen_sensor_event"
   Column    |            Type             | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
 dtype       | character varying(31)       |           | not null |
 id          | bigint                      |           | not null |
 created     | timestamp without time zone |           |          |
 type        | character varying(255)      |           | not null |
 temperature | double precision            |           |          |
 sensor_id   | bigint                      |           | not null |
 updated     | timestamp without time zone |           |          |

I'm looking to get a summary for each sensor for each event type. Currently I'm doing it via this materialized view which gives the created timestamp of most recent event for each type (mapped to an enum in code) and a total event count.
CREATE MATERIALIZED VIEW public.sen_sensor_summary AS
 SELECT x.belongs_to,
    x.sensor_id,
    x.name,
    y.event_count,
    y.temperature,
    y.temperature_created,
    y.human_motion_created,
    y.presence_created,
    y.heartbeat_created,
    y.startup_created
   FROM (public.sen_sensor x
     FULL JOIN ( SELECT COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id) AS sensor_id,
            z.event_count,
            a.temperature,
            a.created AS temperature_created,
            b.created AS human_motion_created,
            c.created AS presence_created,
            d.created AS heartbeat_created,
            e.created AS startup_created
           FROM (((((( SELECT DISTINCT ON (z_1.sensor_id) z_1.sensor_id,
                    count(z_1.sensor_id) AS event_count
                   FROM public.sen_sensor_event z_1
                  GROUP BY z_1.sensor_id
                  ORDER BY z_1.sensor_id) z
             FULL JOIN ( SELECT DISTINCT ON (a_1.sensor_id) a_1.sensor_id,
                    a_1.created,
                    a_1.temperature
                   FROM public.sen_sensor_event a_1
                  WHERE ((a_1.type)::text = 'TEMPERATURE'::text)
                  ORDER BY a_1.sensor_id, a_1.created DESC) a ON ((z.sensor_id = a.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (b_1.sensor_id) b_1.sensor_id,
                    b_1.created
                   FROM public.sen_sensor_event b_1
                  WHERE ((b_1.type)::text = 'HUMAN_MOTION'::text)
                  ORDER BY b_1.sensor_id, b_1.created DESC) b ON ((z.sensor_id = b.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (c_1.sensor_id) c_1.sensor_id,
                    c_1.created
                   FROM public.sen_sensor_event c_1
                  WHERE ((c_1.type)::text = 'PRESENCE'::text)
                  ORDER BY c_1.sensor_id, c_1.created DESC) c ON ((z.sensor_id = c.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (d_1.sensor_id) d_1.sensor_id,
                    d_1.created
                   FROM public.sen_sensor_event d_1
                  WHERE ((d_1.type)::text = 'HEARTBEAT'::text)
                  ORDER BY d_1.sensor_id, d_1.created DESC) d ON ((z.sensor_id = d.sensor_id)))
             FULL JOIN ( SELECT DISTINCT ON (e_1.sensor_id) e_1.sensor_id,
                    e_1.created
                   FROM public.sen_sensor_event e_1
                  WHERE ((e_1.type)::text = 'STARTUP'::text)
                  ORDER BY e_1.sensor_id, e_1.created DESC) e ON ((z.sensor_id = e.sensor_id)))
          ORDER BY COALESCE(z.sensor_id, a.sensor_id, b.sensor_id, c.sensor_id, d.sensor_id, e.sensor_id)) y ON ((x.id = y.sensor_id)))
  ORDER BY x.name
  WITH NO DATA;

I like the resulting view, which I map to a SensorSummary object in Java via Hibernate. However, for each new event type I add I need to add a new FULL JOIN section to the view definition.
Is there a more dynamic way to define the materialized view so that when a new type value appears it is automatically added to the view?

There is no way where you don't have to touch your query when new types are introduced, because the number and type of the columns of a query must be known to the database before the query is actually run (they are evaluated at parse time).
But you can simplify your query by using filtered aggregation to get the different created columns in the output. You also don't need a full join as you make sen_sensor the driving table. A left join is enough in this case (assuming you never have sensors in the even table that don't exist in the sensor table).
SELECT x.belongs_to,
       x.sensor_id,
       x.name,
       y.event_count,
       y.temperature[1] as temperature,
       y.temperature_created,
       y.human_motion_created,
       y.presence_created,
       y.heartbeat_created,
       y.startup_created
FROM sen_sensor x
  left join (
      select sensor_id,
             count(*) as event_count,
             array_agg(temperature order by created desc) filter (where type = 'TEMPERATURE') temperature,
             max(created) filter (where type = 'TEMPERATURE') as temperature_created,
             max(created) filter (where type = 'HUMAN_MOTION') as human_motion_created,
             max(created) filter (where type = 'PRESENCE') as presence_created,
             max(created) filter (where type = 'HEARTBEAT') as heartbeat_created,
             max(created) filter (where type = 'STARTUP') as startup_created
      from sen_sensor_event
      group by sensor_id
  ) as y on y.sensor_id = x.sensor_id

The array_agg(temperature ...) is necessary to get the "latest temperature" as Postgres has no "last_value" as an ordered-set aggregate function (only as a window function). Aggregating the values into a sorted array and then picking the first one serves essentially the same purpose.
When a new type is added, you only add a new max(...) filter (...) expression, which still needs a manual change, but is a lot less to type. In fact, you could have a function (or procedure) that generates the necessary SQL dynamically based on the available types.
This should also be substantially faster than your solution.


Optimize MySQL average query for millions of rows I have 4 tables (I've specified main columns for each):

monitors (id)
monitor_node (monitor_id, node_id, average_response_time)
monitor_checks (id, monitor_id, node_id, response_time)
nodes (id)

Their relations:

1 monitor - N checks

N monitor - N nodes

1 check - 1 monitor & 1 node

So, I'm logging a lot of checks for each monitor. Monitor_checks will have millions of rows (approximately up to 500 millions).

When I insert another bunch of checks (~1k checks), I need to calculate average response time per  node (table monitor_node, column average_response_time).

I'm pretty sure I'm doing it the wrong way and I need a faster solution. What I do now: after inserting 1k rows in monitor_checks I calculate average response time for each monitor (grouping by monitor_id). Then, based on this info I make an array and use insert ... on duplicate key update for monitor_node table, in order to do bulk update. Apart from average response time I calculate some other attributes, which goes along with response time in this bulk update.

Making the array with info and executing insert ... on duplicate key update is fast enough.

The slow query is:

select monitor_id, avg(response_time) as avg_response_time
from `monitor_checks`
where `node_id` = 2
group by `monitor_id`

which takes like ~10-20 seconds for ~4m rows I guess.

I also realised that it's not necessary to get average time based on all checks, I can as well use last 50-100 rows. But I couldn't figure out how to do this. I got a fancy solution for grouping checks for each monitor and limit them for some number of rows: How to get latest 2 records of each group but it took way too long as well.

So the question is: how to quickly recalculate average response times in monitor_node when inserting ~1k rows in monitor_checks table?

DB Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bd95afc030361bf1d87f8bc5c3935c2f

Final desired result:

monitor_id  node_id average_response_time
1   1   0.30
1   2   0.25
2   1   0.55
2   2   0.65

I would add an array to monitor_node, containing the most recent 100 response times.  When adding recent response times, drop those exceeding 100.  No need for extra data when a new average has to be calculated.

As an image may say more than a thousand words, I've updated the DB Fiddle.  A trigger does the job, but that logic could be incorporated in the job doing the inserts.

create trigger monitor_checks_air
  after insert on monitor_checks for each row
  begin
  update monitor_node mn
    set mn.rec_resp_times =
          json_extract(
             json_array_insert(mn.rec_resp_times,'$[0]',new.response_time),
             '$[0 to 99]'),
        mn.average_response_time = (
            select sum(jt.rt)
              from json_table(mn.rec_resp_times,
                             '$[*]' columns( rt double path '$[0]')) as jt
          )
          / json_length(mn.rec_resp_times)
    where mn.monitor_id = new.monitor_id
      and mn.node_id    = new.node_id;
  end;

The fastest query is the one you don't execute.


Stored Procedure Is Slower Than Query Due to Collation I'm running two servers with MySql installed.  I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development and as a stand-alone query.  On my production server, I have a stored procedure which simply does:

SELECT * FROM table WHERE uid = p_uid;

p_uid is specified as: p_uid VARCHAR(36) and takes an input like:

'e42018af-e0c5-11e5-a505-f8b156b860fc'

The column is VarChar(36) as well.  When I create a second stored procedure that returns the EXPLAIN, it shows plainly that the index is not used.  The index is used appropriately if I execute the query outside of the stored procedure.

I've seen a number of mentions of this possibly being a collation issue.  The collation of the database, table, and column are latin1 on both servers.  I've tried changing the query to specify a character/collation:

SELECT * FROM table WHERE uid =
CONVERT(p_uid USING latin1) COLLATE latin1_swedish_ci;

and this seemed to do the trick.  Even though this worked, I'm not happy with this solution as handling different and erratic behavior from development to production is not a scalable or efficient plan.

How can I prevent this from happening again?

When declaring HEX strings such as UUIDs, or when declaring other strings with limited character set needs, such as country_code, postal_code, zip_code, etc, declare them to be CHARACTER SET ascii.  It can be applied just to the column, letting the rest of your columns be utf8mb4 or whatever.

And be consistent between tables so that JOIN works efficient.  And, as Rolando says`, be consistent with Stored Routine arguments, etc.

You can either let the collation for ascii be defaulted, or you can be explicit.  But be sure to be consistent.  Mismatched collation is as bad for performance as mismatched character set.

(If such fields are already latin1, there is no need to switch to ascii.)

UUIDs have a nasty problem when the dataset becomes huge.  Because of the seeming randomness of the strings, caching becomes useless when the table is bigger than cache.  One small improvement is to convert to BINARY(16) to save space and make it slightly more cacheable.  More discussion.

BTW, SHOW CREATE PROCEDURE procname \G provides the info at the end.  For example:

mysql> show create function GCDist\G
*************************** 1. row ***************************
            Function: GCDist
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `GCDist`(
    ...
BEGIN
    ...
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci


Is there any benefit to having one sql server login for multiple users? I just entered a development project where the developers use the same SQL Server login to connect to a central database for their work. I have never come across this before, as each dev usually has their own login.

Are there any benefits to using this model?
Is there a limit to the number of simultaneous connections for the same SQL Server login?

While there is no limit on the number of simultaneous connections by a single login, other than the usual @@MAX_CONNECTIONS value, using one login for multiple users or developers is generally frowned on because doing so makes it difficult or impossible to limit permissions on a per-person basis. (You may still be able to identify/contain people based on something like a workstation name, but this would require much hackery on the DBA's part, while simply using seperete logins addresses the issue.) Such "well-known" logins also have a way of getting hard-coded into things, along with the well-known password, and then developers and users become very resistant to ever changing that password. With so many ways to get onto a corporate network these days (a rogue LogMeIn running somewhere, for example) being able to turn database access off via an AD group is beneficial.

Using a single login for a web application, which can have many simultaneous users, is more common. It might not be feasible to give out SQL Server logins or AD logins to individuals for large sites (for example, facebook). In that case, there is a benefit in that IIS can use connection pooling.

I haven't worked in an environment where it was standard operating procedure to use a shared login like that for many years, although there were some smaller/older projects where a shared login was used (and sometimes abused).


Maximum overall size of MariaDB binary logs I am looking for a way to prevent MariaDB binary logs from taking a huge size when they grow faster than usual.

variable max_binlog_size set the maximum size of ONE binary logfile.
variable expire_log_days set the number of days after which the binary logs are removed.

But is there a way to set a "maximum overall size" of ALL binary log files? or, maybe simpler, a maximum number of binary log files, which would overload expire_log_days retention...

max_binlog_size
With regards to max_binlog_size, what you are asking is not possible. Why ?
On May 05, 2017, I answered the question MySQL - Binary Log File size growing more than the maximum limit. I mentioned the following from the MySQL Documentation on max_binlog_size:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

That fact that your binary logs are bigger than max_binlog_size just indicates that you are writing too many changes per single transaction.
expire_logs_days
With regards to expire_logs_days, the granularity is in days. There is nothing you can do with that.
If you wish to squeeze the number of binlogs down, you may have do one of two things:
SUGGESTION #1 : Scan in the binlog index file, find the last 10 binlogs, get the first binlog of those 10, and run something like:
PURGE BINARY LOGS TO 'whatever-binlog.002316';

SUGGESTION #2 : Write a cronjob that will keep the last 18 hours of binary logs:
PURGE BINARY LOGS BEFORE (NOW() - INTERVAL 18 HOUR);

GIVE IT A TRY !!!


What would cause Mysql database entries to be truncated to 253 characters? Randomly seeing database entries truncated to 253 characters on a php site and can't figure out why or if it could be malicious or a corruption issue.  Any thoughts would be appreciated.

If not configured (by adding strict_trans_tables to your sql_mode settings) to throw errors when presented with invalid data, such as a character value that is longer than the definition of a VARCHAR column allows, MySQL truncates it to fit, and only throws a warning.

My guess is that you've got a VARCHAR(255) column and at some point in the process, there's an inconsistency in character set configuration, and you have some UTF-8 characters that at some point in your system are being treated as latin1, causing the system to misinterpret the number of characters and truncate the value.

Wait, what?  How is the number of "characters" different than the number of "bytes?"

In UTF-8 encoding, character values from 0x7F (decimal 127) downward are still encoded as their 1-byte ASCII equivalent.  Larger values are encoded as 2 or 3 bytes.  Common symbols like © (ASCII 169 0xA9) and ® (ASCII 174 0xAE) are encoded as two bytes, 0xC2 0xA9 and 0xC2 0xAE respectively.

When fully configured for UTF-8, MySQL understands this; using "®" as an example:

mysql> SELECT LENGTH('®'), CHAR_LENGTH('®'), HEX('®');
+--------------+-------------------+-----------+
| LENGTH('®')  | CHAR_LENGTH('®')  | HEX('®')  |
+--------------+-------------------+-----------+
|            2 |                 1 | C2AE      |
+--------------+-------------------+-----------+

The LENGTH() function returns the number of bytes, while CHAR_LENGTH() returns the number of characters.

A VARCHAR column restricts not the number of storable bytes but the number of characters you can store in it, regardless of the byte-width of each character... so a VARCHAR(2) column could actually end up containing 6 bytes of data (plus requiring 1 byte of storage overhead) if the two characters happened to be both 3-byte-wide characters.

However... 0xC2 and 0xAE are both codes for valid characters in the default latin1 character set, so if you were configured with latin1 but sent those characters in their 2-byte unicode encoding, the server would interpret that single unicode character as if it were two characters -- both valid -- thereby reducing the number of printable characters that could be stored in the VARCHAR(255) column by one character for each 2-byte encoding, or reducing it by two characters for each 3-byte encoding.

In the example above, all of my character_set_* values (except character_set_filesystem, which is set to BINARY) were set as utf8, and the CHAR_LENGTH() of a 2-byte character was correctly reported as being "1."

Now, I'll change these settings, to illustrate:

mysql> set character_set_client = latin1, character_set_results = latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select CHAR_LENGTH('®');
+-------------------+
| CHAR_LENGTH('®')  |
+-------------------+
|                 2 |
+-------------------+

Note that I didn't actually change anything about my actual client's behavor -- each time the "®" symbol appears, the character was UTF-8 encoded and there were two bytes being sent and received over the wire.  All that changed is that I only told MySQL that what I was sending and what I expected to receive should be interpreted as being sent to the server (character_set_client) and returned to me (character_set_results) as latin1.

http://dev.mysql.com/doc/refman/5.1/en/charset.html


Where is my old partition in mysql show create table? I am keep adding daily base partition for a table as e.g.

ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) (
PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')),
PARTITION p21 VALUES LESS THAN (to_days('2012-12-26')),
PARTITION p22 VALUES LESS THAN (to_days('2012-12-27')),
PARTITION p23 VALUES LESS THAN (to_days('2012-12-28')),
PARTITION p24 VALUES LESS THAN (to_days('2012-12-29'))
)

When I run this show create table tblData is only shows me the partition p20 to p24. I am wondering where is all my previous p0-p19? Is it get dissolve when each time I run the alter table to add partitions?

You don't have plessthanmax part? When you set up a partition (that "moves" daily) the best practice is to set up a plessthanmax part to avoid errors. Each day you create a new partition and reorganise the plessthanmax like this :

For December 29

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p24 VALUES LESS THAN (to_days('2012-12-29')), partition plessthanmax values less than maxvalue)

For December 30

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p25 VALUES LESS THAN (to_days('2012-12-30')), partition plessthanmax values less than maxvalue)

Max.


Postgres: error message "does not exist" when dropping existing trigger I have a trigger associated to a table. When i drop it via

DROP TRIGGER IF EXISTS myTrigger on dummytable;

postgres tells me

NOTICE:  trigger "mytrigger" for table "dummytable" does not exist, skipping
DROP TRIGGER

When i use a dummy table for tests this works. I've tried:

changing CaSe,
making the trigger name longer (the real trigger is 17 chars long)

When testing it always drops without issue.

This is Postgres 8.2 on Linux. What does work is adding quotation marks:

DROP TRIGGER IF EXISTS "myTrigger" on dummytable;

Why this is so is beyond me, there's nothing in the docs that mentions the name must be in quotation marks.

Am i missing something obvious?

The behaviour you've described is correct and documented : http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

If the trigger have been created with double quote and special char, it must be used with the same syntax.


Managing and speeding up queries on PostgreSQL table with over 3 trillion rows I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns.

At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant amount of time. For example running the below command takes well over 15 mins:

SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';

The table is mostly used for reads. The only time the table is written to is during weekly updates which insert about 15 million rows.

What are the best ways to manage tables so large? Would you recommend splitting it by year?

The table size is 542 GB and the external size is 109 GB.

EXPLAIN (BUFFERS, ANALYZE) output:

"Seq Scan on table  (cost=0.00..116820941.44 rows=758 width=92) (actual time=0.011..1100643.844 rows=667 loops=1)"
"  Filter: (("COLUMN_A" = 'Value1'::text) AND ("COLUMN_B" = 'Value2'::text))"
"  Rows Removed by Filter: 4121893840"
"  Buffers: shared hit=2 read=56640470 dirtied=476248 written=476216"
"Total runtime: 1100643.967 ms"

The table was created using the following code:

CREATE TABLE tbl (
  DATE     timestamp with time zone,
  COLUMN_A text,
  COLUMN_B text,
  VALUE_1  double precision,
  VALUE_2  double precision,
  VALUE_3  double precision,
  VALUE_4  double precision,
  VALUE_5  double precision,
  VALUE_6  double precision,
  VALUE_7  double precision,
);

CREATE INDEX ix_table_name_date ON table_name (DATE);

Your existing index on DATE is obviously useless for the query. The first obvious step for your query:

SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';

is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like:

CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b);

Details:

Is a composite index also good for queries on the first field?

Next, I would consider partial indexes if you can safely exclude large parts of the table from the queries. Or partition your table in something like 100 partitions. And use constraint exclusion.

"Splitting it by year", like you contemplated, would be just as useless (harmful, even) as your index on DATE for the given query. Partitions need to be based on columns in the predicates of the query (column_a and column_b) to be helpful. Predicates on just one column would be much easier in this respect. If you have important queries filtering on various columns, partitioning is probably not the way to go. (Partial indexes still might.)

If partitioning is no good, a less invasive measure would be to CLUSTER data based on the new index (you can't use a partial index for this). Or simply create a new table from the sorted output of a query. This is particularly interesting since your table is mostly read-only. It should pay to do this at least once, but that one time will be very expensive: the whole table has to be re-written one or the other way. You need enough free space, as much RAM as you can get and an exclusive lock on the table. Or use pg_repack, to avoid the exclusive lock:

Configuring PostgreSQL for read performance
Best way to populate a new column in a large table?

Be sure to run the latest version of Postgres. The upcoming Postgres 9.5 might be particularly interesting for you, since it introduces BRIN indexes (block range indexes), which can reduce the index size dramatically for very big tables. Might be exactly what you are looking for.


Overloaded Database - Batch Updates vs Atomic/Single Updates We have on overloaded database which is now receiving failed transactions consequently. The proposed solution is to create a 2nd database that taskes all the (ecommerce) transactions initially and processes & persists them.  Then, eventually, the current/old database will be updated. That's the simple overview.

The question I have is - when I do the batch update from newDBServer -> OldDBServer every x minutes, won't it slow operations down on the old server during that update (assuming this isn't done overnight, etc...)?

The server is under a heavy load, so that's why I'm not certain it will really help minus the time in between updates. Is there a better way to do this?

I agree with @gbn, can you share more details about what constitutes "load".

First take a look at your server level wait types to get a feel for where your resource crunch points are.

Also identify your poorest performing queries and those that are most frequently executed. These are where you should target your performance tuning efforts.

To get you started quickly, grab a copy of Glenn Berry's SQL Server Diagnostic Queries which contain a number of useful scripts that you can use.


CASE statement and SARGability - specific use case edit: SQL Server - I'm hoping this is a generic enough question I need not specify version, but most of the instances I'm working with are 2012 or later.

I am not good enough to mock up data and actually test this, so I'm hoping someone could look at it and answer with simple experience.

Imagine you have a state table with American state abbreviations in a column (and it's indexed, like a good lookup column). When writing ad-hoc queries, users will often hit up this column to filter on, but using criteria that represents information that is not implicit in the database.

For example, if they want to get "big states" they may include a filter in their ad-hoc query that shows something like

...
where
  StateAbbreviation in ('AK', 'TX')

aka the dreaded "Business Rules"

So this query is fine, it performs well, and makes use of the index. But, man, what a bear to write every time we need to query "the big states." I'm tempted to create a view with this filter in it's definition to make it easier on them.

The problem here is that those business rules are specific to some number of ad-hoc queries supporting a line of business, but don't really have universal uses. So creating a view that filters out data in this way will have little utility.

So instead of writing a view where that criteria is in the filter, I want to write a view where the criteria result is calculated, such as

select
  StateAbbreviation
  , IsBig = case when StateAbbreviation in ('AK' , 'TX') then 1 else 0 end
from tblStates

Now, when they want to write a query for big states, they just include

where IsBig = 1

in the query.

So, my question is simple - if the view were called with that criteria, can the index on StateAbbreviation be used?

I know there are all kinds of things I could do inside a CASE statement that may change the answer, so for the purposes of answering this very specific question, assume the case statement will only ever look like that. It will not make use of multiple fields, it will not aggregate - just a simple in or out calculation of literals to expose complex filter criteria to report writers in a simpler way.

From experience I would expect the index not to be used in the query against the view. However, to determine if something is SARGable you just need an example query against a data set for which the index is beneficial. So, best to test it.

First I'll create some test data:

CREATE TABLE dbo.tblStates (
StateAbbreviation VARCHAR(2) NOT NULL,
FLUFF VARCHAR(10) NOT NULL
);

-- insert all possible abbreviations to future-proof table
INSERT INTO dbo.tblStates WITH (TABLOCK)
SELECT CHAR(t1.number) + CHAR(t2.number), REPLICATE('Z', 10)
FROM
(
    SELECT number
    from master..spt_values
    WHERE number BETWEEN 65 and 90
) t1
CROSS JOIN
(
    SELECT number
    from master..spt_values
    WHERE number BETWEEN 65 and 90
) t2;

CREATE INDEX IX_TBL_STATES ON dbo.tblStates (StateAbbreviation);

When running this query we can see that the index is used to do a seek:

SELECT StateAbbreviation
FROM dbo.tblStates
where
StateAbbreviation in ('AK', 'TX');

However, if we create a view:

CREATE VIEW STATE_VIEW
AS
select
  StateAbbreviation
  , IsBig = case when StateAbbreviation in ('AK' , 'TX') then 1 else 0 end
from tblStates;

The equivalent query no longer uses the index to seek:

SELECT StateAbbreviation
FROM STATE_VIEW
where IsBig = 1;

db fiddle link is here.


Find rows in a single table where values in a certain column are near-duplicates I have a Postgres 11 table on RDS containing a column email; some of the values in this column [and only that column] are clearly de facto duplicates but differ in case, i.e., different capitalization, such as:

foo@****.com
Foo@****.com

To be clear, none of the present rows are true duplicates, nor share precisely the same values in that column. My objective is to identify these records [and, once found, eliminate/merge the de-facto duplicates].

My initial inclination was to use a self-join, e.g.:

SELECT c.email
FROM schema.table c
INNER JOIN schema.table d ON lower(c.email) = lower(d.email)
ORDER BY c.email;

However, this returns all the email records rather than only those that are de-facto-duplications.

Using a subquery such as the following produces a similar [i.e., too-inclusive] result:

SELECT c.email, alias.email
FROM schema.table c
  JOIN (SELECT email FROM schema.table) alias ON lower(c.email) = lower(alias.email);

Since I’m not looking for an aggregate, but rather a case-insensitive comparison, it seems to me that a window function is not the correct approach.

I think that this should be a straightforward query, but I’m having a difficult time seeing it clearly and am sure there is an error in the way I’m conceiving of the problem; it’s pretty frustrating.

In addition to searching here and on SO, I consulted Molinaro’s SQL Cookbook, but to no avail.

What is the correct way to structure the query so that it returns only those records whose email values are the same, disregarding case?

edit note : my initial question formulation expressed a misguided inclination to use ILIKE for case-insensitive matching, but the use of lower() as suggested in the below answers is far more sensible

You need to exclude the rows from each other:

SELECT c.email
  FROM schema.table c,
       schema.table d
 WHERE lower(c.email) = lower(d.email)
   AND c.email <> d.email;

One question you will need to answer (and I will update my answer if relevant) -- are you using ILIKE because you want partial matches too?  Like, are bert@foo.com and RObert@foo.com supposed to be considered duplicates?  (I would assume not, but when people use ILIKE, it's usually to patch parts of strings, as in the bert/robert example)


Optimize a MySQL Database using MySQL Workbench I actually have a few issues.

First, I found this information about optimizing: https://stackoverflow.com/questions/5474662/mysql-optimize-all-tables

The problem is, I don't seem to have a mysqlcheck program anywhere.  I'm using MySQL Workbench.  It's possible I have used other MySQL versions in the past.  When I look for mysql I find in the Program Files folder there is a MySQL folder with a MySQL Server 5.5 folder.  This one has some .ini files and the bin, dat, etc directories, but no executables.  Then in Program Files (x86) there is another MySQL folder.  In that there are several folders including: MySQL Documentation 5.5.30, MySQL Installer, MySQL Notifier, MySQL Workbench CE 5.2.47, and Samples and Examples 5.5.30.  The only one that has mysql.exe is MySQL Workbench CE 5.2.47.  But it does not have mysqlcheck.exe.

I poked around in Workbench and could not find any maintenance or optimization functions.

My database is 17 GB and I removed some large text fields from large tables, and I'm hoping to recover space because my OS is yelling at me to make more space.  Does Workbench have the capability to optimize, and if so how do I do it?  And if not, what can I install to be able to do it?

The maintenance feature is a bit hidden in MySQL Workbench. You can reach it by opening your connection as usual and right clicking on your schema to open the schema inspector:

You will get a new tab in the editor area with many pages for inspection/maintenance. Click on the Maintenance button at the bottom to open this page:

From there it should be clear what to do.


Find minimum record based on two fields, self joins vs. derived tables Is there a better way? I need attributes of the oldest SequenceID of oldest DateID for each ThingID. PK is DateID & SequenceID:

SELECT
          ThingID     = F1.ThingID
        , Attribute1  = F1.Attribute1
        , Attribute2  = F1.Attribute2
        , Attribute3  = F1.Attribute3
FROM dbo.Fact F1
INNER JOIN (
              SELECT  DateID      = F2.DateID
                    , SequenceID  = min(SequenceID)
                    , ThingID     = F2.ThingID
              FROM dbo.Fact F2
              INNER JOIN  (
                              SELECT    DateID   = min(DateID)
                                      , ThingID  = ThingID
                              FROM      dbo.Fact
                              GROUP BY  ThingID

                          ) AS F3 ON  F2.ThingID = F3.ThingID
                                  AND F2.DateID  = F3.DateID

              GROUP BY F2.DateID, F2.ThingID

            ) AS F2  ON   F1.ThingID    = F2.ThingID
                    AND   F1.SequenceID = F2.SequenceID
                    AND   F1.DateID     = F2.DateID

FACT TABLE

DateID       datetime  PK
SequenceID   INT       PK
ThingID      INT
AttributeID1 INT
AttributeID2 INT
AttributeID3 INT

edit: SQL 2012

You can use window functions for this:

WITH ct AS
  ( SELECT
        ThingID,
        Attribute1,
        Attribute2,
        Attribute3,
        Rn = ROW_NUMBER() OVER (PARTITION BY ThingID
                                ORDER BY DateID, SequenceID)
    FROM dbo.Fact
  )
SELECT
    ThingID,
    Attribute1,
    Attribute2,
    Attribute3
FROM ct
WHERE Rn = 1 ;

and an index on (ThingID, DateID, SequenceID).


How to get the timezone in SQL server Was there any query to retrieve current timezone setting for the SQL server with respect to some offset like UTC..

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone


Does RMAN in Oracle only copy the archive logs? What happens to the active redo log in case of failure? If RMAN copy copies the archived redo logs when they are full, what happens to the current redo log in the event of a failure (hardware failure, etc.)?  Isn't there always an active redo log that is not an archive_log?

The first thing RMAN does it automatically perform an ALTER SYSTEM ARCHIVE LOG CURRENT to switch logfiles so that the active log at the point the backup was started will be included.

It doesn't do this in all backup situations, but this answers your question.


Update performance: clustered versus covering index I have a simple, 3-column table with about 50 million rows in it.  This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table.  The table looks like this:

Controller: Int
ExecutionTime: DateTime
Result: Int

To maximize the efficiency of my queries, I need two indexes.  (Result Includes Execution Time) and (Controller, ExecutionTime).  These two indexes fully cover my queries - all information is served directly from the indices, no table lookups required.

I chose nonclustered indices because I was worred about the performance hit using a clustered index with so many updates.  But it occurs to me that since I am fully covering the queries, this might not be a valid concern - perhaps my covering, nonclustered indices require the same amount of maintenance as a clustered index would.

So my question:  In a table with a lot of inserts/updates, will a covering, nonclustered index usually have a lower UPDATE performance hit than a clustered index?

Thanks for your time and help!

Under the covers clustered and nonclustered indexes are the same. The clustered index just has the additional property that is is guaranteed to INCLUDE all columns. Therefore the data does not need to be maintained somewhere else. So, a clustered index and a nonclustered index that INCLUDEs all columns are virtually the same from an update cost perspective.

However, every index needs to be maintained if it contains a column that was changed during an updated. That means, the more indexes you have, the more expensive updates get.

So in your situation, I would try to keep the number of indexes to a minimum. That will help update performance more than worrying about if a particular index is better clustered or covering.

That all being said, your updates still need to find the row(s) to update as quickly as possible. Because you have two orders of magnitude more updates then select, updates should be looked at first when designing the indexing strategy. After they are taken care of, look at providing the minimal number of appropriate indexes for the read queries.


Error with SQL stored procedure output type I have a table 2 tables, namely DAH_Errors and Intraday_Errors, and I want to calculate some statistical performance indicators, fore example the NMAE over a specified range of rows. The range is specified by 2 variables @fromdate and @todate, as shown below:

DECLARE @fromdates VARCHAR(20)='2017-12-11 00:15:00'
DECLARE @todates VARCHAR(20)='2017-12-12 00:00:00'
DECLARE @fromdate SMALLDATETIME=CAST(@fromdates AS SMALLDATETIME)
DECLARE @todate SMALLDATETIME=CAST(@todates AS SMALLDATETIME)

DECLARE @nmae1 DECIMAL(20,5)
DECLARE @nmae DECIMAL(20,5)
DECLARE @nrmse1 DECIMAL(20,5)
DECLARE @nrmse DECIMAL(20,5)

In order to calculate the NMAE, I have created a the procedure get_nmaev2, which is the following:

CREATE PROCEDURE [dbo].[get_NMAEv2]
        @forecasttype VARCHAR(MAX),
        @fromdate2 SMALLDATETIME,
        @todate2 SMALLDATETIME,
        @nmae DECIMAL(20,5) OUTPUT
AS
SET NOCOUNT ON;

DECLARE @aux VARCHAR(MAX)='Hello'
IF @forecasttype='DAH'
    SET @aux='SELECT '+@nmae+'=AVG(ABS(['+@forecasttype+'_Errors].[Forecast]-['+@forecasttype+'_Errors].[Metering])/CAST(['+@forecasttype+'_Errors].[Capacity] AS DECIMAL(20,5)))
    FROM dbo.'+@forecasttype+'_Errors
    WHERE ['+@forecasttype+'_Errors].[DateTime(UTC)]>='+@fromdate2+' AND ['+@forecasttype+'_Errors].[DateTime(UTC)]<='+@todate2+' AND ['+@forecasttype+'_Errors].[NAE] IS NOT NULL;'
    EXEC(@aux)

IF @forecasttype='Intraday'
    SET @aux='SELECT '+@nmae+'=AVG(ABS(['+ @forecasttype+'_Errors].[Forecast]-['+@forecasttype+'_Errors].[Onlline])/CAST(['+@forecasttype+'_Errors].[Capacity] AS DECIMAL(20,5)))
    FROM dbo.'+@forecasttype+'_Errors
    WHERE ['+@forecasttype+'_Errors].[DateTimeUTC]>='+@fromdate2+' AND ['+@forecasttype+'_Errors].[DateTimeUTC]<='+@todate2+' AND ['+@forecasttype+'_Errors].[NAE] IS NOT NULL;'
    EXEC(@aux)

The variables @fromdate2,@todate2 are used in the WHERE statement in order to specify the range over which I will calculate the NMAE.

I then execute the following, in order to get my result:

EXEC dbo.get_nmaev2
     @forecasttype='DAH',
     @fromdate2 =  @fromdate,
     @todate2 = @todate,
     @nmae=@nmae1 OUTPUT

What I want to do, is to store value of the select statement in the variable @nmae, and output that to the variable @nmae1, shown in the first script where I call execute the procedure. However, I get the following error message:

Msg 8114, Level 16, State 5, Procedure get_NMAEv2, Line 16
Error converting data type varchar to numeric.

I can not find what causes the error, so I would really appreciate some help here. Does this happen because I am trying to include the variable @nmae in the dynamic SQL statement;

I know what the error itself actually means, but I simply cant find what causes it :)

This row causes your error:

SET @aux='SELECT '+@nmae

Since @nmae is DECIMAL(20,5) there is no way to make a sum without converting, and thanks to type precedence, it's string SELECT that should be converted to numeric, and this is impossible.

Here is quick repro:

declare @nmae DECIMAL(20,5);

DECLARE @aux VARCHAR(MAX)='Hello'

SET @aux='SELECT '+@nmae;

exec(@aux);

To see what is converted to what you can use this query:

select '2' + 2

this will not produce '22', but 4, as it's tring '2' to be converted to numeric, not viceversa.

UPDATE

You can use synonym to get rid of all your dynamic code like this:

declare @forecasttype VARCHAR(MAX) = 'DAH'
declare @sql varchar(8000) = 'create synonym dbo.MySyn for ['+@forecasttype+'_Errors]';
exec(@sql); -- this will create MySyn that points to your table

Now you can use the created synonym instead of your table:

SELECT @nmae=AVG(ABS([MySyn].[Forecast]-[MySyn].[Metering])/CAST([MySyn].[Capacity] AS DECIMAL(20,5))) from dbo.[MySyn]

And it's even better create an alias to not rewrite table name every time in the select:

SELECT @nmae=AVG(ABS(s.[Forecast]-s.[Metering])/CAST(s.[Capacity] AS DECIMAL(20,5))) from [MySyn] s

At the end of sp just drop the synonym.

You should be sure that this sp is not executed at the same time by more than 1 user, if it's so you need other solution