Tag Info

Hot answers tagged

3

You need a compound unique index. Suppose your table is called photos. You can do this: CREATE TABLE photos_new LIKE photos; ALTER TABLE photos_new ADD UNIQUE INDEX pid_tid_index (pid,tid); INSERT IGNORE INTO photos_new SELECT * FROM photos; ALTER TABLE photos RENAME photos_old; ALTER TABLE photos_new RENAME photos; If it works out, then run DROP TABLE ...


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


2

The comment from @ChristianAmmer is the key to answering your question. Why ??? First look the the WHERE clause DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds ...


2

You want to use a TRANSLATIONS table, that looks something like: CREATE TABLE TRANSLATIONS ( translation_id INTEGER, language_id INTEGER, translation VARCHAR(255), PRIMARY KEY (translation_id, language_id) ); Then edit your current schema and/or code to reference the TRANSLATIONS table, rather than any hard-coded values (using ...


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


2

You need to have the SHOW VIEW privilege. I wrote about this Dec 2013 : Which are the minimum privileges required to get a backup of a MySQL database schema? In that post I show these minimum privileges for a mysqldump SELECT SHOW VIEW (If any database has Views) TRIGGER (If any table has one or more triggers) LOCK TABLES (If you use an explicit ...


2

Do you mean by "kill / stop the MySQL instance" the linux command kill -9 ...? Yes, this might lead to corrupt data. Which instead should not leave corrupt data is the build-in kill command of MySQL. See the lower part of the page, which states that it might take the thread some time to actually notice the presence of the kill flag. The part which states it ...


2

Question: Does this mean the query is using indexes? The Using intersect(FK_DataValues_Sites,FK_DataValues_Variables) means that the query is using two indexes on table datavalues with the Index Merge Intersection Access Algorithm and then (Using where) the rows are additionally checked against some other conditions. Question: Any other suggestions? (I ...


2

I took the liberty of editing your SQL so the clauses are on separate lines. I violated CodeReview group discipline by doing that. Sorry! My suggestion to you is to format your queries so the various clauses are clearly visible, as I did. This kind of formatting, in my experience, makes it easier to understand the logic of queries and to spot errors. ...


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


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

3 seconds is kind of a long time for mysql. I bet you have a lot of full-table scans going on that are using up CPU. I've gotten good mileage in decreasing CPU with just a little indexing. This is not going to be a short answer, but try this: Enable the slow query log (set global slow_query_log=on and set global ...


1

I will answer each the the three questions QUESTION #1 Since I'll probably move to mysql cluster, does it make sense to switch from InnoDB to NDB engine and use HASH indexes on "category" and "status" columns? ANSWER TO QUESTION #1 Hash indexes are for one-to-one lookups. Hash indexes are only available for the MEMORY Storage Engine (See my May 17, ...


1

Your Altered_Files design is absolutely fine. In fact I would award bonus points for separating the files per se from the hierarchy. @chillworld's suggestion is workable and very common but yours is better because of the NULL issues you mention. I can't think of any update which is better in one schema or the other. Change a single row - either the file ...


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


1

You need to change java code to something like (for simplicity sake error handling and closing statements omitted) try { conn = ..... conn.setAutoCommit(false); Statement s1 = conn.createStatement(); s1.executeUpdate(/*first insert */); s1.executeUpdate(/*second insert */); // etc conn.commit(); } Side notes. 1) it's much ...


1

Your query can be simplified to: SELECT COUNT(DISTINCT t.id) FROM cs AS t JOIN uploads AS u ON t.upload_id = u.id WHERE t.user_id = 1 AND t.upload_id IN (1,2,3) AND u.term > '' AND t.amount > 0 ; and also: SELECT COUNT(*) FROM cs AS t WHERE t.user_id = 1 AND t.upload_id IN (1,2,3) AND t.amount > 0 AND EXISTS ( SELECT ...


1

Okay, few notions about your procedure DECIMAL(12,12) means 12 digits, where ALL 12 are behind a floating point (so basically, 0.*). To see an example, execute SELECT CAST(23.45 AS DECIMAL(12,12)); and see what output does it give you. I suggest that you change DECIMAL definitions to DECIMAL(9,6) which means 9 digits, where 6 are after the point. I think ...


1

It's easier for the storage engine to load in smaller chunks. The reason is that a big data load fills up the InnoDB rollback segment, and that takes time to purge. For this reason, tools like pt-fifo-split exist. This helps to process a huge file in modestly-sized pieces, without copying the pieces to disk.


1

There is no solution for fan-in with traditional MySQL replication. Another option is Tungsten Replicator. Here are a couple of posts on how it supports fan-in replication: http://narmitag.wordpress.com/2012/09/27/setting-up-fan-in-using-tungsten-replicator/ https://code.google.com/p/tungsten-replicator/wiki/TRCMultiMasterInstallation Another option is ...


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

THIS IS THE QUERY YOU NEED SET @GivenDate = '2014-04-15'; SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime FROM airport_taxi WHERE date=@GivenDate; SET @nexttime = 0; SELECT TIME(FROM_UNIXTIME(StartTime)) "start", TIME(FROM_UNIXTIME(EndTime)) "end", FLOOR((EndTime - StartTime)/60) diff_min FROM (SELECT (@prevtime:=@nexttime) ...


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

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

I like this presentation by Giuseppe Bianchi. Starting on page 5 it contains a desctiption of the TLS protocol - segment size, header size, HMAC overhead. As for the handshake, the impact on replication should be negligible. It will only occur on connection, and there may be a key exchange going on every hour, depending on the configuration. As for the ...


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


1

What you seek is a table like the one below, from which you could query: SELECT * FROM departures WHERE route_id = 'SOME_ROUTE' -- This is the code to a route, encompassing from/where AND departure_date = @some_day AND departure_time_min <= @target_time AND departure_time_max >= @target_time I present a solution where such table is ...


1

Let's say you made the table like this CREATE TABLE TrainEvents ( id INT NOT NULL AUTO_INCREMENT, station_arr_id INT NOT NULL, station_dep_id INT NOT NULL, arr_dt DATETIME NOT NULL, dep_dt DATETIME NOT NULL, ... PRIMARY KEY (id), KEY station_arr_index (station_arr_id,arr_dt), KEY station_dep_index (station_dep_id,dep_dt) ...


1

Since you control my.cnf on EC2, you have two options, neither of which require restarting mysql OPTION #1 : Change old_password Protocol for EC2 Stop replication to the RDS Slave As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0; Start replication to the RDS Slave OPTION #2 : Lower the Security for EC2 Stop replication to the RDS Slave ...



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