Tag Info

Hot answers tagged

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 ...


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 ...


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 ...


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

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

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 ...



Only top voted, non community-wiki answers of a minimum length are eligible