Tag Info

New answers tagged

0

for the query monitoring you have two solutions from MySQL level: solution 1: create log file for the query by editing my.ini or my.cnf slow_query_log_file = "C:/slowquery.log" log = log_file_name solution 2: Create log tables: Slow queries: CREATE TABLE `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ...


1

Suppose your date range is 2014-12-08 to 2014-12-11 This would be the Query you need SELECT B.*,A.quantity FROM ( SELECT AA.id,SUM(BB.quantity) quantity FROM ( SELECT id FROM orders WHERE date >= '2014-12-08' AND date <= '2014-12-11' ) AA INNER JOIN orderdetails BB ON AA.id = BB.order_id GROUP BY AA.id ) A ...


0

This should work, takes maximum use of your date index and minimizes possible join anomalies: SELECT (SELECT SUM(orders.amount) FROM orders WHERE orders.date >= [BEGIN DATE] and orders.date < [END DATE) amount, (SELECT SUM(od.quantitiy) FROM ordersdetails od INNER JOIN orders o ON od.id = o.id ...


1

From the output the two inserts are doing gap locking on the index index_shipments_on_ci_ai_tn_sti. Without knowing the table definition, I think the index is a UNIQUE index. Here is a decent blog explaining the reason UNIQUE constraints can cause deadlocks on concurrent inserts. Snippet here: Mysql innodb engine performs row locking on inserts. If ...


0

The problem was related to an index product_discount pd2 USE INDEX (ORDERBY) and product_special ps USE INDEX (ORDERBY) after removing the USE INDEX (ORDERBY) everything works perfect. I am not sure why it did not worked with the index but is working without.


0

Database Indexes are a major benefit to performance of queries. I work almost exclusively with Microsoft SQL Server, but the principles apply on pretty much all flavors of SQL servers. Because the indexes are smaller than the overall table and have the needed value to be searched in a sorted order, creating indexes (to a reasonable degree) is the quickest ...


0

As you can see, the process that takes most time is "query end". There is an interlocking problem when multiple threads want to write the file at the same time, this way the log will be flushed every second: innodb_flush_log_at_trx_commit = 0 in the /etc/my.cnf file Also, you should try Optimize InnoDB as explained above for future query improvement.


0

Try to turn off Query Cache, which might cause extra lock.


1

You should use Percona Toolkit to backup database to prevent from blocking database: sudo innobackupex --user=root --password=rootPASSWORD --host localhost /tmp/ sudo innobackupex --apply-log --use-memory=2G /tmp/$TIMESTAMP/ You need enough disk space in /tmp. Once you finish it, you can copy entire directory to another server. In xtrabackup_binlog_info, ...


1

You need to use Percona XtraBackup tool. It works like a charm for huge datasets and doesn't interrupt MySQL operations. http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/creating_a_backup_ibk.html There are some tricks but it's worth it.


1

There is a bug report on this: mysqldump: Couldn't execute 'show table status': SELECT command denied to user. Surprisingly, it's not a bug at all. here is why: customer_cohort_paid might be a view. Whatever user created the view is not listed anymore in mysql.user. What this does is make such a view incapable of being dumped. You could do one of two(2) ...


1

try the following command this one since the database size is not small: mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz


1

You must use --single-transaction option mysqldump -uroot -p --single-transaction databasename > dump.sql It allows for point-in-time snapshot of data. Once mysqldump starts, all the InnoDB tables will be frozen in time. Suppose you start the mysqldump at 2:30 PM and it finishes at 3:00 PM. All the InnoDB tables dumped will be from 2:30 PM. All other ...


3

The reason why the index on search_id is not even considered is because the two tables - and therefore the two search_id columns - have different character sets. One has CHARSET = latin1 and the other CHARSET = utf8. The types are the same varchar(100) but charsets differ and that matters. Columns that are used in joins or comparisons should have identical ...


2

You are trying to execute a semijoin. This is a very well know issue with the MySQL optimizer before MySQL 5.6. The only way MySQL knows how to execute it is to perform a full table scan on the left table and execute the inner query once per row, thus it is unable to use the index. You have several alternatives: Migrate to 5.6 (or MariaDB 5.5): it will be ...


0

ALTER TABLE `blog` CHANGE `read-more` `read_more` VARCHAR(255) NOT NULL; Above mentioned query is correct and there is no need to use "column" keyword and quotes around table and column name if you are using mysql database


2

Yes, creating a PK on a big table takes time. When you don't have PK (more precisely a clustered index - the two is the same in MySQL using InnoDB) the table is called heap and the DBMS adds a 'row identifier' to each row to track them. This ID is used to identify records in indexes and other places. When you have a clustered index (PK in MySQL), the PK ...


1

DISKSPACE FOR EVERYTHING INNODB SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace FROM information_schema.tables WHERE engine='InnoDB'; DISKSPACE FOR DATABASE mydb BY TABLE SELECT IFNULL(tbl,'Total') table_name, FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size FROM ( SELECT table_name ...


0

Rolando's answer worked for me with some additions. I had the same problem, with these 5 tables showing via SHOW TABLES, but SELECT or other operations on the table resulted in table not found. To resolve the issue, using Rolando's answer, I needed to: DROP TABLE <tablename> -- all 5 tables In the file system, delete the remaining .ibd files (the ...



Top 50 recent answers are included