optimization – Optimize SELECT statement for mysql database

I have this query:

SELECT COUNT(*) AS "Other"
FROM events
JOIN triggers ON events.objectid = triggers.triggerid
WHERE events.name REGEXP "No data from SAM0056(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline"
AND events.value = 1 
AND triggers.value = 1;

Altogether I need to execute 32 variants of these queries depending on REGEXP. And I also need to run these queries every 15. So I have a lot of select statements. How can I optimize the database for these queries?

In this case, should I create indexes and on which tables? These are the tables involved (events and triggers):

| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| events |          0 | PRIMARY  |            1 | eventid     | A         |        7633 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_1 |            1 | source      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_1 |            2 | object      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_1 |            3 | objectid    | A         |         954 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_1 |            4 | clock       | A         |        7633 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_2 |            1 | source      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_2 |            2 | object      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | events_2 |            3 | clock       | A         |        7633 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Y

| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| triggers |          0 | PRIMARY    |            1 | triggerid   | A         |        1826 |     NULL | NULL   |      | BTREE      |         |               |
| triggers |          1 | triggers_1 |            1 | status      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| triggers |          1 | triggers_2 |            1 | value       | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| triggers |          1 | triggers_2 |            2 | lastchange  | A         |        1826 |     NULL | NULL   |      | BTREE      |         |               |
| triggers |          1 | triggers_3 |            1 | templateid  | A         |         228 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

I also see some absurd results in my interface for this query, like vb: 264 or 84 or 56. This is not possible if you look at the were clause. Could it be a hardware problem or the database can't keep up with the selections? How can I diagnose these absurd results?

I use mariadb version 10.1.44 in an Ubuntu version 18.04.4.

lightroom – Converts a large database of mixed-format files to screen-size JPEG files

If you have the technical skills to install Python3 and the Python Image Library on your computer, you can use this Python script:

# !/usr/bin/python3
import os
import sys
from PIL import Image

size = 128, 128

def thumbnail(fromFile,  toFile):
    _, ext = os.path.splitext(fromFile)
    if ext.lower() in ('.jpg', '.jpeg', '.png'):
      print('Creating thumbnail', toFile)
      im = Image.open(fromFile)
      im.thumbnail(size)
      im.save(file + ".thumbnail", "JPEG")

(_, fromDir, toDir) = sys.argv
for root, dirs, files in os.walk(fromDir, topdown=True):
   for name in dirs:
      toPath = os.path.join(toDir, root, name)
      print('Making', toPath)
      os.makedirs(toPath, 0o777, True)

   for name in files:
      fromFile = os.path.join(root, name)
      toFile = os.path.join(toDir, root, name)
      thumbnail(fromFile,  toFile)

Run it like this: python3 scriptname sourcedir destdir

Remember that sourcedir Must be a relative path name. If you have a file called /usr/traveler/mystuff/images/2009/Mar/IMG3.jpggo to the directory /usr/traveler/mystuff, and use python3 scriptname images thumbnails, the output will be in /usr/traveler/mystuff/thumbnails/images/2009/Mar/IMG3.jpg

Drupal 8 Recommended Database

What is the recommended database for Drupal 8? Is it MySQL or MariaDB? I have no particular preference and just needed a recommendation on what is the best long term database for Drupal 8.

clusters: SQL server error message "the database is read-only" on a remote server: availability database

I have a failover cluster with a highly available sql server service, the problem is that I can't create a table, update, delete remotely even if I already grant specific permissions and already made the request "ALTER DATABASE [** **] SET READ_WRITE ".

I don't understand why I always get the "database is read-only" error message every time.

Thank you.

Fill database drop-down menu – WordPress Development Stack Exchange

I am creating a custom plugin for our martial arts school and I have encountered a problem that is affecting me. I'm definitely missing something obvious, but I've never dealt with WordPress in this capacity before, instead of breaking my computer, I thought I'd ask the brains to trust what I'm doing wrong;)

the following code works perfectly and generates the dropdown menu:






but the following does not work from WordPress:


Am I not connecting to the database correctly?
Is there something else I'm missing?

I appreciate any help.
Thank you

database design: what's the best way to store multidimensional time series data in a (R) DBMS environment?

Any suggestions on how to store and organize multidimensional time series data in an RDBMS environment? For example, we have multiple experiments from multiple clients. This translates well to say a NetCDF file type structure, where a NetCDF can be for a client project, and contains multiple data variables in overtime. This image explains the concept.

From my preliminary research, this seems like an unsolved problem or one with a complicated solution. Are there any recent (not necessarily optimal) attempts at this?

SQL Server: Query to verify long-running queries on a database for the past week

How to find long-term queries in a database in the last week?

sql server – Maintenance plan error – Backup failed due to database name

Good afternoon,

I have an interesting problem. One of our data engineers created a database to hold the performance metrics, but created the name of the database as the named instance of this specific SQL server (i.e. Server / InstanceName).
I created a maintenance plan yesterday to take full backups and transaction log regularly.

I noticed that the history of my maintenance plan was all red, indicating ERRORS in all executions. Upon further investigation, I noticed

You can see that the SQL server was trying to escape the & # 39;  & # 39;

Basically, the & # 39; & # 39; in the database name you are spoiling the specified file path. Think of it as a subfolder below.
I wonder if there is something around this.
I know I can simply exclude that database from the maintenance plan, but then when new databases are added to the server I manually add them (I don't like it at all) to the maintenance plan which will be backed up by choosing Specific databases instead of & # 39; All user databases & # 39 ;.

Or maybe you could use some kind of synonym to tell the SQL server that I mean that database, without using the actual name of the database.

Any help would be greatly appreciated as I spend quite a bit of time and effort planning these backups, just to make the history look like it is not running (it is. It just doesn't back up the database in question)

update the value of the matching column anywhere in the sqlserver database

I have assetid column in multiple table in my database and asset table (main)

I want to update all occurrences of tables in all occurrences of inserts in the database assetid column where the value has 1020 to 1

sql server: can changing the database owner affect permissions or role memberships?

If the user owns the database, that user implicitly gets db_owner permission, without having to explicitly assign that role.

The database owner (single user) is a different assignment than membership in the db_owner paper, but they have the same effect. I like to remember that since the database owner is an INSTANCE LEVEL setting (i.e. it is in sys.databases, but db_owner role membership is a DATABASE LEVEL permission.

A user can ONLY be the owner of the database (server level) and have no permissions within that database. When that happens, changing the database owner to sa would effectively block that user from the database they previously owned.

To avoid this problem, you will need to add the user to db_owner role for that database first, then change the owner.