Tag Info

Hot answers tagged

5

Consider that MySQL has a 31MB source code download. PostgreSQL has a 16MB source code download. SQLite has a 1.45MB source code download - please at least start by comparing like with like - comparing 300K with 600 MB isn't fair! While it's true that SQLite can be made smaller, it's also possible to do the same with MySQL - there is an embedded version ...


3

You don't need self-joining or subqueries at all. This query does the trick: select id from your_table t group by id having sum(name = 'John') + sum(name = 'Jill') = 2 and count(*) = 2; see it working live in an sqlfiddle name = 'whatever' in the sum() function returns 1 or 0, true or false.


3

There are several aspects of your query. The first is that mysql by default will give you a random answer if there are several possible answers to a group by question. SQL92 demanded that all non aggregated columns in the select clause must be part of the group by clause. SQL99 loosened this restriction and requires that all non aggregated columns are ...


3

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table. EXAMPLE Suppose the table is called mydb.mytable. Do it like this USE mydb CREATE TABLE mytable_new LIKE mytable; ALTER TABLE mytable RENAME mytable_old; ALTER TABLE mytable_new RENAME ...


2

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. ...


2

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way. If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with SET sql_log_bin = 0; The underlying bug is probably this ...


2

By default ''@localhost, and other bunch of accounts exist in a freshly installed MySQL instance. If you execute SELECT user() you may see something like this: mysql> SELECT user(); +--------------------+ | user() | +--------------------+ | user-one@localhost | +--------------------+ 1 row in set (0.00 sec) That user doesn't really exist, ...


2

ISSUE #1 : Upgrade Path Your grant tables can get screwed up because you are leaping two versions instead of one. I just helped someone a week ago because they did just that (MySQL service stops after trying to grant privileges to a user). The solution for that question was to manually fix the mysql.user. Rather than going through that heavy-handed route, ...


2

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change ...


2

This is likely to be a many to many relationship. create table drivers ( driver_id int primary key, ... ); create table trucks ( truck_id int primary key, ... ); create table drivers_trucks ( driver_id int references drivers(driver_id), truck_id int references trucks(truck_id), primary key (driver_id, truck_id) );


2

An example from the manual: First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this: CREATE TABLE test_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) ...


2

Following the famous article "fixing ORDER BY rand()", and in particular the multiple-selection via union, we can write this: ( SELECT `p1`.*, `users`.`username`, `users`.`displayname` FROM pics p1 JOIN (SELECT ceil(rand() * (SELECT max(id) FROM pics)) AS id ) AS p2 ...


1

Use a before insert trigger to set the new values back to the old ones? Or, even better, set it to do nothing at all - as per below. As Eric Raymond pointed out in his (good) book "The Art of Unix Programming", the fastest code is that which doesn't have to run at all!. (Note - don't have a running system to test - but I think the underlying concept is ...


1

I would use a before insert trigger: CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'You cannot insert rows to this table.'; END;


1

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used. If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that ...


1

EXPLAIN will get the execution plan for a query ANALYZE TABLE recalculates the index statistics. So just run ANALYZE TABLE `pics`; Give it a Try !!!


1

Since we don't have the hardware, of course, I can't benchmark it While you can't simulate the exact hardware you might be able to get some estimates by comparing simpler local hardware with similar relative differences. If you are upgrading from spinning rust in a similar RAID config then you could benchmark a single traditional drive against a single ...


1

I found the error to have been my varchar CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar which i changed to CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar(255) and worked.


1

I think what you're looking is simply to use INSERT IGNORE. Forget about steps 1 and 2, just insert and ignore :) If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and ...


1

Swap the foreign key so that the Drivers table has id_truck. This way multiple drivers can be associated with the same truck.


1

Install percona-toolkit. Then you can use pt-online-schema-change. I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, ...


1

Convert the first value using user variables to load the True/False values. Then, compare it to the value 'True' PROPOSED SOLUTION LOAD DATA LOCAL INFILE 'C:/bin/input.txt' INTO TABLE n1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' (@var1,c2) SET c1 = (@var1 = 'True'); SAMPLE DATA C:\bin>dir Volume in drive C is TI10665200H ...


1

Normally in OLTP databases, transactions are so quick that we need not to place explicit transactions.Though internally MySQL take care of this and place implicit transaction. But if you think your scripts are long and it will take time to execute and if unrealistic/unwanted results can occur if failed in middle then you must apply explicit transactions.


1

myisamchck did its best job, so that may be the maximum what you can get out of mytable.MYD. The structure of MyISAM record is pretty simple, integers are stored in packed format, strings are prefixed with their length(1 or 2 bytes). So you can write a C program with pre-defined pattern that would try to fetch records of out the MYD file. This approached ...


1

Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data. In most cases, you ...


1

tagName column in table cardTags is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).


1

Your architecture is not right for asynchronous replication. I strongly recommend against runing a Master-Master standard replication in a fully automated system, as you will run into precisely the issue you are describing. While you may not be writing to both servers at the same time, the fact that you are replicating in a non-syncronous way means that you ...


1

This is not a hack. It is the intention of them: Use auto_increment_increment and auto_increment_offset http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_auto_increment_increment


1

Unless your WordPress site is huge, you are unlikely to see much performance improvement in switching to TokuDB, if any at all. TokuDB's 20x performance advantage is for an indexed insertion workload, where the secondary indexes do not fit in memory. You will be using less space on disk with TokuDB's compression.


1

Looking at your comments, I can see that the problem is not the JOINs, but the LIKE '%term%' operators. There are several options here, but assuming you are using MyISAM for your tables, or InnoDB and a MySQL version equal or newer than 5.6, you may use FULLTEXT indexes. MySQL implementation is not perfect, but it will work way better than using '%LIKE%'. ...



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