So if the table is round, can I split it to sectors?

|
madno 2019-12-27 10:09:50
Ok
piterden 2019-12-27 10:10:35
I can suggest one to you if you need
MasterZiv 2019-12-27 10:14:42
madno 2019-12-27 09:30:41
How to make updates faster in a table

Well, it seems like you try using the DB like an Excel table,
this will not do.
Normalize, re-desing.

These
parameters=’…20kb char string…’
scan_logs=”…20kb char string…’
are just not the way relational DBs should be used.

MasterZiv 2019-12-27 10:16:36
madno 2019-12-27 10:02:24
Two things i want to know
1. Any work around for the lock-mode X locks rec but not gap waiting (what is the solution for this)
2. How to make the MySQL write optimised

2 – just extend buffer pool.
1 — I don’t understand.

MasterZiv 2019-12-27 10:17:29
madno 2019-12-27 10:02:24
Two things i want to know
1. Any work around for the lock-mode X locks rec but not gap waiting (what is the solution for this)
2. How to make the MySQL write optimised

A Record will be locked because this is UPDATE, it can’t go without locking because of ACID.

MasterZiv 2019-12-27 10:21:40
madno 2019-12-27 09:54:57
We use PK for updates as u can see in the query

And again the problem is you insert a small empty row, and then you start stuffing things, BIG things, in the same narrow , small row, instead of inserting new rows, this is bad, the index structure has to be reorganized and the row has to be moved physically into a new place in the index tree — this is all slow.

madno 2019-12-27 10:23:20
MasterZiv 2019-12-27 10:21:40
And again the problem is you insert a small empty row, and then you start stuffing things, BIG things, in the same narrow , small row, instead of inserting new rows, this is bad, the index structure has to be reorganized and the row has to be moved physically into a new place in the index tree — this is all slow.

Woh..i never thought that before

madno 2019-12-27 10:23:50
(bow)
piterden 2019-12-27 10:24:33
I know one amazing DB architector…
madno 2019-12-27 10:25:54
piterden 2019-12-27 10:24:33
I know one amazing DB architector…

Thanks @Piterden ..we are a small team..i am not sure we can afford a DB consultant right now

piterden 2019-12-27 10:32:03
If you would calculate it, you definitely afford
madno 2019-12-27 10:34:33
Thanks @MasterZiv @Piterden for your help
MasterZiv 2019-12-27 10:37:20
madno 2019-12-27 10:23:20
Woh..i never thought that before

OK,
and just imagine that you need to check if you already processed the host 172.24.110.17 in parameters….

piterden 2019-12-27 10:49:43
172.24.110.0/24 === 172.24.110.1-254
OmsaiV 2019-12-28 12:57:41
I want joints in sql
OmsaiV 2019-12-28 12:57:50
Easy way
piterden 2019-12-28 12:58:29
Why do you need them?
OmsaiV 2019-12-28 13:00:00
I am using hibernate querys
piterden 2019-12-28 13:00:25
What do you mean? Sorry
OmsaiV 2019-12-28 13:01:14
Sql jointes same using hibernate
piterden 2019-12-28 13:01:45
But why do you need hibernate?
OmsaiV 2019-12-28 13:02:27
Iam learning sql also bro
piterden 2019-12-28 13:03:29
So what is hibernate? Can you explain?
OmsaiV 2019-12-28 13:03:57
Hibernet is java framework bro
piterden 2019-12-28 13:05:45
But joins are used by any software using relational DB. SQL is essentially joins
OmsaiV 2019-12-28 13:06:35
Yes
piterden 2019-12-28 13:08:28
So what is your question?
OmsaiV 2019-12-28 13:09:20
Hello I want suggestions
OmsaiV 2019-12-28 13:09:50
Sql jointes
piterden 2019-12-28 13:10:10
https://www.google.com/search?q=sql+joins&oq=sql+joins&aqs=chrome..69i57j0l7.3497j1j4&sourceid=chrome&ie=UTF-8

sql joins – Google SearchGoogle
OmsaiV 2019-12-28 13:12:32
Thanks bro
Manogna 2019-12-30 09:27:23
What is the difference between having and where
MasterZiv 2019-12-30 09:39:05
Manogna 2019-12-30 09:27:23
What is the difference between having and where

They are quite similar except one thing: HAVING is applied logically after GROUP BY, when all aggregates have been calculated already and that is why only in HAVING you can place filtering expressions which are based on aggregates.

