Does the principle of having an "ever increasing index" also apply for NVARCHAR and VARCHAR? This might sound like an amateur question, but I just need to be 100% sure and I haven't found anything on it online.

It is considered good practice having an "ever increasing index". Now, does that rule also apply to NVARCHAR and VARCHAR?

Let's say I insert "abc", then "def" and then "ghi". Is this considered "increasing"?

Note: I am well aware that it is bad convention to use an NVARCHAR and VARCHAR as index.

If I understand your question:
It is a good practice to have an ever increased index - that prevents page splits - read about this issue if you are not familiar with it. It is correct also for char values. To make it simple - sql server will fill page after page without having to insert rows in the middle. Think about it as filling phone book - it is much easier to fill it in abc order than randomly insert names.
So the order "abc", then "def" and then "ghi" is ever increasing.
Don't know why do you think that using char columns as indexes is a bad practice - if you need them there you should create them. Yes, creating logic that will enforce ever increasing char insertion is difficult and sometimes impossible, but it still shouldn't prevent you from creating indexes on those columns.
With all that said if you need to enforce ever increasing clustered index you should probably use numbers(identity or sequence)


Cannot create an additional database in Oracle 18c XE Creating a new database in Oracle 18c XE throws this error, does this mean that I can only use the database created by the installation?

ORA-00442: Oracle Database Express Edition (XE) single instance
violation error
ORA-00600: internal error code, arguments:
[OSDEP_INTERNAL], [], [], [], [], [], [], [], [], [], [], []
ORA-27302: failure occurred at: sxesingleins ORA-27303: additional
information: environment variable ORACLE_SID should be XE only

Licensing Restrictions

  Oracle Database XE restricts itself to only one installation per
  logical environment. The logical environment can either be a virtual
  host such as a VM or container, or a physical host. If more than one
  Oracle Database XE installation is attempted to be started in such a
  logical environment, an "ORA-00442: Oracle Database Express Edition
  (XE) single instance violation error" is raised and the database will
  not start.


Grouping results and combining duplicates as array I'm trying to write a query that gets all the records grouped by code and the rows that are duplicated are aggregated into an array.
+----------+---------+
| code     | name    |
+----------+---------+
| a        | Mike    |
| b        | Jones   |
| b        | Who     |
+----------+---------+

expected result:
+----------+----------------+
| code     | name           |
+----------+----------------+
| a        | Mike           |
| b        | [Jones, Who]   |
+----------+----------------+

I believe SQLServer 2017 introduced STRING_AGG:
select code, string_agg(name, '') as data
from T
group by code


How to run mongod from the commandline? How do you manually run mongod from the commandline, so it outputs all logging directly in your terminal?

I'm trying to diagnose a crashing Mongo 3.4.11 database on Ubuntu 16. When I try and start it via sudo service mongodb start it says:

Job for mongodb.service failed because the control process exited with error code. See "systemctl status mongodb.service" and "journalctl -xe" for details.

However, systemctl status mongodb.service gives me an equally useless message:

● mongodb.service - LSB: An object/document-oriented database
   Loaded: loaded (/etc/init.d/mongodb; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2018-02-07 20:32:17 UTC; 26s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2392 ExecStop=/etc/init.d/mongodb stop (code=exited, status=0/SUCCESS)
  Process: 2913 ExecStart=/etc/init.d/mongodb start (code=exited, status=1/FAILURE)

Feb 07 20:32:16 proddb1 systemd[1]: Starting LSB: An object/document-oriented database...
Feb 07 20:32:16 proddb1 mongodb[2913]:  * Starting database mongodb
Feb 07 20:32:17 proddb1 mongodb[2913]:    ...fail!
Feb 07 20:32:17 proddb1 systemd[1]: mongodb.service: Control process exited, code=exited status=1
Feb 07 20:32:17 proddb1 systemd[1]: Failed to start LSB: An object/document-oriented database.
Feb 07 20:32:17 proddb1 systemd[1]: mongodb.service: Unit entered failed state.
Feb 07 20:32:17 proddb1 systemd[1]: mongodb.service: Failed with result 'exit-code'.

Nothing's written to the log at /var/log/mongodb/mongodb.log.

I tried manually launching the daemon via:

sudo -u mongodb /usr/bin/mongod --config /etc/mongod.conf

but after a few seconds, this simply returned with:

Aborted (core dumped)

Because nothing is written to log file, the first (and only) problem is (probably) ownership of log file. Usermongodb don't have access to that file. sudo chown -R mongodb: /var/log/mongodb should fix that.

What comes to the original question of how to run mongod at the command line and see what is happening, you have two choices. First is comment out (from config file) line what orders mongod to fork itself to background. Second possibility is check from config file,  what parameters you need (like dbPath) and then replace that --config with those command line parameters. You can see list of command line parameters with mongod --help command


Reduce the number of queries I have a website where I want to create option for user to remove items from their bag. I am doing this task using many different queries.

Is there way to reduce the number of queries?

I have the following 3 tables:

BAG_TB
BAG_ITEM_TB
ACCOUNT_TB

Here is how I am doing it:

1- Get the bag_ID of user who is using the site. This is by account_id.

select Bag_ID
FROM   [BAG_TB] b
WHERE  Account_ID = @Account_ID;

2- Based on above step, it checks how many records are on BAG_ITEM_TB. This will tell me how many items are in current user bag.

Select Bag_ID
FROM   [BAG_ITEM_TB]
WHERE  [Bag_ID] = @Bag_ID;

3a- If there is 1 record returned from step #2, this means I have to delete bag. So do a delete on BAG_ITEM, BAG_TB, and ACCOUNT_TB.

3b- If there is more than 1 record returned from step #2, then only delete from BAG_ITEM_TB because there is more than 1 item in bag... so do not delete from BAG_TB and ACCOUNT_TB

Below one example of my delete queries:

delete FROM [BAG_ITEM_TB]
WHERE  [Bag_ID] = @Bag_ID
AND    [Product_ID] = @Prduct_ID;

delete FROM [BAG_TB]
WHERE  [Bag_ID] = [Bag_ID];

delete from [IS_ACCOUNT_TB]
WHERE  Account_ID = @Account_ID;

Define referential integrity in your database, so that a bag cannot be removed so long an item exists, idem for the accounts and bags.  Then don't do the selects but provide exception handling on the deletes.  Your database will do the selects itself, to enforce the integrity.


Can't create linked server SQL Server 2005 I'm new to doing linked server and I have this error.

"The linked server has been created but failed a connection"

On the same virtual machine I have two SQL Server instances

one 2000 (VMDEVCIAQ\NMP)
one 2005 (VMDEVCIAQ\NMPUTIL).

My setup :

The 2005 try to modify tables in the 2000.
I enabled remote connection on both instance.
I tested manually connecting to the instance of 2000 and it worked.

What I input in the form

Linked server: NMP
Provider : Microsoft OLE DB Provider for SQL Server
ProductName: SQLServer
Data source : VMDEVCIAQ\NMP

In the security tab I set "Be made using this security context" with the tested login and password.

So any idea why this would fail ? Any pointer or doc would be appreciated.

Cheers

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

"


Check what event called the trigger on PostgreSQL? I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE.

Oracle triggers can check this in a simple 'IF' statement:

IF INSERTING, IF UPDATING or even IF DELETING

Is there a similar statement on PostgreSQL?

Usually I just create a function for INSERT, an other one for UPDATE and one more for DELETE, but it's a waste of code if there's some way to create just one function.

Can someone help?

Trigger functions can check the value of the TG_OP variable to determine the event which caused the trigger to fire.  There are a number of other special variables as well listed in the documentation.


Can DBA create a public synonym for a table that has the same name as an existing table Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr.

If a user performs a query like the following statement:

select * from hr;

What will be returned by the query?

Whose table values will be shown?

The records of the public synonym
The records of the table

The user table will take precedence over the public synonym.
The data from the user table would be shown.

Oracle's object name resolution is explained in the article Managing Object Name Resolution (Oracle Database Administrators Guide)

The following SQL code will explain the use case:

SQL> conn user1
Enter password:
Connected.

SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.

SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.

SQL> select count(0) from test; --first table with 0 rows
  COUNT(0)
----------
         0

SQL> select count(0) from conflicting_name; --second table with 1 row
  COUNT(0)
----------
         1

At this stage you have the test table containing zero (0) rows and the conflicting_name table containing one (1) row.

Let's continue by creating a public synonym and querying conflicting_name:

SQL>  create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.

SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
  COUNT(0)
----------
         1

The result shows you that the query is run against the table conflicting_name which contains one row and not against the public synonym conflicting_name which references the test table containing zero rows.

What happens with a different user? Let's try:

SQL> conn user2
Enter password:
Connected.

SQL> -- Connected to different user

SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
  COUNT(0)
----------
         0

SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
  COUNT(0)
----------
         1

Because of the public synonym, user2 will be querying the test table. Only if user2 directly queries the user1.conflicting_name table will the single row be returned.


Function accepts only certain passwords in postgreSQL I have created a function in a postgreSQL(v10.10) database to create new users or update existing ones.
As parameters you pass the username, password, role memberships, first name and last name. In my case the function is called by an external program with connection to this database. Basically it works, but strangely only passwords are taken over which contain only lower case letters and do not start with a number. As soon as the password contains uppercase letters, special characters or the password begins with a number, the user is created, but the login data is supposed to be wrong when trying to connect afterwards.
I have already written the password in that function into a logging table. There the password is also identical with the given password parameter(e.g. uppercase, numbers, etc.), but a login still does not work.
Thats my function I am using:
DECLARE
v_role TEXT;
v_timestamp TIMESTAMP;
v_status TEXT;

BEGIN
    v_timestamp := NOW();
    --check ob Nutzer bereits angelegt, dann create oder alter
    IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = v_username)
      THEN --Neuanlage
        --lege Nutzer an
        EXECUTE FORMAT('CREATE USER %I WITH PASSWORD ''%I''', v_username, v_password);
        EXECUTE FORMAT(E'COMMENT ON ROLE %I IS ''%s %s \nAngelegt am:\n%s''', v_username, v_vorname, v_nachname, to_char(v_timestamp, 'DD.MM.YYYY HH24:MI:SS'));
        v_status := 'neuanlage';

      ELSE --Änderung
      --ändere bestehenden Nutzer
        EXECUTE FORMAT('ALTER USER %I WITH PASSWORD ''%I''', v_username, v_password);
        EXECUTE FORMAT(E'COMMENT ON ROLE %I IS ''%s %s \nUpdate am:\n%s''', v_username, v_vorname, v_nachname, to_char(v_timestamp, 'DD.MM.YYYY HH24:MI:SS'));
        --entferne Mitgliedschaft aus bestehenden Gruppen
        FOR v_role IN
        SELECT rolname FROM pg_roles WHERE pg_has_role( v_username, oid, 'member')
        LOOP
            IF v_role != v_username THEN
                EXECUTE(FORMAT('revoke %I from %I', v_role, v_username));
            END IF;
        END LOOP;
        v_status := 'update';
    END IF;
    --füge zu Gruppen hinzu
    FOREACH v_role IN ARRAY v_roles LOOP
        EXECUTE FORMAT('GRANT %I TO %I', v_role, v_username);
    END loop;

    --Protokollierung
    INSERT INTO verwaltung.roles_protokoll (roles, status, timestamp, username, password) VALUES (v_roles, v_status, v_timestamp, v_username, v_password);

    RETURN 1;
    -- Simple Exception
