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

It's a common optimization to use UNION in these cases (at least when you must use a single query): select * from users where name='smith' /* using single index */ UNION select * from users where nick='smith'; /* using single index */ In my experience, I don't like to rely on index_merge (union) because the performance is usually not as good as doing the ...


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

There's a good chance the mysql crashed while trying to execute your query. First check the error log to see if it did. Common reason for mysql crash is memory exhaustion and tmp dir space depletion.


2

If you're using InnoDB and have a reasonably default configuration, tablespace data is in the ibdata files, you should have at least ibdata1 by default. You may want to read up about its configuration: https://dev.mysql.com/doc/refman/5.7/en/innodb-configuration.html Specifically splitting it so that each table gets its own ibdata file: ...


2

Your query is correct and it should return one row (5,6) DECLARE @table1 TABLE ( a INT, b INT ) DECLARE @table2 TABLE ( x INT, y INT ) DECLARE @table3 TABLE ( x INT, y INT ) INSERT INTO @table1 ( a, b ) VALUES ( 1, 2 ), ( 5, 6 ), ( 7, 8 ) INSERT INTO @table2 ( x, y ) VALUES ( 1, 0 ), ( 0, 0 ), ( 9, 5 ) ...


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

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


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

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.


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

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

If you are using Windows, you need not worry about named pipes at all. Why ? Years ago, MySQL for Windows had distributed three(3) different executables: mysqld.exe mysqld-nt.exe mysqld-max-nt.exe The two executeables mysqld-nt.exe and mysqld-max-nt.exe used the named pipes protocol. This is briefly mentioned in Chapter 23 Section 23.2 Page 353 ...


1

This is something you set up when starting the mysqld daemon (or service on Windows). You should (don't have Windows running) be able to see your startup options using the Task Manager (or failing that, use Process Explorer from here).


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


1

Every time you have perform something atomically you should use transactions. Please understand that (depending on the language/framework/ORM), MySQL may be in auto-commit mode, which means that, functionally speaking, if you are using InnoDB something like this: INSERT INTO innodb_table VALUES (1, 2, 3); gets converted internally into: START ...


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

You will have to find the my.ini, look for init_connect, and change it, remove it, or comment it out. Then, run net stop mysql net start mysql If the values keep getting reset anyway, you have to do the following: Create a file called C:\mysql_init.sql the the contents SET GLOBAL init_connect=''; Go back to my.ini and add this line ...


1

I guess you are referring to the InnoDB clustered index performance. If I'm not mistaken, the PK is chosen as the clustered index by default, so any other, secondary index containing the UUID column should not suffer from the same problem. Alternatively, I think you can create the table without declaring the PK at first, in which case MySQL will create an ...


1

What you're seeing is completely unacceptable, and developers should not be expected to work around it. You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup. Here's what you should have seen: mysql> insert into t1 values (uuid_short()); Query OK, 1 row ...



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