Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

insertion taking time in mysql 5.0.7

Alberto last edited by

Tables having 70-80 columns and 15-20 indexes.

Rows around 90+ lac

Taking time between 60 to 300 seconds in insertion while 30 columns are in a single query.

Insertion from the application one by one

Please suggest how to resolve this issue.
Reply Quote 0
1 Reply Last reply
E
esther last edited by

Fastest way if the data comes from a CSV file: LOAD DATA.

Next best: "batch" INSERTs -- Load 100 to 1000 rows at a time. (With 9 million rows, that will be thousands of batches, but that is OK.)

If the data can be pre-sorted, sort by the PRIMARY KEY and have the PK already included in the table definition.

Depending on various factors, it may be faster to drop (or not have) the secondary keys, load the data, then add the secondary keys in a single ALTER.

MySQL 5.0 was released in 2005; please consider upgrading. Still, my answer probably applies to all versions of MySQL and MariaDB and to MyISAM and InnoDB.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

E

Update from PHP 5 to PHP 7.0 - PHPmyAdmin will show two server logins: localhost / localhost:3306
SQL, Database Testing • php mysql phpmyadmin users • • esther

2

0
Votes

2
Posts

0
Views

B

There are two ways to connect to the mysql on the localhost. One is the connection via network @localhost:3306 and another is the connection through the filesocket like /tmp/mysql.sock. Filesocket connection is preferred due to the lower overhead and mysql library choose it if available when connection to the localhost requested. I have no clue why they are separated now. If you want to connect to the mysql only locally then networking can be disabled completely in the my.cnf so all connections are forced to be filesocket-based. That is also a bit more secure as far as no one can connect to the mysql over the network.
R

How to make the OBD table of an uncertain length SQL
Software Programming • mysql sql database • • rielyn

2

0
Votes

2
Posts

0
Views

O

I don't understand much either. I believe, however, that in the correct design, lines should be added rather than columns. And then you won't have a problem like that. But without knowledge of the subject area, it is difficult for me to recommend a specific structure.
Why does InnoDB corrupt tables upon recovery?
SQL, Database Testing • mysql innodb mysql 5.7 rec languageovery • • Mystic

2

0
Votes

2
Posts

0
Views

O

innodb_fast_shutdown controls the change buffer, not the redo log. No matter what value of innodb_fast_shutdown is, if a transaction wasn't committed at the time of shutdown it won't survive the restart. If a transaction was committed then whether it's going to persist depends on innodb_flush_log_at_trx_commit. If it's the default 1 the transaction will persist. If != 1, may not persist. Now, innodb_force_recovery. Here's what possible. When you run an UPDATE in a transaction, the new record is written to a page (in the buffer pool, the page becomes dirty). Later on, the data page can be flushed to disk even if the transaction isn't committed yet. When you have to use innodb_force_recovery that means you are ready to sacrifice data consistency (is it what you call a damaged table?) just to get the some data back. With innodb_force_recovery=6 InnoDB tries to salvage as much data as possible. It cannot complete the crash recovery process, but the new record is already written to the page - why not make it available to a user?
A

What is the usage of performance_schema in MySQL 5.5?
SQL, Database Testing • mysql performance sc languagehema • • Anderson

2

0
Votes

2
Posts

0
Views

C

All of your answers can be found by reading the documentation: What is the usage of this new schema..? The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. What are the advantages of using this schema..? The Performance Schema is a tool to help a DBA do performance tuning by taking real measurements instead of “wild guesses.” source Is there any overhead by enabling of this schema..? Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable. How can we monitor using this schema..? This page gives a basic overview of how to use the performance schema
J

NOWAIT equivalent in MySQL 5.7
SQL, Database Testing • query mysql 5.7 • • juvenalb

2

0
Votes

2
Posts

0
Views

C

Server-side SELECT statement timeouts allows: SET SESSION MAX_EXECUTION_TIME=2000; Not exactly the same, but close.
K

Write one or more triggers to manage the grade attribute of new Highschoolers
SQL, Database Testing • mysql • • Kadir

2

0
Votes

2
Posts

0
Views

E

As i mentioned in the comment. For your purpose you can use a BEFORE INSERT trigger DELIMITER $$ CREATE TRIGGER `gradeTrigger1` BEFORE INSERT ON highschooler FOR EACH ROW BEGIN CASE WHEN new.grade < 9 OR new.grade > 12 THEN SET new.grade = NULL; WHEN new.grade IS NULL THEN SET new.grade = 9; ELSE SET new.grade = NEW.grade; END CASE; END$$ DELIMITER ;
Aurora MySQL 5.7 randomly fails
SQL, Database Testing • amazon rds aurora • • jeanid

2

0
Votes

2
Posts

0
Views

N