EXCEPTION
    WHEN others THEN
        RETURN 0;
END;

Do you have any ideas what's going wrong in there?

That's because you used the wrong format specifier. %I is for identifiers, but the password is a string literal, so you should use %L (and not include the single quotes).
Compare these:
SELECT format('PASSWORD %L', 'gut'), format('PASSWORD ''%I''', 'gut');
     format     |     format
----------------+----------------
 PASSWORD 'gut' | PASSWORD 'gut'
(1 row)

test=> SELECT format('PASSWORD %L', 'Böse'), format('PASSWORD ''%I''', 'Böse');
     format      |      format
-----------------+-------------------
 PASSWORD 'Böse' | PASSWORD '"Böse"'
(1 row)

So there were unexpected double quotes included in the password.
But there are bigger problems:
SELECT format('ALTER USER me PASSWORD ''%I''', 'inject'' SUPERUSER --');
                     format
-------------------------------------------------
 ALTER USER me PASSWORD '"inject' SUPERUSER --"'
(1 row)

Ha! I can abuse your function to become superuser, because you didn't use proper quoting.


Is it safe to run optimze on mutliple tables in parallel in mysql (percona) I have a slave on which no query is coming. Lot of data has been archived and deleted from almost all tables. Size of tables vary from 80Gb to few Mbs. And there are around 130 such tables.

Mysql (Percona) version is "Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1"

I am running optimize on them, one at a time, but since optimization is a time consuming process, would it be Okay if I run optimize on multiple tables (say 3 to 4) at a time? I have enough disk space for 3 to 4 tables to be copied in the same partition.

I am assuming optimize on one table simple locks that table alone and has no effect at all on the other tables, hence it would be perfectly safe. But any caveats and former experience is dealing with this is welcomed.

As KJavalik pointed out in the Comments, it it safe, but Disk IO will increase and may choke other operations. So in case the Server is not serving any traffic/queries, you may try, else be careful.

Also keep an eye on the free disk space as optimize command will copy the remaining data of the table being optimized to a new table, and if the slave is in replication, new data will also take up some space.

So in case you are optimizing 2 tables of 50Gb each, for example, and prior experience suggests that post optimization each table will be around 35 Gb, then its good to have at least 90Gb of free space (35 Gb + 35 Gb + 20 Gb of extra free space for other tables to grow in case the database is in replication)


How to use mysql sandbox to build s - m - m -s topology? I want to build this topology

 M1  <->   M2
 |         |
 S1        S2

I've checked sandbox --help and it seems that it can only build:

M  ->  S

make_replication_sandbox mysql-5.6.tar.gz

M  <->  M

make_replication_sandbox  --master_master mysql-5.6.tar.gz

M  <->  M  <->  M  <->  M  <-> (circular)

make_replication_sandbox --circular=4 mysql-5.6.tar.gz

But how can I build

 M1  <->   M2
 |         |
 S1        S2

Well afaik you can't if you rely on mysqlsandbox to orchestrate it for you. But you can create a master-master for M1 <-> M2 and then create two standalone instance and configure each one to slave off of one master. It's pretty easy since you have all replication user created "rsandbox".


Calling stored Procedure from one db to give me a result in another db CREATE DEFINER=root@localhost
PROCEDURE AccountReqs( IN IsNewAccountStart VARCHAR (255)
                 , In AccountName varchar (255)
                 , IN AccountNumber varchar(255)
                 , In PaymentTerm varchar(255)
                 , IN Batch varchar (255)
                 , In AdditionalEmailAddresses varchar (500)
                 , In WorkEmail varchar(500)
                 , In PersonalEmail varchar (500) )`
BEGIN
    SELECT * FROM ACCOUNTS
    WHERE IsNewAccountStart <>'TRUE' AND IsNewAccountStart <>' '
    Union all
    SELECT * FROM ACCOUNTS WHERE AccountName=' '
    Union all
    SELECT * FROM ACCOUNTS WHERE AccountNumber=' '
    Union all
    SELECT * FROM ACCOUNTS
    WHERE PaymentTerm <> 'Net 30'
    AND PaymentTerm <> 'Net 45'
    AND PaymentTerm <> 'Net 60'
    AND PaymentTerm<> 'Due Upon Receipt'
    Union All
    SELECT * FROM ACCOUNTS WHERE Batch <> 'Batch1';

    SELECT * FROM ACCOUNTS
    WHERE AdditionalEmailAddresses <> ' '
    OR WorkEmail <> ' '
    OR PersonalEmail <> ' ';

END

SUGGESTION #1

You need to explicitly set the database before calling the Stored Procedure

USE targetDB
CALL Shared.AccountReqs(...);

SUGGESTION #2 (OPTIONAL)

Since you have all these UNION clauses against the same table, try combining all the WHERE clauses under a single SELECT. I see two SELECTs in your original question. Therefore, the Stored Procedure should look more like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AccountReqs`(
  IN IsNewAccountStart VARCHAR (255),
  In AccountName varchar (255),
  IN AccountNumber varchar(255),
  In PaymentTerm varchar(255),
  IN Batch varchar (255),
  In AdditionalEmailAddresses varchar (500),
  In WorkEmail varchar(500),
  In PersonalEmail varchar (500)
)
BEGIN
    SELECT * FROM ACCOUNTS
    WHERE (IsNewAccountStart <>'TRUE' AND IsNewAccountStart <>' ')
    OR    (AccountName=' ') OR (AccountNumber=' ')
    OR    (PaymentTerm NOT IN ('Net 30','Net 45','Net 60','Due Upon Receipt'))
    OR    Batch<> 'Batch1';

    SELECT * FROM ACCOUNTS
    where AdditionalEmailAddresses<>' '
    Or  WorkEmail<> ' '
    Or  PersonalEmail<> ' ';
END


how to associate columns between differnt tables So i'm designing a database for my application which is basically a qustionaire. I designed my db as best as i could and i think it kinda works.
The questionair isn't straightfoward. by this i mean there are multiple questionairs and the questions are not a simple (yes/no). some questions are multiple choice (lists) some are a yes/no (enum) and for some qusetions i have a other 'option' in which users can input string.
Here are some questions which i think illustrate the returned data types:
q1) do you like cats?

yes
no

q2) pick toppings on your burger?

onions
tomatoes
cheese
pickles

q3) what is your favourite number?

one
two
three
other    [ ' enter your favourite number ' ]

What i am trying to figure out
How can i associate questions with their possible "options"?

While a better solution might exist, a simple way would be to have a Questions table with QuestionId as the primary key, QuestionText column, and an QuestionType column so your consuming application can render the appropriate control (e.g. Q1 likely should be a radio button vs Q2 which would be a checkbox).
And have an Answers table with an AnswerId primary key column, the QuestionId it belongs to as a foreign key reference, and an AnswerText column so it can hold any data type of answer. It may be helpful to also have a column called AnswerDataType so you can dynamically cast the AnswerText to the underlying data type when needed.


Insert streamed RFID data I have RFID readers currently writing data to files on a hard drive, which then SSIS reads and inserts into the database. Instead, I'd prefer that SQL Server (2016+) read the data stream directly into a Memory Optimized Table to increase performance and handle scalability.

What is the best method to do this, handling multiple readers sending data all at once, and making sure no data gets lost due to  bottlenecks, deadlocks, etc.?

Each reader can handle approximately 60 reads per second, then transmit via wi-fi (or later via USB cradle).  So per minute, we're talking 60x60x512bits of data per reader.  For future, assume in large warehouses that there could be 10+ readers going at once.

There is simultaneous processing and reading of the data.  While the reader data is coming in, it needs to be checked for inconsistencies, redundancies, de-duped, updated (e.g. if a product location has moved), etc.  Then, queries / reports will be made against the DB - various user searches, inventory and location reports, analysis.

