Tag: database

Postgresql – TimescaleDB Performance Database Administrators Stack Exchange

I try to optimize some queries at a large table, but can not speed it up. I was wondering if it is possible to run TimescaleDB even faster?

Test results:




Select tracker => number (*) from clicks where click_at is between & # 39; 2019-04-15 00: 00: 00 & # 39; and & # 39; 2019-04-17 23: 59: 59 & # 39 ;;;
number
----------
31385884
(1st row)

Time: 2306.110 ms (00: 02.306)

When I try to group the results, it gets a little worse:




select tracker => time_bucket (& # 39; 1 day & # 39 ;, click_at) as ts, (*) count from clicks, where click_at is between & # 39; 2019-04-15 00: 00: 00 & # 39; and & # 39; 2019-04-17 23: 59: 59 & # 39; group after ts;
ts | number
------------------------ + ----------
2019-04-15 02: 00: 00 + 02 | 28855475
2019-04-14 02: 00: 00 + 02 | 2530409
(2 rows)

Time: 3453,420 ms (00: 03.453)

Both queries execute a parallel index or seq scan, depending on the block size.

It was expected that a piece has at least 100 million records.

Can you do it faster or not?

The clicks table structure is below.

Thank you in advance!

        
        
        
        Column | Type | Sorting | Nullable |
default
----------------------- + -------------------------- + ----------- + ---------- + ------
-------------------------------
id | bigint | | not null | NexTV
al (& # 39; clicks_id_seq1 & # 39;; regclass)
click_at | Timestamp with time zone | | not null | now()
hash_id | Characters vary | | not null |
offer_id | Integer | | not null |
affiliate_id | Integer | | not null |
affiliate_sub | Text | | |
affiliate_sub2 | Text | | |
affiliate_sub3 | Text | | |
affiliate_sub4 | Text | | |
affiliate_sub5 | Text | | |
Source | Text | | |
ip | inet | | |
country_iso | Characters vary | | |
Connection type | smallint | | |
asn | Integer | | |
long | Characters vary | | |
referer | Text | | |
device_types_id | Integer | | |
browsers_id | Integer | | |
offer_payout_types_id | Integer | | |
offer_urls_id | Integer | | |
offer_files_id | Integer | | |
affiliate_click_id | Text | | |
affiliate_unique1 | Text | | |
affiliate_unique2 | Text | | |
affiliate_unique3 | Text | | |
affiliate_unique4 | Text | | |
affiliate_unique5 | Text | | |
user_variables | Text | | |
devices_id | Integer | | |
ip_proxy | inet | | |
tracking_users_id | Integer | | |
Conversion status | smallint | | |
indices:
"clicks_affiliate_id_idx" btree (affiliate_id)
"clicks_click_at_idx" btree (click_at DESC)
"clicks_hash_id_idx" btree (hash_id)
"clicks_id_idx" btree (id)
"clicks_offer_id_idx" btree (offer_id)
"clicks_tracking_users_id_idx" btree (tracking_users_id)
Triggers:
ts_insert_blocker BEFORE INSERTING INCLUDE Click _ti for each series execution method
mescaledb_internal.insert_blocker ()

How do I handle the password for the database connection?

What you come across is one of the basic problems of client-server architecture. In order for the client computer to access the database, you need credentials for that database on the client computer. As an accepted response to this question asked by Robert Harvey explains, the user of the application can not be completely prevented from receiving these credentials.

This is a problem that is simplified by moving to an N-tier architecture. Apart from that, I see two main approaches:

  1. Create database credentials for each user. Then, the DB credentials do not have to be stored on the client computer. Logging into the database could then replace the management of user passwords in a table. You would then need to maintain the rights in the database for each user to prevent them from accessing data that is beyond their authority.

  2. Encrypt the database password with the credentials of the clients. This is extremely error-prone (especially for credential updates (both for users and for the database).) Ideally, you'll find a well-tested, ready-made solution for that, but I doubt you'll find one because such a solution is still a reality for you Someone with user credentials (legitimized or otherwise) can intercept the database credentials, and you need to check what access that database credential provides and what someone could do with them for your database.

It may not be easy, but the preferred approach to improving security is to move to a more contemporary architecture.

magento2 – how to update data in the database table

I send data to the database by checking duplicate data. However, I can not insert / update data in the database.

protected function saveEntityFinish (Array $ entityData, $ table) {
$ collection = $ this-> PostTableFactory-> create ();
$ item = $ collection-> getCollection ();


if ($ entityData) {
$ tableName = $ this -> _ connection-> getTableName ($ table);
$ entityIn = [];
$ entityInup = [];
foreach ($ entityData as $ id => $ entityRows) {
foreach ($ entityRows as $ row) {
$ item-> addFieldToFilter (& # 39; rule_id & # 39 ;, $ row)['rule_id']);
$ item-> addFieldToFilter ('store_id', $ row)['store_id']);

if (count ($ item-> getData ()) == 0) {
$ temp = $ item-> getData ();
$ logger-> info (& # 39; one & # 39;);
$ collection-> setRuleId ($ row)['rule_id']);
$ collection-> setStoreId ($ row)['store_id']);
$ collection-> setStatus ($ row['status']);
$ entityIn[] = $ row;
$ collection-> save ();
}otherwise{
$ entityInup = $ row;


$ collection = $ this-> PostTableFactory-> create ();
$ collection-> load ($ item-> getData ()[0]['p_id']);

$ collection-> setRuleId ($ entityInup['rule_id']);
$ collection-> setStoreId ($ entityInup['store_id']);
$ collection-> setStatus ($ entityInup['status']);
$ entityIn[] = $ entityInup;
$ collection-> save ();

}

}
}


}
return this $;
}

Can I get help? thank you in advance

Google Sheets – Can I navigate from a pivot table back to the original database?

When I'm on a pivot sheet, there's an easy way to click on a row or cell in the pivot table and navigate directly to the related data in the original database (Google Sheet), from which the pivot Derived table. The use case would be a pivot table, indicating, for example, only the name of a sales representative and the area assigned to him. If you click on the name of the sales representative, you will be taken back to that line or to the first line that contains the server. Name of the seller and all other data (columns / fields) in the original Google spreadsheet (database).

mysql 5.6 – Quiz System Database Schema

I want to create a database schema for a quiz system that is little different from the rest of the typical schema.

As we all know, the companies involved are involved

  1. Quiz type (s)
  2. Sections / Groups
  3. ask
  4. Responses of the target group

So far, the database schema is the same as we all should have, but in my case the point where it differs from the other is when the question falls under the section / group C, which in my case is called that AUTO FAIL when someone from the audience selects an option Yes then the entire quiz will automatically fail.

Another case I have is when the user selects from section / group D. incorrect Of the options, the question should not be considered a quiz question and their scores from the grand total should be excluded (not included in the quiz marks).

I have a table assessment_section_question_option where I have the following:

  1. question_id
  2. option value
  3. Result
  4. View Order

I need the best / best way to customize that in the database, since I'm not going to hard-code anything on the client side. Thank you in advance.

Note: It is basically a quality check.

Database Site redirects automatically

[17-Apr-2019 10:13:58 UTC] WordPress database error You have an error
in your SQL syntax; Check the manual that corresponds to your MySQL
Server version for the correct syntax that is close to & # 39; 1 & # 39; to be used in line 1
Query 1 after request (& # 39; wp-blog-header.php & # 39;),
required_once (& # 39; wp-include / template-loader.php & # 39;),
include (& # 39; / themes / attorney / page.php & # 39;), get_footer, locate_template,
load_template, required_once (& # 39; / themes / attorney / footer.php & # 39;),
dynamic_sides, call_user_func_array, WP_Widget-> display_callback,
Wp_StatsMechanic-> Widget