Special thanks to @WilsonHauck. After 4 weeks of monitoring, Manually upgrading Aurora to the latest version solves the issue. There have been several bugfixes addressing unexpected restarts on 2.01.1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Updates.20Updates.html To manually upgrade your Aurora: Go to RDS - AWS Console Navigate to Clusters Select your cluster Click Actions >> Upgrade now
E

Selection of Nraz from mysql
Software Programming • php mysql • • emerson

2

0
Votes

2
Posts

0
Views

A

Try this:SELECT t1.*, COUNT(*) num FROM MY_TABLE t1 JOIN MY_TABLE t2 ON t1.CAT = t2.CAT AND t1.ID >= t2.ID GROUP BY t1.CAT, t1.ID HAVING COUNT(*) <= 8 ORDER BY CAT, ID
A

How can I disable utf8mb4 entirely on MySQL 5.5?
SQL, Database Testing • mysql replication mysql 5.5 utf 8 mysql 5.1 • • Anderson

2

0
Votes

2
Posts

0
Views

J

POLITICALLY CORRECT ANSWER You cannot do MySQL Replication from a New Master to an Old Slave I have written many posts about this: Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave? Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work? Dec 22, 2011 : What does the base64 BINLOG statements in mysqlbinlog output mean? Feb 04, 2011 : MySQL master binlog corruption In addition, there is the likelihood of dealing with the internal BINLOG magic number being in the wrong place and replication going senile. Here are my posts on the BINLOG magic number: Oct 28, 2012 : Binlog has bad magic number Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT Since there is nothing in the meantime you can do, please upgrade your Slave to MySQL 5.5 ALTERNATE PERSPECTIVE In all fairness, @ChristopherSchultz is correct in that you can implement it. I just yell and scream "RISK !!!" to be more cautious because of the BINLOG magic number's place in the MySQL Replication Universe.
F

Do Log_Slave_Updates in MySQL 5.7 fail the replication in Linux?
SQL, Database Testing • mysql replication innodb mysql 5.7 my.c languagenf • • fbio

2

0
Votes

2
Posts

0
Views

N

ASPECT #1 Please make sure ServerA and ServerB have log_slave_updates enabled. ASPECT #2 This setup will not break replication because every time a transaction is logged in the binary logs, the server_id of the transaction is written as well. Let's say server_id=10 for ServerA and server_id=20 for ServerB So, when you run this on ServerA INSERT INTO mytable (col) VALUES (1); The transaction will include server_id 10. When ServerB looks into its relay logs and sees server_id 10, it will compare that with its own server_id (20). Since there is no match, it is OK to execute. ServerB records that transaction in its binary logs. ServerA will pick that transaction up from its relay logs. When ServerA looks into its relay logs and sees server_id 10, it will compare that with its own server_id (10). Since there is a match, it refuses to execute. I discussed this before in my past answers Mar 27, 2012 : Master-Master Replication in MySQL Oct 15, 2012 : Will changing server_id on master break replication?
R

ola.hallengren - Alternative MySQL scripts!
SQL, Database Testing • mysql query performance optimization mysql workbenc languageh maintenanc languagee plan • • Rossere

2

0
Votes

2
Posts

0
Views

P

You might like to visit https://github.com/shlomi-noach/awesome-mysql which is a list of recommended community tools for MySQL. Specifically the kind of tools you want may be: common_schema - DBA's framework for MySQL, providing a function library, views library and QueryScript interpreter. sys - A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage. Percona Toolkit - a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually. openark kit - a set of utilities that solve everyday maintenance tasks, which may be complicated or time consuming to do by hand, written in Python.
E

How do you write SELECT for JSON?
Software Programming • mysql sql • • emran

2

0
Votes

2
Posts

0
Views

P

mysql> set @x='{"3":"Ivanovich","2":"nick_Ivan"}'; Query OK, 0 rows affected (0.06 sec) mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX(@x,'"2":"',-1),'"',1); +-------------------------------------------------------+ | SUBSTRING_INDEX(SUBSTRING_INDEX(@x,'"2":"',-1),'"',1) | +-------------------------------------------------------+ | nick_Ivan | +-------------------------------------------------------+ 1 row in set (0.06 sec)
O

How do you get the text off the OBD?
Software Programming • php mysql phpmyadmin • • Oba22

2

0
Votes

2
Posts

0
Views

A

For starters, get on to the:<?php try { $pdo = new PDO('mysql:host=Название хоста (у вас наверняка localhost);dbname=название БД','имя пользователя','пароль'); } catch (Exception $e) { echo $e->getMessage(); } ?> Then ask for a sample of data from the table:$stmt = $pdo->query('select * from text'); Next in the cycle (if you have a few lines of record) shall be taken as follows:if($stmt->rowCount() > 0){ while($res = $stmt->fetch(PDO::FETCH_BOTH)){ echo ''.$res['text'].''; } } If you'd like to take out one of the:$res= $stmt->fetch(); echo ''.$res['text'];.''; You can do it right in html, then just don't forget to put the php in. For example: <?php echo $res['text']; ?>
O