Also handling of bottlenecks due to simultaneous delivery of data by readers - i.e. say 5 readers were delivering their daily data at the same time through cradles, while the DB was doing various other tasks.

How confident are you that max concurrent connections will stay at 10 in the future?

If you're interested in raw speed - which is part of the domain of In-Memory OLTP - then transmitting via wifi will likely be a bottleneck. Even if  you're mainly interested in scalability of the write workload - wifi or USB cradle will have its limits - preventing the full (potential) benefit of using In-Memory OLTP.

While Tony Hinkle is correct that an IDENTITY is more "efficient" than a GUID, clustering on an IDENTITY is an anti-pattern for highly concurrent, write-intensive workloads. It's exactly this anti-pattern that In-Memory is meant to solve, but consideration of using In-Memory OLTP should start a dialog that takes into account all of the many gotchas, i.e. no db mirroring, no snapshots, can never remove the memory-optimized filegroup, etc.

Queries can produce different results when using any form of snapshot isolation, so you'd have to verify that the way your app works can handle that.

So while the volume of data to be inserted is not particularly high, if you throw enough concurrent connections at it, scalability will be an issue. This is easily demonstrated with ostress (as long as you don't output to files). As concurrency ramps up, say from 100 to 500 connections, INSERT performance simply crawls. This is due to having to latch the last page of the clustered index, which essentially serializes all writes to the table.

Given what you've described, In-Memory OLTP might be best used like a "shock absorber", as a landing pad for your data. Then you could validate it, before sending it to its final destination. If that final destination is another memory-optimized table, be advised that all writes to a memory-optimized table are serial, no matter what the source. So you would have to roll your own parallelism, and ensure that your processing does not step on its own toes. You'll essentially have a queue, and due to its lock-free architecture, In-Memory OLTP is poorly suited to queue processing. There must be some form of serialization to make sure your concurrent processes don't attempt to process the same set of rows.

All traditional/disk based tables can suffer from deadlocks, which can usually be reduced, and perhaps eliminated, but there is no guarantee of that. Deadlocks are not possible with memory-optimized tables, due the lock/latch free architecture (same for indexes on memory-optimized tables).

Queries will probably not be any faster with In-Memory OLTP, as it's not designed for that.

I have blogged extensively about In-Memory OLTP. If you are seriously considering deploying it, I strongly suggest you study what I've written.


Backup and restore procedure I am having only MYI files is it possible to restore my database with this files.?

If yes means tell me the procedure please

No, it's not possible You will need the MYD file. The MYI file only stores the indexes and does not contain any data.


Adding a Clustered index to a HEAP table on someone else's app We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index).  Over the years, these tables have grown badly fragmented (e.g. 99% fragmentation).  I need to defragment them.

Now, in SQL Server 2005, there's no way to do that directly.  I can either:

Create a Clustered index on a prexisting field, and keep it
Create that index, rebuild the table, and then drop it
Add my own field (eg autoincrement key)

Now, this is a large app, written by a vendor - a giant black box.  I'm not eager to mess around with their stuff.  I don't know a lot about how the tables are used.  What's the least impact way to do this?

And, as a follow up: There are many of these HEAP based tables, over a dozen databases used by the application.  Is there a way to automate choice #2 or #3? Or, how should I pick which tables to modify?

UPDATE:
To answer the questions posed by the (very helpful) responses:

Performance has been unacceptable.  The vendor told my client that they this is because the tables are extremely fragmented, and it's the client's responsibility to defragment them regularly
We have two instances of the application: a test one and a production one
I first defragmented all of the Clustered tables, which greatly improve performance
The vendor's support team has been very unhelpful.  They've told us it's our responsibility to defragment the tables.  When I've asked them how they recommend defragmenting HEAP based tables - should I add a Clustered Index? - they've only responded "That's a Microsoft question".

In short, the customer support team has made it clear: You must defrag the tables, how you do it is your business.

As for future versions: Yes, new versions are being developed, and they will eventually migrate to SQL Server 2012.  But they need performance solutions today.

Finally, as for the defragmenting taking too long: It doesn't matter.  They have giant tables with 99% fragmentation; the application isn't used at night; I can easily spend hours at a time defragging them.

I'm going to go on the assumption that you've identified a problem that needs to be fixed... though I'm not entirely convinced of that yet.

Similar to my answer here, there are two cases when it comes to vendor applications.

The application is currently under license and/or the vendor/owner doesn't want you meddling.

In this case, definitely do not change the schema in any way, including adding indexes of any kind.

Making schema changes may in fact void your license contract, but this can also cause problems for the vendor if they decide to fix the issues in a future schema update. Speaking from personal experience from the point of view of the vendor, we sometimes have to very actively tell clients to stop creating objects in our databases, because it impacts our ability to successfully apply schema changes. (I'm not just saying that -- we had a case a couple months ago where a schema update failed for one client because of an unexpected dependency from a client-created object. Slightly different scenario than adding an index, but still, it does come up.)

There are rare cases where database-level changes are okay, but you need to make absolutely certain the vendor is okay with what you're planning to do.

The best option for this scenario is... actually, not to make any changes yourself. Put together a compelling case to prove why it's important these changes be made, and present it to the vendor for them to implement in a future release.

From a vendor's point of view, ideally suggestions are specific, simple to implement and test, and have a high positive impact for all clients. In a large application, suggesting that all tables that are heaps be converted to clustered indexes may be (and probably is) a good idea, but it's a complete non-starter. Instead, figure out the top 5-10 most important tables or 2-3 application areas that would benefit from this type of change in your environment.
The application is out of license or the vendor/owner doesn't care what you do with it.

In this case, you can add any index you want, but objects should remain in place with the same names (note I didn't say the same objects). There are many options here, depending on how the application and database works internally. If you still plan to apply future vendor-supplied schema changes, tread very carefully and make sure you create undo scripts for all your changes.

The key to successfully making changes is to maintain application functionality... easier said than done, of course, if you plan to make significant changes.

That said, simply adding clustered indexes is pretty safe. The challenge will be selecting an appropriate key for each index, and this should be done by hand, not automated. The only way to do this part well is by familiarizing yourself with a table's access patterns and purpose.

Come to think of it, you could go through the same process as in the other case: identify the 5-10 tables that would have the highest overall impact, and fix those first. You may find that's all that needs to be done.

Edit: this answer, in a more general sense, is now available in video form.


Using WHERE col IN with many data OR select all rows I have a query where I select many data at once like

  WHERE id IN ( 1,2,3,4,5,6,7,8 ).

Imagine that we might have more than 10.000 Numbers inside the IN query. It's better to do the select as I'm doing it using IN() or just select all data and do the parse with PHP Code for the IDs I want?

You mentioned that the IN statement will include almost all possible values (90-95% based on your example numbers).  Joe W is correct that depending on your setup it could go either way and it's impossible to say for sure without testing which of those two would be faster.

However, you will eventually run in to a diminishing return on a query like that.  While one or the other will be faster than the other, neither will be fast. (by default MySQL will allow you up to max_allowed_packet for # of values, which is a lot).  A faster option would be to use where NOT IN and just list the 5-10% of values you didn't want.


Deadlock on Insert/Update Using SQL Server 2008 R2:

I'm currently attempting to track down the cause of a deadlock issue we have.  Not sure where to turn.  Here's the setup.

TABLE Scores:
id INT IDENTITY(1,1),
first_name VARCHAR(50),
last_name VARCHAR(50),
player_id VARCHAR(50),
score INT

PRIMARY KEY/CLUSTERED INDEX:
id

NON-CLUSTERED INDEX:
first_name, last_name INCLUDES id, player_id, score

A multi-threaded application processes messages, then calls one of two stored procedures for every message.

If the application has not processed the user, it will call a stored procedure that inserts a row into the table.

If the application has processed the user, it will update any or all of first_name, last_name, scores, based on first_name and last_name  player_id does not change.

There will be only one row in the table per player_id.

The thread that the application processes messages is chosen based on the player_id, so two threads will NOT process messages for the same player at the same time.

The INSERT and UPDATE statements are using the WITH (ROWLOCK) directive.

Here's the issue:

The process is causing deadlocks with thread 1 locking the primary clustered index and thread 2 locking the non-clustered index.

I do not understand why the locks are occurring - the UPDATE and INSERT statements are using row locks, and it's been tested and proven that threads are not accessing the same rows.

What could I do to fix this issue?

Caveats:

There must exist two stored procedures, one updating and one inserting.  As much as I would like to switch to a single MERGE, that's not gonna happen.  : /

Updating any of the NC index keys is an update that has potential to deadlock. This is the typical read-write deadlock due to different access paths. Even if you update different keys, that doesn't eliminate conflicts because the locks are on key hashes, see %%lockres%% collision probability magic marker: 16,777,215. However, you describe the problem as appearing 'immediately' which strongly suggest that some other factors are at play, and we can only speculate about in lack of actual information.

Of course it would be much more useful to capture the deadlock graph and post it here. Discussing on a mere description of the deadlock has only limited value, as your description may or may not be factual, your understanding may or may not be correct and our interpretation may or may not be accurate.


Reinstall MySql but keep database tables and data Please help!

There are server issues and MySql is no longer running on our server (Ubuntu).  The service is not recognized and needs to be reinstalled.  Unfortunately, the database has not been backed up for 48 hours and that is a lot of information.

How do I reinstall MySql AND keep all my database data?  Please note - I can't access mysql at all.  I can't use command line mysql nor phpmyadmin.

Thanks in advance and let me know if I am missing important details.

If the database files are accessible, you can follow these steps:

mv /var/lib/mysql /var/lib/mysql.bkp
sudo apt-get install mysql-server
sudo /etc/init.d/mysql stop
mv /var/lib/mysql.bkp /var/lib/mysql
sudo /etc/init.d/mysql start

It should work if you don't have any table file corruption.


DBCC FREEPROCCACHE along with Re-Organise Index maintenance task While creating Index maintenance plan in SQL Server, they have also added execute T-SQL statement DBCC FREEPROCCACHE. Is this beneficial, or are there any side-effects?

The DBCC FREEPROCCACHE command is not beneficial (for index maintenance), and should not be executed in this context.

It flushes the plan cache, which causes all query plans to be recompiled when they are executed again.

If there's no way to turn that setting off manually, it is better to use recommended third-party index maintenance scripts, such as those by Ola Hallengren or Minionware.

EDIT: For an index re-organize, you should add a step to update statistics as well, because they are not updated automatically. This step alone has the same effect as invalidating stale plans, without the negative of flushing the entire cache. The above third-party maintenance solutions include a step to only update statistics that need updating.


Bulk insert in multiple tables I have two tables on which I perform some bulk inserts:

key: key_id (pk), key_name
related_key: related_key_id (pk), key_id (fk)

First INSERT:

values_data = "($$key_1$$), ($$key_2$$)"

INSERT INTO key (key_name) VALUES values_data

Sec INSERT:

values_data = "(1, `the id of the first value inserted in key`)
             , (1, `the id of the sec value inserted in key`)"

INSERT INTO related_key (related_key_id, key_id) VALUES values_data

I don't know how to do the second INSERT because I don't know how to get those ids for the rows inserted in the key table.

Or is there a better way of doing this?

Use a data-modifying CTE to chain the inserts in a single statement.

Assuming from your example that you want to insert 1 for related_key_id in all rows.

WITH ins_key AS (
   INSERT INTO key (key_name)
   VALUES ($$key_1$$)
        , ($$key_2$$)
   RETURNING key_id       -- return newly generated key_id(s)
   )
INSERT INTO related_key (related_key_id, key_id)
SELECT 1, key_id
FROM   ins_key;


How to update a SET statement so I can use the MIN() function? I have an existing UPDATE table SET statement that was working until one of the Drivers used two different trucks in his/her shift, so now what I need to do is modify the UPDATE statement so I can use the MIN() function on a few columns. When I tried to add the MIN() function to the statement as it exists I received the error

  An aggregate may not appear in the set list of an UPDATE statement."

Here is my existing code for the UPDATE statement:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE
                        WHEN  ((CONVERT(int, DTE.StrtTm) + 800) > 2400) AND (CONVERT(INT, DTE.StartBreak) < 1600) THEN ((CONVERT(INT, DTE.StrtTm) + 800) - 2400)
                        ELSE (CONVERT(INT, DTE.StrtTm) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND DTE.StartBreak IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, DTE.StartBreak) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);

Example inputs:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   NULL
 1/1/11    1     1200   1430

Goal results:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   1430

I am not able to change the table schema so please don't come in here tell me I should redesign the entire then so it is setup properly, I don't have that luxury.

Code I tried that produced the error:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE
                        WHEN  ((CONVERT(int, MIN(DTE.StrtTm)) + 800) > 2400) AND (CONVERT(INT, DTE.MIN(StartBreak)) < 1600) THEN ((CONVERT(INT, MIN(DTE.StrtTm)) + 800) - 2400)
                        ELSE (CONVERT(INT, MIN(DTE.StrtTm)) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND MIN(DTE.StartBreak) IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, MIN(DTE.StartBreak)) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);

I'm not going to try to understand or re-write your entire query, but the typical form to get around this parser limitation is to calculate the aggregates in a CTE. The basic idea:

;WITH cte(id, dt) AS
(
  -- simplifying here, you'll need to with your logic
  SELECT DrvrID, MIN(datetimecolumn)
  FROM dbo.spr_DriverTimeEntry
  -- WHERE ...
  GROUP BY DrvrID
)
UPDATE t
  SET t.StartPlus8 = cte.dt
  FROM #tmpDriverTime AS t
  INNER JOIN cte
  ON cte.id = t.EmployeeNo;

Why create the #temp table with a different column name (EmployeeNo) instead of what it really represents (DrvrID)?

Are you updating a #table or setting a variable? Pick one.

Also try to avoid converting a date to a string to see if they're on the same day - this is by far one of the least efficient ways to do so.


unable to add index to column, duplicate entry I have a table that collects virtual machines usage metrics, I'm trying to add an index to timestamp column, to speed up queries, when selecting time, this is a sample of the table I have, it has other columns, I have only listed 3 here.

    id  timestamp            vm_name
------  -------------------  ---------
     1  2015-05-05 18:21:50  i-2-20-VM
     2  2015-05-05 18:21:50  i-2-21-VM
     3  2015-05-05 18:21:50  i-2-22-VM
     4  2015-05-05 18:21:50  i-2-23-VM
     5  2015-05-05 18:21:50  i-2-24-VM
     6  2015-05-05 18:21:50  i-2-25-VM
     7  2015-05-05 18:21:50  i-2-26-VM
     8  2015-05-05 18:21:50  i-2-27-VM
     9  2015-05-05 18:21:50  i-2-28-VM
    10  2015-05-05 18:21:50  i-2-29-VM

As you can see, my application is inserting duplicate timestamp entry.

When attempting to add an index to timestamp column, I get the following error message.

Duplicate entry '2015-05-05 18:21:50' for key 'PRIMARY'

I'm not sure what do I need to change in my DB design to resolve this issue.

Your suggestions are highly appreciate it.

I think you are trying to create a primary key and not a simple index. (Primary key requires all values to be unique)

To add a index in mysql follow the syntax here: https://dev.mysql.com/doc/refman/5.7/en/create-index.html


SQL Query suddenly slow --- how to test if its network or hardware (memory) issue? I'm not a general IT expert. Sometimes with SQL queries ... they will take 10 seconds about 95% of the time.

Every so often, the exact same query will take 5-10 minutes. As will others. The constant is the host computer and current time --- not the query itself.

I'm just wondering if there's any way for me to test or verify what the problem is that is slowing down the query (and that it is, indeed, slowing the query). Would I remote into the host machine and check the memory usage/ processes? The host machine is also in a foreign country --- USA to Germany. Again, 95% of the time, this doesn't matter. I'm wondering if there are temporary 'network spike' issues. I want to know so I can actually complain to IT there ... but I want to pinpoint exactly what's causing the query issues. Maybe this is out of scope of this board ... I know a showplan wouldn't really indicate anything ... or would it? Would network or memory issues slow all steps the same duration? Is there an easier way to troubleshoot this?

There is not just a DBA but also a SysAdmin question.  Although, there are some basic DBA tools you should have in use to help you out.

Slow query monitoring:  most databases have tools that allow you to monitor SERVER SIDE for slow queries.  Your system could be indexing, re-allocating memory and a myriad of other server side functions that could interfere with the speed of your queries.  Slow query monitoring will help you start drilling down and isolate.
Basic logging:  Make sure your server date is correlated with your local time (as in Germany +8 from US PST) to make it easy to look up your logs.  I often just (I use Linux based systems) run a tail -f on appropriate logs until the slow queries surface or note times of slow queries and then run through the logs.
Basic network network monitoring:  Setup a constant ping to your server(there are also plenty of bandwidth monitoring tools) that can be setup to alert you for slow traffic between two points as well as longer ping times.

There are different indicators based on your operating system that will show memory issues such as page faults.  Memtester for Linux will stress test your memory and all of it when you direct it to.  Memory issues can be sporadic as not all memory is used at all times so access to bad memory can be intermittent.


MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant performance gain? Are there other benefits to updating a set of records in this way?

For example,

UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo';

vs

UPDATE table1 SET value1='foo' WHERE value2='bar';

A more concise example:

+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| Field              | Type                                            | Null | Key | Default | Extra          |
+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| pk_id              | int(10) unsigned                                | NO   | PRI | NULL    | auto_increment |
| user_id            | int(11)                                         | YES  |     | NULL    |                |
| status             | enum('unread','accepted','deleted')             | YES  |     | NULL    |                |
| processed_datetime | datetime                                        | YES  |     | NULL    |                |
| some_column        | varchar(255)                                    | YES  |     | NULL    |                |
+--------------------+-------------------------------------------------+------+-----+---------+----------------+

...with another example query:

UPDATE table1 SET status = "accepted", processed_datetime = NOW() WHERE pk_id = 1234 AND user_id = 5678 AND status != "accepted"

(Note: this is not my code / schema, someone else's that I am working with)

This very much depends on the data present in the table at the time of the UPDATE.

If, for instance, the table has 1,000,000 rows where value1 = 'foo' and only 1 row where value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; will only actually update a single row.  Whereas  UPDATE table1 SET value1='foo' WHERE value2='bar'; would update 1,000,001 rows.

If you are talking about a table with only two rows, the difference is entirely negligible.

If you were using SQL Server and had a filtered index like WHERE value2 = 'bar' AND value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; would most likely use the index instead of scanning the entire table.


InnoDB row locking - how to implement I've been looking around now, reading the mysql site and I still can't see exactly how it works.

I want to select and row lock the result for writing, write the change and release the lock. audocommit is on.

scheme

id (int)
name (varchar50)
status (enum 'pending', 'working', 'complete')
created (datetime)
updated (datetime)

Select an item with a status of pending, and update it to working. Use an exclusive write to make sure that the same item is not picked up twice.

so;

"SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR WRITE"

get the id from the result

"UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>

Do I need to do anything to release the lock, and does it work like I've done above?

What you want is SELECT ... FOR UPDATE from within the context of a transaction.  SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE.  It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to.  Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary.  It also has a tendency to multiply in a codebase as people cut and paste.

Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.

First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ.  This is normally unnecessary, as it is the default isolation level for InnoDB:

session1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session1> BEGIN;
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

In the other session, update this row.  Linda got married and changed her name:

session2> UPDATE customer SET last_name = 'BROWN' WHERE customer_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

But now, we want exclusive access to this row, so we call FOR UPDATE on the row.  Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction.  That's not REPEATABLE READ, that's READ COMMITTED

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3 FOR UPDATE;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | BROWN     |
+------------+-----------+
1 row in set (0.00 sec)

Let's test out the lock set in session1.  Note that session2 cannot update the row.

session2> UPDATE customer SET last_name = 'SMITH' WHERE customer_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

But we can still select from it

session2> SELECT c.customer_id, c.first_name, c.last_name, a.address_id, a.address FROM customer c JOIN address a USING (address_id) WHERE c.customer_id = 3;
+-------------+------------+-----------+------------+-------------------+
| customer_id | first_name | last_name | address_id | address           |
+-------------+------------+-----------+------------+-------------------+
|           3 | LINDA      | BROWN     |          7 | 692 Joliet Street |
+-------------+------------+-----------+------------+-------------------+
1 row in set (0.00 sec)

And we can still update a child table with a foreign key relationship

session2> UPDATE address SET address = '5 Main Street' WHERE address_id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1> COMMIT;

Another side effect is that you greatly increase your probability of causing a deadlock.

In your specific case, you probably want:

BEGIN;
SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR UPDATE;
-- do some other stuff
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>;
COMMIT;

If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:

UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `status`='pending' LIMIT 1;

Hope this makes some sense.


Redundant to have index + full text on same column? If I have a full text index is it redundant to have a regular index on the same column? I am asking this because I need to support 5.5 and 5.6 at the same time and am using a stored procedure to create the indexes and am wondering if I need both.

Not redundant, but rarely practical.

An INDEX has limitations, based on 255 characters.  (There are too many wrinkles for me to go into detail.)

FULLTEXT is often used for big blocks of text.

Would you be looking at 'words' and doing = comparisons on the whole column?  Seems strange.

Here's one trick for when I want to search for a phrase that messes up in FULLTEXT.  Let's say I am looking for the title of your question, but I remember only 'index + full' (including the plus sign):

WHERE MATCH(col) AGAINST('+index +full')
  AND col LIKE '%index + full%'

The MATCH will quickly find only a few rows, but too many.  Then the LIKE finish filtering.  No plain INDEX is involved.


where is the oracle 12c logfile? I am getting all of the following error first:

  ORA-12162: TNS:net service name is incorrectly specified

When I export the ORACLE_SID , I can run any command and I get the following error:

  connected to an idle instance

Where is the ALERTLOG file in 12C?

I can't see any file in ORACLE_HOME/log folder. I am using ubuntu 14.04 BTW.

The alert log never has been under ORACLE_HOME/log.

It is in $ORACLE_BASE/diag/rdbms/<sid>/<sid>/trace and is named alert_<sid>.log

If you don't have $ORACLE_BASE, this typically is equivalent to $ORACLE_HOME/../../..

This is no different in 12c than it was in 11g (or 10g if I recall correctly).

You can also use the ADRCI tool: http://docs.oracle.com/database/121/SUTIL/adrci.htm#SUTIL1474


Query Performance Tuning on a huge table I have the following table Transactions. The table looks like this:

CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
[BalanceTransactionTypeID] [int] NOT NULL,
[BalanceTransactionSubTypeID] [int] NULL,
[PointDelta] [money] NULL,
[TransactionDate] [datetime] NOT NULL,

The table contains the following constraints and indexes.

CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Transactions_PlayerID] ON [dbo].
[Transactions]
(
[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON   [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Transactions_TransactionDate] ON [dbo].
[Transactions]
(
[TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
 DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
 [PRIMARY]

I am writting a query where the goal is to return the top 50 point earners, and write the results using SSIS into a seperate table that will be looked at daily. The job is scheduled to run once a day. The main issue is there are over 3 billion rows and growing by about 300K per day. I know the ultimate solution is to partition it etc since its never used in game and its just historical. We have plans to roll it up as well. For right now however I need to make this query run in a reasonable time. I have tried using a CTE to get the player ids and the sum of the Delta then joining the result of that to another table by id to get the player details, I have tried using a straight up query with the join included all of which takes over 25 minutes to run the query. What can i do to tune this and make the query runable in a normal amount of time? If there is nothing let me know but I am really hoping there is some way to optomize this for the time being. As always thanks in advance for all help.

We have plans to roll it up as well.

Cancel your other plans and do this now. Stop trying to optimise the evaluation of 3 billion rows and instead evaluate rollup + 300k rows. From your outline of the situation, anything else is a waste of time and someone's money.

A parody definition of insanity is doing the same thing over and over and expecting different results. Reading 3 billion rows that aren't changing fits that definition. Stop.


Extract JSON array of numbers from JSON array of objects I have a table containing a json array of objects similar to this:

id    |   record
____________________
name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...]

I would like to get a table containing a json array of only, say, the "a" values:

id    |   record
____________________
name1 | [0, 1, 2, ...]

I use PostgreSQL 11, so the latest functions are acceptable.

You need to first unnest the array elements, and then aggregate back each value:

select id,
       (select jsonb_agg(t -> 'a') from jsonb_array_elements(record) as x(t)) as record
from the_table;

Online example: https://rextester.com/ZONHTW97204


"SQL1042C An unexpected system error" when creating a database I installed DB2 Express-C 10.1 on OS X 10.8.4. I installed it in user mode, and it seems to have created an instance with my username (avernet). I checked the instance is indeed there by running db2ilist. The database manager is running, as when I run db2start, I get the message SQL1026N  The database manager is already active.

Now, I am trying to create a database by running db2 from the command line, and typing the command create database orbeon, but it returns:

QL1042C  An unexpected system error occurred.  SQLSTATE=58004

What am I missing here? How can I create a simple database from the command line (I prefer not to bother with Data Studio as it isn't available for OS X)?

In db2dump/db2diag.log, I see the follwing error (full file, in case you're interested):

2013-06-29-15.31.38.948217-420 E159047E344          LEVEL: Error (OS)
PID     : 1766                 TID : 140735175762304PROC : db2star2
INSTANCE: avernet              NODE : 000
HOSTNAME: huashan.local
FUNCTION: DB2 UDB, SQO Memory Management, sqloMemCreateSingleSegment, probe:100
CALLED  : OS, -, shmget                           OSERR: 17

Note that for the install to go through, I increased the OS X shared memory, per this recommendation.

Try creating your database in a different directory: db2 create db orbeon on /datadir dbpath on /dbdir, where /datadir and /dbdir are directories where whoever runs the db2sysc process has full access.


MySQL: Create index If not exists Is there a way to create an index in MySQL if it does not exist?

MySQL does not support the obvious format:

CREATE INDEX IF NOT EXISTS index_name ON table(column)
ERROR 1064 (42000): You have an error in your SQL syntax;...

MySQL version (mysql -V) is 5.1.48, but I think that MySQL lacks the CREATE INDEX IF NOT EXIST ability in all of its versions.

What's the right way to create an index only if it does not already exist in MySQL?

That functionality does not exist. There are two things to keep in mind:
Create the Index Anyway
You can generate index in such a way that the index is created without checking if the index exists ahead of time. For example, you can run the following:
ALTER TABLE table_name ADD INDEX (column_to_index);
ALTER TABLE table_name ADD INDEX (column_to_index);

This will definitely create two indexes without checking. Each index will be assigned a name (perhaps column_to_index,column_to_index_1). Of course, you are trying to avoid that.
Check INFORMATION_SCHEMA first
Here is the layout of INFORMATION_SCHEMA.STATISTICS:
mysql> show create table statistics\G
*************************** 1. row ***************************
       Table: STATISTICS
Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
  `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
  `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLLATION` varchar(1) DEFAULT NULL,
  `CARDINALITY` bigint(21) DEFAULT NULL,
  `SUB_PART` bigint(3) DEFAULT NULL,
  `PACKED` varchar(10) DEFAULT NULL,
  `NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
  `COMMENT` varchar(16) DEFAULT NULL,
  `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

You could just query for the existence of the index by name. For example, before you run
CREATE INDEX index_name ON mytable(column);

You need to run
SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';

If IndexIsThere is 0, you can create in the index. Perhaps you can write a stored procedure to create an index on the table of your choice.
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
CREATE PROCEDURE `adam_matan`.`CreateIndex`
(
    given_database VARCHAR(64),
    given_table    VARCHAR(64),
    given_index    VARCHAR(64),
    given_columns  VARCHAR(64)
)
BEGIN

    DECLARE IndexIsThere INTEGER;

    SELECT COUNT(1) INTO IndexIsThere
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_schema = given_database
    AND   table_name   = given_table
    AND   index_name   = given_index;

    IF IndexIsThere = 0 THEN
        SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
        given_database,'.',given_table,' (',given_columns,')');
        PREPARE st FROM @sqlstmt;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    ELSE
        SELECT CONCAT('Index ',given_index,' already exists on Table ',
        given_database,'.',given_table) CreateindexErrorMessage;
    END IF;

END $$

DELIMITER ;

Here is a sample run (Hey Remember This Table? It's from the question you asked back on June 27, 2012) :
mysql> show create table pixels\G
*************************** 1. row ***************************
       Table: pixels
Create Table: CREATE TABLE `pixels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pixel_data` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
Query OK, 0 rows affected (0.20 sec)

mysql> show create table pixels\G
*************************** 1. row ***************************
       Table: pixels
Create Table: CREATE TABLE `pixels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pixel_data` blob,
  PRIMARY KEY (`id`),
  KEY `type_timestamp_id_ndx` (`type`,`timestamp`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
+-----------------------------------------------------------------------+
| CreateindexErrorMessage                                               |
+-----------------------------------------------------------------------+
| Index type_timestamp_id_ndx Already Exists on Table adam_matan.pixels |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Give it a Try !!!


Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes? I'm adding the following cross-reference table to my SQL Server-hosted DB:

company_id bigint not null (FK)
org_path nvarchar (2048) not null

The company_id field refers to the id field in another table (in which it's the primary key).

Given that there can also be multiple records with the same company_id, any primary key would have to use both fields. However, I'm unable to create a key using both fields because org_path is too long for SQL Server.

As for org_path, this is the only table in which it exists. There's every likelihood that queries to this table will be asking for either all entries or all org_path entries by company_id. Or to put it another way, it looks doubtful that this table will ever be queried by org_path. Furthermore, it's unlikely that org_path will be updated, and more likely inserted and - probably rarely - deleted.

I expect that the total number of rows will be in the low thousands.

Also, the reason it's nvarchar (2048) is because the value has to mimic that in a third-party DB. A typical example will be something like

\Translation Providers\[customer name]\[order name]\

and can contain diacritics.

So my question is this: would it be more efficient to add an auto-increment id field and use that in conjunction with company_id as the primary key, or would it add unnecessary overhead - and does the fact that company_id is the primary key in another table have any effect here?

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) be unique (in combination) and NOT NULL (individually). A PK is enforced via a Unique Index, though it can be either Clustered or Non-Clustered.

A Clustered Index is a means of physically (i.e. on disk) ordering the data in the table and deals with performance; it has nothing to do with data integrity. A Clustered Index can require that the key column(s) be unique (in combination), but it doesn't need to. However, since the Clustered Index is the physical order of the data, it needs to uniquely identify each row no matter what. So if you don't set it to require uniqueness, it will create its own uniqueness via a hidden 4-byte "uniquifier" column. That column is always there in non-Unique Clustered Indexes, but it doesn't take up any space when the key fields are unique (in combination). To see first hand how this "uniquifier" column works (both in the Clustered Index and the effect on Non-Clustered Indexes), please check out this test script I posted on PasteBin: T-SQL script to test Uniquifier size.

Hence, the main question of:

  would it be more efficient to add an auto-increment id field and use that in conjunction with company_id as the primary key, or would it add unnecessary overhead

is conflating those two concepts, so they need to be addressed separately, though there is definitely some overlap.

Should an IDENTITY column be added or would it be unnecessary overhead?

If you add an INT IDENTITY column and use it to create a PK, assuming it would be a Clustered PK, that adds 4 bytes to every row. This column is visible and usable in queries. It could be added to other tables as a Foreign Key, though in this particular case that won't happen.

If you don't add the INT IDENTITY column, then you can't create a PK on this table. However, you can still create a Clustered Index on the table as long as you don't use the UNIQUE option. In this case, SQL Server will add a hidden column called "uniquifier" which behaves as described above. Because the column is hidden, it cannot be used in queries or as a reference for Foreign Keys.

So as far as efficiency goes, these options are roughly the same. Yes, there will be slightly less space taken up by having the non-Unique Clustered Index due to some rows (ones with the initial unique key values) taking up 0 bytes while all rows in the IDENTITY / PK will take the 4 bytes. But there won't be enough of the 0 byte rows (especially with the small amount of rows expected) to ever notice a difference, let alone out-weigh the convenience of being able to use the ID column in queries.

INT IDENTITY Column or Hash of org_path Persisted Computed Column?

Given that you won't be looking up rows based on org_path values, then it doesn't make sense to add the overhead of the Persisted Computed Column plus needing to compute that hash in queries in order to match against the Computed Column (this was my original suggestion, available in the revision history here, which was based on the initial wording / details of the Question). In this particular case, the INT IDENTITY "ID" Column is probably best.

Key Column Order

Given that the ID Column will rarely, if ever, be used in queries, and given that the two main use-cases are to get either "all rows" or "all rows for a given company_id", I would create the PK on company_id, id. And because this means that rows are not inserted sequentially, I would specify a FILLFACTOR of 90. You will also need to make sure to do regular index maintenance to reduce the fragmentation.

Second Question

  does the fact that company_id is the primary key in another table have any effect here

No.

Trigger

Since org_path values within a company_id are unique, you should still create a Trigger on INSERT, UPDATE to enforce this. In the Trigger, do an IF EXISTS with a query that probably does a COUNT(*) and GROUP BY company_id, org_path. If anything is found, issue a ROLLBACK to cancel the DML operation and then a RAISERROR saying that there are duplicates.

Collation

In my initial answer (based on original wording / sparse details of the question, and available in the revision history here), I had suggested possibly using a binary (i.e. _BIN2) Collation. Now that we have insight into what exactly org_path is, I would not recommend using a binary Collation. Since there will be diacritical marks, you do want to make use of linguistic equivalences.


What is the best order of columns on a compound index? I have a series of small 'attribute' tables (innodb) with this structure:

uuid
name (TEXT)
organisation_id (INT)

In my application, when users add a new attribute, I am first performing a lookup if it already exists, SELECT * WHERE name = '?' AND organisation_id = '?'

What would the optimal index setup be?

Bonus:

I am also displaying all attributes from an organisation so often just querying the table by organisation_id, do I need a separate independent key on the organisation_id column or will the compound be ok for this too?

In MySQL, the order of the columns in an index is very important. Searching over an INT is faster than search on a string(VARCHAR). So an index on (organisation_id, name) would be better than that on (name, organisation_id).

If a WHERE clause satisfies the prefix an INDEX then that INDEX is used. So no need for a separate index on organisation_id. The existing index will take care of it.


Safe to kill SPID as long as its a SELECT We just ran into a problem, where a server process running a select was blocking our entire public website. What we ended up doing was killing the server process.

My question is: Is it considered safe to kill of a server process as long as the process is only running a SELECT statement?
Or could there be some side-effects to this I haven't considered?

Ultimately it will depend on whether your website is coded to handle this happening. I have seen blocking occur when a select statement is issued within the context of a transaction which then locks a considerable amount of rows until the transaction is complete. If your website is in the middle of placing an order or billing somebody's credit card and you kill it bad things could happen if the website is not coded to handle it properly. To be sure consult the developers of the website - they would be your best resource in understanding how it will cope in situations like this.


Change MySQL's temp directory I'm trying to add keys to an InnoDB table. I started with the small ones(~6.8GB) and I encountered a problem. After a while of processing it throws an 1114 error(table is full). I restarted the process of adding indexes and monitor everything. The problem seems to be that it is using OS's temp dir(running Windows). I have it installed on another drive that has plenty of space. The error is thrown when the space on C:/ approaches 0. Is there anything I could do to have it save its temp files on the same drive where MySQL is installed and not on C:/ ? Thank you

Searched a bit more and I found the solution:

[mysqld]

tmpdir=[other drive]:/tmp

Now it's writing to the tmp directory on the wanted drive.


How to get the SUM between dates in a WHERE clause? I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example:

Query:

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
a.name AS 'name',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
ORDER BY a.id ASC

Records:

transactions:
123, 2012-01-01, 10000, 100, 0
124, 2012-01-01, 30000, 100, 0
125, 2013-01-01, 20000, 200, 0
126, 2014-01-01, 20000, 0, 100
127, 2014-01-01, 10000, 0, 200

Desired Result:

date        id      name                balance
-----------------------------------------------
2014-03-31  10000   Cash                -200
2014-03-31  20000   Payables            -100
2014-03-31  30000   Issued Capital      0

2013-03-31  10000   Cash                0
2013-03-31  20000   Payables            200
2013-03-31  30000   Issued Capital      0

2012-03-31  10000   Cash                100
2012-03-31  20000   Payables            0
2012-03-31  30000   Issued Capital      100

Tables:

calendar:
datefield (date)
transactions:
id (int), value_date (date), account_id, debit_amount (decimal), credit_amount (decimal)
accounts:
id (int), name (varchar), description (varchar)

I've already tried the following GROUP BY statements with no luck:

GROUP BY YEAR(cdr.datefield), MONTH(cdr.datefield), a.id
GROUP BY YEAR(cdr.datefield), a.id
GROUP BY YEAR(t.value_date), MONTH(t.value_date), a.id
GROUP BY YEAR(t.value_date), a.id

I'm thinking my problem has to do with the fact that I want to group by dates that not necessarily belong to the same year.

How can I modify my query to get the desired result?

Thanks!

You might laugh when you here this, by there is an aggregate clause that triggers summary between breaks in values. The aggregrate clause is WITH ROLLUP. (Look in MySQL Documentation under GROUP BY Modifiers)

let's take your query and make the following changes

Remove ORDER BY
Substitute WITH ROLLUP
Remove name

You get this

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP;

Here is what should happen

In between each datefield, there will be

datefield
id NULL
a sum of balance for that date

The last row return

datefield NULL
id NULL
a sum of balance for all dates

Based on the desired output, you want Cash, Payables and Issued Capital as zero in case there is no transaction type for the given date. You also done want the last row because you don't want the grand total. What you do is take the new query, make it a subquery, and LEFT JOIN the accounts table to the subquery, returning zero for balance if it is NULL and exclude any row whose date is NULL.

Here is the new query

SELECT
    BB.date,AA.id,AA.name,IFNULL(BB.balance,0) balance
FROM accounts AA LEFT JOIN
(SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP) BB USING (id) WHERE AA.date IS NOT NULL
ORDER BY BB.date DESC,AA.id;

I have used WITH ROLLUP to answer many posts in the DBA StackExchange

Jun 20, 2014 : Get only overall summary WITH ROLLUP and GROUP BY for multiple fields
Aug 12, 2014 : Fetch data from same table using two group by clauses in mysql
Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
Sep 21, 2014 : Monthly report by time
Jul 31, 2013 : TokuDB database size unknown in phpmyadmin
Jul 10, 2013 : How to estimate/predict data size and index size of a table in MySQL
Jul 03, 2013 : Information about Disk Storage MySQL
Apr 25, 2013 : WITH ROLLUP WHERE x IS NULL

Give it a Try !!!


How to efficiently count rows in a query by limiting the number or found rows? I have a website where I output a pagination with a limited number of pages, let's set it to 50 for example. That is, if there are actually 51 or more pages of content the 50th will be the latest that can be viewed by the users.

If a user requests a page that goes beyond the actual limit of available pages (I know it because the page's number is more than 50 or because the query for that page returns 0 rows) I want to display the last available page instead. So in this case I'll display the 50th page if there are 50 or more pages available, or I'll just calculate how many pages that are and show the latest.

Right now to do this I count how many rows are found in the query, for example:

SELECT COUNT(*) FROM my_table WHERE codition1 > X AND condition2 > Y

The problem here is that this query may even returns a count of 200,000 rows, when I actually just need to know if there are enought rows for the 50th page, or if there are less pages I need to know what's the latest.

Sometimes this query may take quite some time to iterate across the whole database to return a number that I actually not entirelly need, so I'm assuming that just counting the first (50*rows_per_page) matched rows would be a lot more efficient.

To accomplish this I've tried:

SELECT COUNT(*) FROM my_table WHERE codition1 > X AND condition2 > Y LIMIT 50*rows_per_page

But I actually get the same result of the first query.

What's the correct way, and possibly the most efficient way, to do this?

First, it's almost never OK to use LIMIT without ORDER BY, because you will not get consistent results across different executions of the query. So, especially for the pagination queries, I'd use:

SELECT <list of columns>
FROM <tables>
WHERE <conditions>
ORDER BY <some columns>
LIMIT rows_per_page
OFFSET x ;

Now, to find the number of pages this query could return - and with the extra condition that you will not use more than 50 pages - I'd try a separate query;

SELECT CEIL( COUNT(*) / 50 ) AS pages
FROM
  ( SELECT NULL
    FROM <tables>
    WHERE <conditions>
    LIMIT 50 * rows_per_page
  ) AS x ;

(and that is an exception to the "never use LIMIT without ORDER BY" rule above :)


Help in understanding Hinted Handoffs and Data replication in Cassandra We have a 3 node Cassandra cluster with RF = 2. The read and write consistency is set to ONE. We are using Vnodes as well.
Lets label these nodes as N1, N2 and N3. Let's say that N3 goes down. I was under the impression that whenever a node goes down, the other nodes would store hints and whenever N3 comes up, the hints would be sent to N3, thereby ensuring that the data is consistent across replicas. However, as I was going through the docs, I came across the parameter '' which defaults to 3 hours.
So, if a node is dead for more than 3 hours, it is considered permanently dead and no hints are stored. So far, so good.

So, my understanding now is that if a node is down for say 10 hours, then the hints for the first 3 hours would be transferred to this node when it comes back up, but the writes for this 7 hour duration would be lost for this node. Moreover, if a read query is fired for a particular token range, and since this node is also elligible to serve the read requests for a token range, it would return null instead of the actual data that is stored in some other node. Is my understanding correct? What, then, should be done?

Your understanding is correct that beyond the default 3 hours, hints are no longer stored. This is to avoid overloading the other alive nodes for prolonged period of time.

If any node happens to be down for a longer period than the hint_window, its absolutely necessary to rebuild the node or even remove all the files from its data directory and let it bootstrap as a brand new node. You may have to use replace_address if you are bringing up the same node.


Mysql Query Optimization [Match Against] I have tried all the possible option to optimize the query, but no luck.
Query 1:

SELECT
  *,
  (
    3959 * acos (
      cos(
        radians(51.5073509)
      ) * cos(
        radians(ats_latitude)
      ) * cos(
        radians(ats_longitude) - radians(-0.12775829999998223)
      ) + sin(
        radians(51.5073509)
      ) * sin(
        radians(ats_latitude)
      )
    )
  ) AS `distance`,
  (
    SELECT
      `distance`
  ) < 75 AS `in_user_radius`
FROM
  table_name
WHERE
  `feed` = 'external'
  AND (
    MATCH (`keywords`) AGAINST (
      'word1 word2 word3*' IN BOOLEAN MODE
    )
    OR `title` LIKE '%word1 word2 word3%'
  )
  AND `business_area` RLIKE ('word1')
  AND 1
HAVING
  (
    `distance` < 95
    OR isnull(`distance`)
  )
  AND (
    `in_user_radius` = '1'
    OR isnull(`in_user_radius`)
  )

Query 2:

SELECT
    *,
    MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE) AS `score1`,
    MATCH(`title`) AGAINST('"word2"' IN NATURAL LANGUAGE MODE) AS `score_title_exact`,
    MATCH(`title`) AGAINST('word2' IN NATURAL LANGUAGE MODE) AS `score_title_phrase`
FROM
    table_name
WHERE
    `jobfeed` = 'external' AND(
        MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE) OR `title` LIKE '%word1%'
    )
ORDER BY
    `nationwide`
DESC
    ,
    `score_title_exact`
DESC
    ,
    `score_title_phrase`
DESC
    ,
    `score1`
DESC
    ,
    `id`
DESC
LIMIT 48, 12

Below things I tried to optimize:
1) instead of select * I have specified the specific columns.

Observation: Due MATCH AGAINST condition query is taking too much time to execute.

please suggest some solution to optimize the above queries.

MATCH is not the primary villain in the sluggishness; these are:

OR
Using LIKE / RLIKE
pagination via OFFSET
Most of the above prevent the use of any INDEX, hence the query must scan the entire table, thereby being slow for lots of reasons.

Some can be solved in SQL; most require changes to the specifications for the query.

Since the first thing the Optimizer will use is a FULLTEXT clause, I will focus on them.

        MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE)
     OR `title` LIKE '%word1%'

This can be simplified (approximately) by:

FULLTEXT(keywords, title)   -- add this

MATCH(`keywords`, `title`) AGAINST('word1*' IN BOOLEAN MODE)

That should make query2 much faster.

Query1 is pretty hopeless as a starting point.  Can we start with query2 instead?

SELECT ...
    FROM ( ...query2... ) AS x
    JOIN table_name AS t2  USING(id)
    WHERE ...
    HAVING ...
    ORDER BY ...  LIMIT ...   -- moved from query2

The idea is to

combine the tests as discussed above;
do the most filtering possible with the least work in the derived table (the subquery);
join back to the same table to pick up things left out of the subquery;
finish with the rest of the work.


Configuring PostgreSQL to match server configuration We are currently running web application and database server on singe dedicated machine. This is hardware configuration - http://www.hetzner.de/en/hosting/produkte_rootserver/ex10. Around 50Gb of ram is free. PostgresSQL takes only 600Mb while webserver processes take 7Gb. Average CPU load is 25%.

Software is Ubuntu 12.04 and Postgres 9.1. Database size is 15Gb.

As load increased our application response time increased from 230ms to 450ms over last few months. Application takes 40% share while DB takes 60% of response time.

We cached a lot of things, but was wondering if we could get something by tweaking Postgres configuration. After bit of researched we found that default PostgreSQL configuration (https://gist.github.com/darkofabijan/9453c793ceec1ac6274d) is really conservative and that we should definitely tweak our configuration.

After running pgtune we got following recommended values.

maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
effective_cache_size = 44GB
work_mem = 288MB
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 15GB
max_connections = 200

Once we started running our PostgreSQL server with recommended values we got somehow regular spikes when application response time jumped to 2000ms+ while increase was in database response time. After running it for couple of hours we reverted to original Ubuntu 12.04/PostgreSQL 9.1 configuration.

Obviously we don't have much experience with running DBs. Both concrete recommendations regarding pgtune suggested values and pointers to good resources would be much appreciated.

Edit 1:

            name            |         current_setting          |        source
----------------------------+----------------------------------+----------------------
 application_name           | psql                             | client
 client_encoding            | UTF8                             | client
 DateStyle                  | ISO, MDY                         | configuration file
 default_text_search_config | pg_catalog.english               | configuration file
 external_pid_file          | /var/run/postgresql/9.1-main.pid | configuration file
 lc_messages                | en_US.UTF-8                      | configuration file
 lc_monetary                | en_US.UTF-8                      | configuration file
 lc_numeric                 | en_US.UTF-8                      | configuration file
 lc_time                    | en_US.UTF-8                      | configuration file
 log_line_prefix            | %t                               | configuration file
 log_timezone               | localtime                        | environment variable
 max_connections            | 100                              | configuration file
 max_stack_depth            | 2MB                              | environment variable
 port                       | 5432                             | configuration file
 shared_buffers             | 24MB                             | configuration file
 ssl                        | on                               | configuration file
 TimeZone                   | localtime                        | environment variable
 unix_socket_directory      | /var/run/postgresql              | configuration file

This is a pretty broad topic. I suggest you pick up a copy of Greg Smith's excellent book "PostgreSQL 9.0 High Performance" and work through it. http://www.packtpub.com/postgresql-90-high-performance/book

Can you show the output of the following query:

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

Things to test:

wal_buffers = 16MB
checkpoint_segments = 32 #(or higher)
shared_buffers = 6GB
random_page_cost = 2.0 # not the first choice of things to tune

What are your logging settings? Are there obvious slow queries? If so, you can run them through pgbadger to give you a good idea of queries to target.

Here are some settings you can test, given your current settings and what you have mentioned about your system.

log_line_prefix = '%t [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] '
shared_buffers = '6GB' # requires cluster restart
checkpoint_completion_target = 0.8
checkpoint_segments = 32
effective_io_concurrency = <lesser of cpus and disks/channels>
log_destination = stderr    # This and the log_directory are what I use,
log_directory = pg_log      # it is up to you whether or not you want to change them
logging_collector = 'on'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = '2s' # Set this as necessary, set lower per role
random_page_cost = 2.5 # If faster disks, drop to 2.0
track_activity_query_size = 4096
work_mem = '50MB'   # Adjust this as necessary.
                    # Be aware this is a per-action setting,
                    # eg. aggregation, sorts, etc can each consume up to
                    # this amount of RAM

You'll need to restart the database for some of those settings to take effect.


BULK INSERT not supported I have a text file, which I want to insert into a table in my database.

My query looks like this:

BULK INSERT myDepartment
FROM 'C:\myDepartment-c-t.txt'
WITH (
  DATAFILETYPE = 'char',
  FIELDTERMINATOR = '\t', -- default terminator
  ROWTERMINATOR = '\n'
);

On executing it, SQL Server Management Studio 2008 gives the following error:

'BULK' rowset provider not supported in this version of SQL Server.

I googled it and found that SQL Server 2008 does support BULK INSERT. Can anyone tell me what am I missing?

'BULK' rowset provider not supported in this version of SQL Server.

BULK INSERT is not supported on SQL Azure. You can use bcp or write something that wraps SqlBulkCopy to bulk insert into SQL Azure. There's other ways to bulk insert aside from these, too.

  On executing it,my SQL management studio 2008,gives the following error :

Don't confuse SQL Server Management Studio with SQL Server Database Engine.


MySQL Select previous row that matches condition I am using MySQL 5.7 and I have 2 tables where relevant data looks like this:
SQL was exported using mysql workbench, let me know if it doesn't work or if it doesn't make sense:
CREATE DATABASE  IF NOT EXISTS `blog`
USE `blog`;

DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` bit(1) NOT NULL DEFAULT b'0',
  `date` date NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_article_1_idx` (`category_id`),
  CONSTRAINT `fk_article_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

LOCK TABLES `article` WRITE;

INSERT INTO `article` VALUES (1,_binary '','2020-02-01',2),(2,_binary '','2020-02-01',2),(3,_binary '','2020-02-02',2),(4,_binary '\0','2020-02-05',2),(5,_binary '','2020-02-06',2),(6,_binary '\0','2020-02-08',2),(7,_binary '\0','2020-02-11',2),(8,_binary '','2020-02-13',2),(9,_binary '','2020-02-16',3),(10,_binary '','2020-02-29',2),(11,_binary '','2020-03-01',2),(12,_binary '\0','2020-02-29',2);

UNLOCK TABLES;

DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

LOCK TABLES `category` WRITE;

INSERT INTO `category` VALUES (1,'category_1'),(2,'category_2'),(3,'category_3'),(4,'category_4');

UNLOCK TABLES;

What I want to accomplish is get the articles(or even better a list of article pairs[updated-not_updated] that fit in a certain category that were not updated, but the direct previous(by this I mean previous date) article belonging to the same category was updated. If the dates are equal then additional ordering is by id.
For the examples sake I guess I'm interested in retrieving the articles 4(and 3), 6(and 5) and 12(and 10) since these are the ones that are not updated but the article posted right before them that was in the same category was updated.
This doesn't have to be happening all in one query(although that would be the preferred way). I'm using java with Spring native queries if it matters.
I did think of getting all articles that were not updated and then check each record to see if it fits my criteria but it doesn't seem this is the right approach since I can get to maybe a couple of articles that were not updated and making the check could hurt performance and processing times. Any way I can do this directly from MySQL with a native query?
I hope my question is well formed, please ask for more details if they are needed. Column names have been changed but the structure is correct(in case you are wondering why they weird criteria for select)
Thanks in advance.
Reason for edit: provide create and insert startements(exported with mysql workbench)

With MySQL 8 it would be much simpler using window functions.
The problem is with your data that you have no sequential order, so we have to get one by using row number.
This way we can compare the row numbers from the coplete table and those with has was_updated = 0.
This will only work in MySQL because it holds the ORDER BY from the inner select in MariaDB you have to use LIMIT to keep the order.

CREATE TABLE articles
    (`id` int, `category` int, `was_updated` int, `date_added` varchar(10))
;

INSERT INTO articles
    (`id`, `category`, `was_updated`, `date_added`)
VALUES
    (1, 2, 1, '10-01-2020'),
    (2, 2, 1, '11-01-2020'),
    (3, 2, 0, '13-01-2020'),
    (4, 2, 0, '20-01-2020'),
    (5, 1, 1, '22-01-2020'),
    (6, 2, 1, '23-01-2020'),
    (7, 2, 1, '02-02-2020'),
    (8, 2, 0, '29-01-2020')
;

SELECT
    a3.id
FROM
    (SELECT
        a.*, @rown2:=@rown2 + 1 rn
    FROM
        articles a, (SELECT @rown2:=0) t1
    ORDER BY id) a2
    INNER JOIN
    (SELECT
           id, rn,`category`
        FROM
            (SELECT
                a.*, @rown:=@rown + 1 rn
            FROM
                articles a, (SELECT @rown:=0) t1
            ORDER BY id) a1
        WHERE
            `was_updated` = 0) a3
ON

a2.rn  + 1 = a3.rn
AND
a2.`category` = a3.`category`
WHERE a2.`was_updated` = 1

| id |
| -: |
|  3 |
|  8 |

db<>fiddle here


Does mysql use deleted rows space in a table? I am deleting a large data around 60GB from one of my innodb table which is around 70GB.

This is a rapidly growing table so i keep on inserting and deleting the records over a period of time. I cannot do DB optimization every time I delete.

So, does mysql use this deleted rows space (which is not given back to OS) for newly inserted rows?

update: innodb_file_per_table is ON already.

Here is something to consider: you need to determine how much fragmentation exists before deciding to defragment the table or not.

The most efficient way is to measure the space usage from the OS and from INFORMATION_SCHEMA

For this example, assume the following

datadir is /var/lib/mysql
database is mydb
table is mytable

OS

TABLE_SIZE_OS=`ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'`
echo ${TABLE_SIZE_OS}

INFORMATION_SCHEMA

SELECT (data_length+index_length) INTO @table_size_inf
FROM information_schema.tables
WHERE table_schema'mydb'
AND table_name='mytable';
SELECT @table_size_inf;

Simply subtract @table_size_inf from TABLE_SIZE_OS to get the number of bytes unused

Since you deleted 85.7142 % of the table (60GB out of 70GB), you could then run

OPTIMIZE TABLE mydb.mtable;

innodb_file_per_table is disabled

You could do this against an entire database

cd /var/lib/mysql
IBDATA_SIZE=0
for X in `ls -l ibdata* | awk '{print $5}'` ; do (( IBDATA_SIZE += X )) ; done
SQLSTMT="SELECT SUM(data_length+index_length)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='InnoDB'"
INNODB_SIZE=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( IBDATA_FRAG = IBDATA_SIZE - INNODB_SIZE ))

I have suggested stuff like this before

Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Jul 03, 2013 : Information about Disk Storage MySQL

UPDATE 2017-01-31 07:40 EST

You just asked the following

  so i am assuming , if i don't do optimization and keep on inserting data , newly inserted records will use the space which is left over by previously deleted records and table size won't grow. However it depends on the newly inserted data size and contiguous space left over by previously deleted data . Am i correct?

My answer to that is Yes. Deleted rows are simply marked and are available for reuse.

However, each 16K block would have its own internal fragmentation. Even my answer does not compute the granularity of fragmentation within 16K blocks. The more sparse a 16K block is due to deleted space, the more disk I/O InnoDB will experience in traversing 16K blocks in search of free space wide enough for row insertion.

If you are satisfied with the rate of your bulk insertions, then there is no need to OPTIMIZE TABLE that one table. If your index statistics against that table make SELECT queries bad, just run ANALYZE TABLE instead of OPTIMIZE TABLE.


Add unique constraint on combination of 2 columns I'm trying to make two columns (combined) unique in a mysql table. I've searched the stack, but the solutions don't work.
So I'll explain by using an example

cartID   productID
10       7           -> allowed
10       8           -> allowed
11       7           -> allowed
11       7           -> not allowed, the combination of 11 7 already exists in table

The 'solutions' I found are the following:
ALTER TABLE cart
  ADD CONSTRAINT uq_cart UNIQUE(cartID, productID);

But this just seems to add a constraint on each column individual (so a 10 8 would also throw an exception if 10 7 already existed).

A constraint like:

UNIQUE(cartID, productID)

means that the combination of cartID and productID is unique, not the individual columns. It is however possible that something in your syntax makes MySQL ignore the constraint. Can you try the following:

CREATE TABLE Orders
( cartID int not null
, productID int not null
, unique (cartID, productID) );

insert into Orders (cartID, productID) values (10, 7), (10,8), (11,7),(11,7);
ERROR 1062 (23000): Duplicate entry '11-7' for key 'cartID'

insert into Orders (cartID, productID) values (10, 7), (10,8), (11,7);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0