Tag Info

New answers tagged

1

The query select col1,col2 from mytable where col1 between a1 and b1 and col2 between a2 and b2 can be answered using index3 without accessing the table. If there is only index1 and index2 for example the following methods are possible: method I: 1) check for each row from mytable if col1 between a1 and b1 and col2 between a2 and b2 method II: ...


1

MyISAM didn't change between 5.5 and 5.6. It's not being developed, and it's virtually stagnant. Use FLUSH TABLES WITH READ LOCK and then it's safe to copy the MyISAM tables. This is mentioned in https://dev.mysql.com/doc/refman/5.6/en/backup-methods.html Other solutions: mysqldump --tab outputs tab-delimited data files instead of SQL, so it's much ...


0

What you are asking is not really an automatic feature in MySQL 5.6, I could be wrong. Notwithstanding, you can set it up manually. Given the following information Your datadir is /var/lib/mysql You data is in the Database mydb You have this InnoDB table Table Description CREATE TABLE mytable ( if int not null autop_increment, ... primary key ...


0

Yes, you can separate tables into individual files and spread those files across different disks. One example of documentation in MySQL (in this case for 5.7) is: https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html A partial quote says: "This mode is controlled by the innodb_file_per_table configuration option, and is the default in ...


0

The output is truncated when there are many running transactions. The size depends on your version. See "Standard InnoDB Monitor output is limited..." for your version on https://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html


2

Please keep in mind OPTIMIZE TABLE is DDL not DML. In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable; or CREATE TABLE mydb.mytable_new LIKE mydb.mytable; ALTER TABLE mydb.mytable_new DISABLE KEYS; INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable; ...


1

With large datasets, where data does not fit in RAM, you will warm your buffer pool -- and beyond. You will thrash the buffer pool. Your query does a full table scan. Assuming id is the PRIMARY KEY, you will be warming up the entire index tree (though not necessarily rows nor secondary indexes). But then you'll do the same for all your tables, and ...


2

I see one of the comments in the question mentions caching indexes please note that only one index will be cached in the buffer pool (most probably PRIMARY, but check EXPLAIN to be sure). You may need secondary indexes in the cache as well, so you have to run additional SELECTs It just so happens I have post from Feb 04, 2012 that lets you make those ...


0

You could join the label table multiple times under different aliases specifying in the where clause that entries for the 2nd join be not equal to the 1st. Entries for the 3rd be not equal to the 1st or 2nd etc. e.g. Say we have a table of people id name 1 Paul 2 James 3 Scott and a table of places they have visited id peopleid placename 1 1 Africa 2 1 ...


0

You cannot have a view that dynamically adjusts the number of columns returned. That's something you need to implement with client-logic. At least with all databases I know of (you don't mention which database system you use).


2

If the master crashes, then it doesn't really matter what you do as far as stopping the slave, as long as you observe the slave once the master is back online, to verify that it has successfully started reading and executing events from the master again. If you don't stop it, the slave should still be fine, and will sit and continually try to reconnect to ...


1

There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all. All of them creates a unique key/index, constraint md5_constraint unique (query_md5) has the same effect. Just pick the name you prefer for the unique key and feel free to use any of your queries. :)


1

When possible, you should run slave stop on the slave after you have stopped traffic to the master. That said, in most cases the salve will reconnect on its own as long as [a] there isn't an re-attempt limit, [b] the last slave read concluded properly and [c] the binlog on the master side hasn't been corrupted.


-1

Alternatively you may consider setting up replications from the main server to your dev machines. It would require to have a direct connection from your master to the slaves (the dev machines), but it doesn't need to be permanent. If you connect it once a month then the master will replicate only the changes, which should still be much faster. I doubt any ...


2

The replication filter you have in place Replicate_Wild_Do_Table: zo_dev_matrix.* is actually a little misleading. In the MySQL Documentation, the legal characters for wildcards are %, _ and \_. (If you want to interpret a literal underscore). The asterisk character is not listed. The above filter is actually looking for a table called zo_dev_matrix.*. ...


0