Verification of the presence of an OBD record in the mass (high)
Software Programming • php mysql liabilities • • Oba22

2

0
Votes

2
Posts

0
Views

S

SELECT is to be selected without all fields, but only key ones, such as: SELECT id, name... You're also in the cycle asking for removal from the base, and it's also slowing down. I'd be asking for disposal in one line, not more than 50 queries in one line, and I'd send for implementation.For$remove = mysql_query("DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"); $remove = mysql_query("DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"); $remove = mysql_query("DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"); $remove = mysql_query("DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"); You'll have one request, but it'll have a few distances.$str_delete = "DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"; $str_delete .= "DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"; $str_delete .= "DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'"; $str_delete .= "DELETE FROM `s_images` WHERE `filename` = '".$row['filename']."'";
K

Check if column UPDATE occurred
SQL, Database Testing • php mysql • • kalyn

2

0
Votes

2
Posts

0
Views

D

There is no such functionality in the mysql. If you want to trace the changes made to the data you have to collect historical data on your own. Say you create the trigger BEFORE UPDATE that insert all the data from the updated rows into the historical table. Later you can compare the present state of data with the historical states and trace all the changes been made.

insertion taking time in mysql 5.0.7

Suggested Topics

Update from PHP 5 to PHP 7.0 - PHPmyAdmin will show two server logins: localhost / localhost:3306 SQL, Database Testing • php mysql phpmyadmin users • • esther

How to make the OBD table of an uncertain length SQL Software Programming • mysql sql database • • rielyn

Why does InnoDB corrupt tables upon recovery? SQL, Database Testing • mysql innodb mysql 5.7 rec languageovery • • Mystic

What is the usage of performance_schema in MySQL 5.5? SQL, Database Testing • mysql performance sc languagehema • • Anderson

NOWAIT equivalent in MySQL 5.7 SQL, Database Testing • query mysql 5.7 • • juvenalb

Write one or more triggers to manage the grade attribute of new Highschoolers SQL, Database Testing • mysql • • Kadir

Aurora MySQL 5.7 randomly fails SQL, Database Testing • amazon rds aurora • • jeanid

Selection of Nraz from mysql Software Programming • php mysql • • emerson

How can I disable utf8mb4 entirely on MySQL 5.5? SQL, Database Testing • mysql replication mysql 5.5 utf 8 mysql 5.1 • • Anderson

Do Log_Slave_Updates in MySQL 5.7 fail the replication in Linux? SQL, Database Testing • mysql replication innodb mysql 5.7 my.c languagenf • • fbio

ola.hallengren - Alternative MySQL scripts! SQL, Database Testing • mysql query performance optimization mysql workbenc languageh maintenanc languagee plan • • Rossere

How do you write SELECT for JSON? Software Programming • mysql sql • • emran

How do you get the text off the OBD? Software Programming • php mysql phpmyadmin • • Oba22

Verification of the presence of an OBD record in the mass (high) Software Programming • php mysql liabilities • • Oba22

Check if column UPDATE occurred SQL, Database Testing • php mysql • • kalyn

Update from PHP 5 to PHP 7.0 - PHPmyAdmin will show two server logins: localhost / localhost:3306
SQL, Database Testing • php mysql phpmyadmin users • • esther

How to make the OBD table of an uncertain length SQL
Software Programming • mysql sql database • • rielyn

Why does InnoDB corrupt tables upon recovery?
SQL, Database Testing • mysql innodb mysql 5.7 rec languageovery • • Mystic

What is the usage of performance_schema in MySQL 5.5?
SQL, Database Testing • mysql performance sc languagehema • • Anderson

NOWAIT equivalent in MySQL 5.7
SQL, Database Testing • query mysql 5.7 • • juvenalb

Write one or more triggers to manage the grade attribute of new Highschoolers
SQL, Database Testing • mysql • • Kadir

Aurora MySQL 5.7 randomly fails
SQL, Database Testing • amazon rds aurora • • jeanid

Selection of Nraz from mysql
Software Programming • php mysql • • emerson

How can I disable utf8mb4 entirely on MySQL 5.5?
SQL, Database Testing • mysql replication mysql 5.5 utf 8 mysql 5.1 • • Anderson

Do Log_Slave_Updates in MySQL 5.7 fail the replication in Linux?
SQL, Database Testing • mysql replication innodb mysql 5.7 my.c languagenf • • fbio

ola.hallengren - Alternative MySQL scripts!
SQL, Database Testing • mysql query performance optimization mysql workbenc languageh maintenanc languagee plan • • Rossere

How do you write SELECT for JSON?
Software Programming • mysql sql • • emran

How do you get the text off the OBD?
Software Programming • php mysql phpmyadmin • • Oba22

Verification of the presence of an OBD record in the mass (high)
Software Programming • php mysql liabilities • • Oba22

Check if column UPDATE occurred
SQL, Database Testing • php mysql • • kalyn