I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available; * OS Debian 9; * Mysql 5.6.47; To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed. I had enabled the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE MALICIOUS_TABLE (
  column_1 int(11) NOT NULL AUTO_INCREMENT,
  column_2 varchar(8) NOT NULL,
  column_3 datetime NOT NULL,
  column_4 int(11) NOT NULL,
  column_5 int(11) DEFAULT NULL,
  column_6 int(11) DEFAULT NULL,
  column_7 int(11) DEFAULT NULL,
  column_8 tinyint(1) DEFAULT NULL,
  column_9 datetime DEFAULT NULL,
  column_10 int(11) DEFAULT NULL,
  column_11 varchar(2048) DEFAULT 'column_11',
  column_12 tinyint(1) DEFAULT NULL,
  column_13 datetime DEFAULT NULL,
  column_14 tinyint(1) DEFAULT NULL,
  PRIMARY KEY (column_1),
  KEY fk_ual_aut_idx (column_2),
  KEY fk_aul_c_idx (column_4),
  KEY kf_ual_po_idx (column_5),
  KEY fk_ual_ute_idx (column_10),
  KEY column_1 (column_1),
  KEY column_2 (column_2),
  CONSTRAINT fk_aul_c FOREIGN KEY (column_4) REFERENCES t_table2 (column_4) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_aut FOREIGN KEY (column_2) REFERENCES t_tabl3 (column_2) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_po FOREIGN KEY (column_5) REFERENCES t_table4 (column_5) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_ute FOREIGN KEY (column_10) REFERENCES t_table5 (column_10) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from MALICIOUS_TABLE;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time. The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no. Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself? Thank you in advance.

I am trying to implement a ticket system, the numbers generated would be sequential. 4000, 4001, 4002, 4003 My idea was to have a table that relates to current raffle, and the ticket_no column is an auto increment column that resets when the foreign key changes. The composite keys are raf_id and ticket_no since the ticket no should not exist multiple times in same raffle Is this possible? |------------|-----------|---------| | raf_id | ticket_no | user_id | |------------|-----------|---------| | 1 | 4000 | 1 | | 1 | 4001 | 1 | | 1 | 4002 | 2 | | 1 | 4003 | 3 | | 2 | 4000 | 4 | | 2 | 4001 | 4 | | 2 | 4002 | 5 | | 2 | 4003 | 1 | |------------|-----------|---------|

I'm developing a table structure to store the physical activity data and users can save their progress. The activities belong to multiple programs. like beginner program suppose be like having 1000 step counts and 100 pushups. The intermediate program would be like like exercise for 30 days and get Saturday off like so. I am confused about whether to store these program info in separate column or as json.

I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this? - Option 1 : should I integrate all sensors into a single database like this Table Sensor --- SensorID SensorType SensorDesc Table SensorReadings --- SensorReadingID SensorID ReadingDateTime ReadingValue - Option 2 : Or should I put each sensor type into separate table? Table TemperatureSensor --- SensorID SensorDesc Table TemperatureSensorReadings --- SensorReadingID SensorID ReadingDateTime ReadingValue (double) Table MotionSensor --- SensorID SensorDesc Table MotionSensorReadings --- SensorReadingID SensorID ReadingDateTime ReadingValue (integer) Considering that various types of sensor tables will have similar fields but potentially different reading data types (some of them are integer and some of them are doubles, etc). Is it logical to separate the tables for each sensors? Or keep using a single table for every type of sensors? - Which one is the best practice to implement such database if there might be various types of sensors to be added in the future? - Which one can be better optimized for performance? Thank you

I have a table called " Product " it has: **ID_P**, **Name**, **expiration date** Now I want to create another table called "Stock" this table has **ID**, **Quantity_of_the_product**, **product_location_in_the_stock**, **ID_P** (foreign key), and **expiration date** I want to bring the expiration date from the table product I am new to SQL oracle so please be patient with me I am using: SQL ORACLE **UPDATE:** and is it right if I do this CREATE TABLE stock (ID, Quantity_of_the_product,product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product WHERE stock.id_p = product.id_P

I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s. When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM table;, the query now takes 18s. I could not see any notable difference between the plan. What are the steps I can take to minimize this 8s overhead? I have already tried using UNLOGGED keyword.

Someone accidentally deleted several records from Cassandra's table in the cluster. I want to restore the last snapshot to another copy table with the same structure within the cluster. What is the best way to do that? Cassandra v4.0.4

I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where **actions** have **requirements** in multiple other tables, but also have **influences** in multiple other tables. In my end design I will have at least 6 tables that an **action** has potential **requirements** for and a potential **influence** on. Now, there are 2 ways I've thought of it myself: 1 Make action_req_x and action_inf_x tables for every x which would look like this: action_id ... x_id ... minvalue ... maxvalue ... Where the minvalue and maxvalue can be changed upon which table it's refferencing. For example, stats would be Decimal(10,3) and items would be Integer(1). This method would require a LOT of tables and I can't believe I've found the best method on my own. 2 Make action_req_x and action_inf_xtables, but only **one** of each, which would look like this: action_id ... x_tablename ... x_id ... minvalue ... maxvalue ... but this wouldn't be perfect, because different tables have different types of values and I wouldn't know how to setup FK's :( I don't necessarily think this is a really good way to go, but it's better than nothing.. Now, so far I've only talked about the influences and requirements for **actions**, now I'm not sure yet, but I might eventually want **items** to have requirements and influences as well, which would require even MOOOORE tables! My **question**: Is there a better way to handle this than my first method? Or is there a way I can alter my second method and make that work better? How would database professionals attack this problem?

lets say I have a users,posts and categories tables .. - users table would have statuses like ( active, expired .. etc ) - posts table would have statuses like ( published, draft, archived .. etc ) - categories would also have different statuses Is it bad if I did my tables like this: users table: - id - name - email - password - status_id posts table: - id - title - desc - status_id categories table: - id - title - status_id status table: - id - name ( active, expired , published, draft, archived .. etc )

I have two csv files, countries and birth_rate_stat with the following data: | ISO | ISO3 | ISO_CODE | FIPS | Display Name | Currency Name | Phone_Code | Region Code | |---------------|--------------|---------------|-------------|------------------|--------------------|------------------|------------------| | AF | AFG | 4 | AF | Afghanistan | Afghani | 93 | Asia | | AX | ALA | 248 | | Aland Islands | Euro | 340 | Europe | | country_code | country_name | year | birth_rate | |-------------------|-----------------|------------|-----------------| | SI | Slovenia | 2036 | 7.59 | | SI | Slovenia | 2022 | 7.52 | and i created two tables CREATE TABLE countries ( iso varchar(2) NOT NULL, iso3 varchar(3) DEFAULT NULL, iso_code int(11) NOT NULL DEFAULT 0, fips varchar(2) DEFAULT UNIQUE, display_name varchar(255) DEFAULT NULL, currency_name varchar(255) DEFAULT NULL, phone_code int(11) DEFAULT NULL, region_code int(11) DEFAULT NULL, PRIMARY KEY (iso) )ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE birth_rate_stat ( iso varchar(2) NOT NULL, year int(11) NOT NULL, crude_birth_rate float NOT NULL, PRIMARY KEY (iso,year), CONSTRAINT crude_birth_rate_ibfk_1 FOREIGN KEY (iso) REFERENCES countries (iso) )ENGINE=InnoDB DEFAULT CHARSET=latin1; The problem that i face while trying to connect the birth_rate_stat table with the countries one is that, in the birth_rate_stat table, the column country_code contains entries from both the ISO and the FIPS columns from the table countries. That means I can't set iso as a primary key nor fips as fips contains null values. I tried to create a country_id column which would distinguish the countries but then i get an error Cannot add or update a child row: a foreign key constraint fails which makes sense since it doesn't exist in birth_rate_stat. Do you have some suggestions as to how i could connect the two tables?

After deleted a large dataset (~50GB) and optimized tables, some tables have data free (~50GB) and parts of tables have only data free (~KB). As my understanding, the data free are reused and not keeping the ibdata growing. The situation is: My commonly used tables are growing really fast, but unfortunately the data free are allocated to not commonly used tables. The disk space of ibdata is keep growing but the data free is not reused. The question is: Can the data free allocated to commonly used tables, so it can reuse the (~50GB) space, since the storage is costly on cloud?

Could some one please help/guide me how I can script out all the tables structure from a single database in **Elevate database.** (In SQL there is a way to do it but not sure how to do it in Elevate DB) I am new to elevateDB and tried to find out at online portals but didn't get any useful information. Any help will be appreciable. Note: This ElevateDB is on elevate software, just read online about this. Elevate Software develops and markets the DBISAM and ElevateDB embedded database engines for database application developers, and the Elevate Web Builder development environment for web application developers.

i want to copy table privileges dba_sys_privs in table A to new table B. i have create table B. What left now was it's table privileges'. Kindly advice. Thanks

I am creating a CRM for a call-center and I want to store the input field data in a database. My problem is that I am quite new to this ordeal and have little - to no idea how to structure my database. Should I create multiple tables for each part of the form and submit the data there or should everything be submitted to one large table? Note 1: the form has around 80 fields with the data I need. Note 2: I also have another form with approximately 50 fields and plan to add another form which will be much small with app. 15 fields. I appreciate any help and thank you for your time in advance!

I have db and I am trying to create table called "sales_invoice_grid" I am having bellow scenarios: error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it already exists. error 2: When select rows from "sales_invoice_grid" it gives the message that, the table is not exist

I would like to log new products, updates to products and deletions of products (and the same for other schemata [customers, locations etc]). Would it be best to have a separate table (e.g. ProductLog, CustomerLog) for this, so that I can have a product foreign key as a field in the ProductLog, customer foreign key in the Customer Log etc? Or should I use one table to avoid creating essentially double the amount of tables?

Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?

I'm using postgresql 13/14. lets say I have a table with column A,B,C,D,E I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for update actually, the trigger function is just a bit generic) it has run good for now. problem is, column E is only for checking, and it's not needed to run the trigger if it's updated. my trigger function, is now like this:

CREATE FUNCTION public.fnc_check()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
	if NEW.AOLD.A and NEW.BOLD.B and NEW.COLD.C and NEW.DOLD.D then
         -- do something
    end if;

	RETURN NEW;
END;
$BODY$;

problem is, on my real table, there are like 20-30 columns and it's a hassle to make if for every columns except for column E. so, is there a way to make sure the trigger only run for column A,B,C,D and not E (without using if for each other columns except E) ? thx very much

I have two MySQL servers running on two different machines in a local network. I am using PHP and accessing the databases via IP. No problems there. The databases have identical schemas. One of them was an import from the other machine. I want to run a single (ONE) query that updates the same table on each server simultaneously. I already have existing queries and do not want to update them extensively. Is there a simple way to achieve this? I'm looking to keep the databases in sync when an update occurs. I am also open to any other suggestions regarding other ways to keep the databases in sync.

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system and that raised alarms successfully. But now my DBA team is saying they cannot grant the **application DB user** read/write privileges on UTL_FILE in production. And OEM (*Oracle Enterprise Manager*) can't be used at application level. - What are my options? - How can I get an alert on table size? ***Reference:** UTL_FILE * > With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.