It appears that you have a successful connection from the slave to the master, and the SQL thread is running. But I notice this: Replicate_Wild_Do_Table: zo_dev_matrix.* This indicates you have configured a replication filter. It means that even though the relay logs contain all changes, only changes applying to tables in the zo_dev_matrix database will ...


1

You can take a backup from the live MySQL with Xtrabackup. Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions: Install the key: # apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A Add the source file: # cat /etc/apt/sources.list.d/percona.list deb http://repo.percona.com/apt precise ...


1

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root. Here is what you can do to start investigating: Go to the OS and do this: cd /var/lib/mysql df -h . If the root partition is 100% despite dropping 4GB, your ...


2

Users on MySQL are sharing the same context in terms of databases. So if you are creating a database for one user, it will be also visible to user2. You could solve this issue by prefixing your tables for each user, e.g. abc_item1 and setting correct privileges via grant for it -- so only one user is allowed to use this table. Another way would be to ...


0

You can`t run parallel statements in one session. You can manipulate data from different tables in 2 or more sessions and depending of isolation level you can manipulate 1 same table in different sessions.


0

You can open a separate SQL editor for each LOAD command and run it there. While one command is running you can switch to the next editor and run the next command and so on.


0

Use something like the comp command to find out the first place they're different. Then open the files up in a hex editor, like HxD for Windows, and look at where they're different. In particular, look for: CRLF (DOS, 0x0D0A) vs LF (*nix, 0x0A) line termination Unicode BOM (Byte Order Mark, UTF-8 being 0xEFBBBF) at the start of one but not the other ...


0

There can be various reasons why file hashes different: Line Endings differ; Spacing might differ (tabs vs spaces); Different layout for same data Encoding (of the file that data is saved in) Quotes (single vs double) Case sensitivity What I would do, is compare both files with diff tool. git diff can come in help here as it has ability to diff word by ...


0

BUFFER POOL INSTANCES The rule of thumb I usually use is based on a special program in in the Linux environment numactl --hardware When I run this, I get the following output sh-4.1# numactl --hardware available: 4 nodes (0-3) node 0 cpus: 0 1 2 3 node 0 size: 49151 MB node 0 free: 241 MB node 1 cpus: 4 5 6 7 node 1 size: 32768 MB node 1 free: 39 MB ...


0

You should use combination of innodb_buffer_pool_size in accourdance with innodb_buffer_pool_instances You should set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum). This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you ...


0

There is a reasonable explanation. There are occasions where VARCHAR storage can sometime produces inconsistent CHECKSUMs. I have discussed this before your post and after your post Mar 14, 2014 : Native checksum table says there's a difference, pt-table-checksum does not Nov 16, 2012 : Maatkit shows the MySQL replication error in one table, but ...


1

According to the MySQL Documentation on SELECT ... INTO The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database ...


0

If your db2.table1 and db2.table2 tables are joined by some key field and the name field in both databases is unique, you can use the following query to update your data: UPDATE `db1`.`table1` t SET t.`birthday` = (SELECT t2.`birthday` FROM `db2`.`table1` t1 JOIN `db2`.`table2` t2 ON (t1.`key` = t2.`key`) WHERE t.`name` = t1.`name`) This should work if ...


2

The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes: ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;


0

Percona has an extremely awesome wizard for initial server configuration files (I've used it on our own extremely busy production servers). It requires registration, but usage is free.


0

You are using four tables, i think this is better if you avoid sub-query you replace with LEFT Join. SELECT Client_ID, IF(rh.franchise_id IS NOT NULL,rh.franchise_id, cc.franchise_id) FROM SQL_AuditPro.clients c LEFT JOIN Archive.ret_history rh ON cc.client_id = rh.client_id AND ( ADD More conditions ) LEFT JOIN (second table) ...


1

For logging slow queries do like below, It will log all queries those are not using indexes as well as which are taking more than 1 sec slow_query_log = 1 slow_query_log_file = /var/log/mysqld_slow_query.log long_query_time = 1 log-queries-not-using-indexes For Error log log-error = ...



Top 50 recent answers are included