2019-12-30 12:02:45
Hi all
TheQuotidian 2019-12-30 12:07:34
madno 2019-12-27 09:33:49
CREATE TABLE t_discover_agent_master_scan (
id int(11) NOT NULL AUTO_INCREMENT,
scan_id varchar(100) DEFAULT NULL,
name text NOT NULL,
status text,
parameters longtext,
archive bit(1) DEFAULT NULL,
is_completed bit(1) DEFAULT NULL,
is_aborted bit(1) DEFAULT NULL,
start_time bigint(20) DEFAULT NULL,
end_time bigint(20) DEFAULT NULL,
total_time bigint(20) DEFAULT NULL,
scan_logs longtext,
ipaddress_to_credential_id_map longtext,
run_by int(11) DEFAULT NULL,
group_probe_id int(11) DEFAULT NULL,
client_id int(11) DEFAULT NULL,
credential_id int(11) DEFAULT NULL,
location_id int(11) DEFAULT NULL,
unique_id varchar(100) DEFAULT NULL,
scheduled_scan_id int(11) DEFAULT NULL,
is_email_sent bit(1) NOT NULL,
da_ip_address varchar(100) DEFAULT NULL,
exclude_ip_address longtext,
ports longtext,
certificate_directory text,
registry_path text,
software_url text,
install_arguments text,
audit_id int(11) DEFAULT NULL,
blueprint_mssql text,
is_create_disc_asset bit(1) DEFAULT NULL,
is_export_data bit(1) DEFAULT NULL,
mssql_data_mapping_parameters text,
mssql_query text,
is_quick_scan bit(1) DEFAULT b’0′,
is_host_name_scan bit(1) DEFAULT b’0′,
PRIMARY KEY (`id`),
KEY FK51B9EBD5134638C9 (`audit_id`),
KEY FK51B9EBD5C4C3C220 (`run_by`),
KEY FK51B9EBD564392E85 (`group_probe_id`),
KEY FK51B9EBD5A67A35A0 (`client_id`),
KEY FK51B9EBD57F0A9464 (`location_id`),
KEY FK51B9EBD574ABAEA4 (`credential_id`),
CONSTRAINT FK51B9EBD5134638C9 FOREIGN KEY (`audit_id`) REFERENCES t_cmdb_audits (`id`),
CONSTRAINT FK51B9EBD564392E85 FOREIGN KEY (`group_probe_id`) REFERENCES t_discover_agent_group_probe (`id`),
CONSTRAINT FK51B9EBD574ABAEA4 FOREIGN KEY (`credential_id`) REFERENCES t_discover_agent_credential (`id`),
CONSTRAINT FK51B9EBD57F0A9464 FOREIGN KEY (`location_id`) REFERENCES t_locations (`id`),
CONSTRAINT FK51B9EBD5A67A35A0 FOREIGN KEY (`client_id`) REFERENCES t_discover_agent_client (`id`),
CONSTRAINT FK51B9EBD5C4C3C220 FOREIGN KEY (`run_by`) REFERENCES t_core_users (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=753 DEFAULT CHARSET=latin1

Break the dataset into smaller tables. Refactor.

madno 2019-12-30 12:08:30
TheQuotidian 2019-12-30 12:07:34
Break the dataset into smaller tables. Refactor.

Thanx ..we are planning to normalise that table

TheQuotidian 2019-12-30 12:11:39
I’m not Privy to the data but from the looks of the column names it appears the table doesn’t have transitive dependency. So rather than normalizing maybe you can split it up, segregating columns carefully so that none of the smaller tables have too much skew, especially on your future join columns
madno 2019-12-30 12:32:47
Can u elaborate on how to split the table
piterden 2019-12-30 12:34:09
TheQuotidian 2019-12-30 12:11:39
I’m not Privy to the data but from the looks of the column names it appears the table doesn’t have transitive dependency. So rather than normalizing maybe you can split it up, segregating columns carefully so that none of the smaller tables have too much skew, especially on your future join columns

Beautiful words

notpeepeepoopoo 2019-12-30 12:36:01
mysql_en-1167.jpg
How to do this?
notpeepeepoopoo 2019-12-30 12:36:23
notpeepeepoopoo 2019-12-30 12:36:01
How to do this?

I cant understand how to link the two tables 😔

piterden 2019-12-30 12:36:52
TheQuotidian 2019-12-30 12:11:39
I’m not Privy to the data but from the looks of the column names it appears the table doesn’t have transitive dependency. So rather than normalizing maybe you can split it up, segregating columns carefully so that none of the smaller tables have too much skew, especially on your future join columns

So if the table is round, can I split it to sectors?

piterden 2019-12-30 12:38:58
notpeepeepoopoo 2019-12-30 12:36:23
I cant understand how to link the two tables 😔

Track links to a genre through genreid column

notpeepeepoopoo 2019-12-30 12:39:21
Should i perform a natural join?
piterden 2019-12-30 12:40:05
There can be many tracks of one genre, but can’t be many genres of one track
piterden 2019-12-30 12:40:38
notpeepeepoopoo 2019-12-30 12:39:21
Should i perform a natural join?

Join is the method of selection of data

piterden 2019-12-30 12:41:06
Do you want to select it?
piterden 2019-12-30 12:43:22
So if you want, you should decide what data do you want to select
piterden 2019-12-30 12:44:59
Do you need or not a natural join depends on answers on questions above
notpeepeepoopoo 2019-12-30 12:46:45
mysql_en-1177.jpg

|