Tag Info

New answers tagged

0

When you run a query in MySQL Workbench you get actually 2 times in the action output window. The headeing also shows what they are for: There's the execution time on the server (which is probably your ~16s) and the, usually much larger, fetch time, to transport the data from the server to the client. With millions of records that can easily take minutes ...


0

The 16.04 seconds is a timer on the client that measures the amount of time that passed between the call and the execution of the query (if you want to be more specific than that, the wall clock time between calling start_timer() and mysql_end_timer(), something that can lead to hilarious results like this one I got). The reason that you think that it took ...


0

You should look here and here. It's better for permanent settings like you want to set up your my.cnf file to do what you want by default - then you don't run the risk of forgetting to run your SET statements on startup. You might also want to peruse the docco (I've pointed you to 5.6, but you can choose based on your own server version).


2

From the manual here, you could try experimenting with your my.cnf settings - I know that I had this problem in the past and I eventually got around this by using [client] setting. # added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html # to allow for abrowse to load data! loose-local-infile = 1 local-infile = 1 Now, it was either ...


2

You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the Entity-Attribute-Value model. Check out the writings of Joe Celko or Bill Karwin on this issue. It's also called the OTLT (One True Lookup Table) or MUCK (Massively Unified Code Key - there's a reason that particular acronym was coined!). You would ...


0

Queries with WHERE column IN (SELECT subquery) may not be best optimized in various version of MySQL. The usual solution is to rewrite using joins and of course add appropriate indexes. The query can be rewritten equivalently as: SELECT u.* FROM users AS u JOIN ( SELECT selector FROM users AS u2 WHERE u2.key = 'username' AND u2.value = ...


0

Albert - could you in future please format your code something like this? It makes things much easier for those of us who are trying to help you :-). Use the {} code-formatting widget at the top of the question entry panel. You can also quote text (double-apostrophe ") and put in a link (the chain symbol) and do lists and B Bold &c... CREATE TABLE IF ...


0

You have to join the user table to the rank table twice SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor FROM user u INNER JOIN rank r1 ON u.rank_id = r1.id INNER JOIN rank r2 ON u.supervisor_id = r2.id WHERE u.id = 4; or SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor FROM user u INNER JOIN rank r1 ON u.rank_id = r1.id INNER ...


1

Answer by Mat provided in a comment to the question: The second link isn't documentation. It's a feature request. It's someone requesting that bitmap indexes be added to MySQL. It's not something that exists, it will potentially exist in future versions.


0

You could try my solution to this issue - MySQLDumpSplitter. It takes a dump file and splits it into individual tables, which you may find convenient. My email is there should it not conform to your needs - I could take a look at any issues you may have in an effort to make the tool better. Disclaimer: I wrote it, and as you'll see from the Readme, it works ...


0

Usually FULLTEXT searches need more than a single letter, there is actually a setting that prevents them from being used if less than X number of characters are used. ft_min_word_length: The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable My guess is that you are not ...


0

You create database mydatabase and then you grant on database.* The correct is: grant all privileges on mydatabase.* to 'host'@'%';


0

I'm no expert on Mac OS X, but on Linux, it's relatively easy to have more than one install running side by side. I follow a procedure very much based on the one here. Download the source code. Gunzip and untar it. As a sister directory to the unzipped and untarred one, create a directory (I call it "sandbox"). From within sandbox, run "cmake ../", then ...


1

I would code as follows: SELECT (some values) FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND; DELETE FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL ...


0

Try this I believe the first part already evaluates to 00:00:00. So I just added the seconds in. WHERE m_dt BETWEEN (CURDATE() - INTERVAL 60 DAY) AND (DATE_ADD((CURDATE() - INTERVAL 60 DAY),INTERVAL 86399 SECOND))


0

It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.


0

Check out this post by Peter Zaitsev. Use the VSZ column of ps -aux. You can also use these (mysqltuner or tuning-primer) which will give you memory usage (run them with --help first). Interestingly, the performance schema provides a way of doing this - I'm not sure if it's available in 5.5 - will check. The performance schema is really advancing in leaps ...


1

Please note the difference between the information_schema and performance_schema databases INFORMATION_SCHEMA The information_schema database is an inventory of all objects within the MySQL instance Such objects include: databases tables columns constraints indexes (called statistics) processlist locks I wrote a nice post about this 3 years ago : How ...


0

Oops, as often, the answer was in the question. Must've been pretty tired, because as I said : Anyway, the important part is : datadir=/var/lib/mysql chroot=/var/lib/mysql But, if I wanna be consistent with myself, chroot must be /var/chroot/mysql. So the correct setting is : datadir=/var/lib/mysql chroot=/var/chroot/mysql And indeed ...


-1

I think, it's better to use PIVOT. the below link shows how it works : http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx


1

InnoDB InnoDB can access multiple cores, but don't expect it to be that way out of the box. InnoDB must be configured to do so. Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian Sep 20, 2011 : Multi cores and MySQL Performance Sep 12, 2011 : Possible to make MySQL use more than one core? May 26, 2011 : About single threaded versus ...


2

MySQL Server will only execute each query in a single foreground thread, there is no support for multi-thread execution at SQL side. It also executes maintenance operations like ALTER TABLEs that can rebuild a whole table in a single thread. However, engines like InnoDB, specially in recent versions, are able to perform its background threads concurrently, ...


1

You should look at this. Mark Leith is a senior MySQL dev. manager. It's a way (MySQL are trying to get better...) that you can track some metrics - not per query like you want, but data access by table. Install the sys schema in your 5.6 instance and see what happens. Oh - yes - you were asking about InnodDB in 5.6 - you will have to enable ...


1

Innodb_log_writes monitor only writes to the innodb transaction log. InnoDB also writes to the double write buffer, data (aka primary key) and secondary indexes on the tablespace, change buffer, undo space, ... Some of these can be buffered on memory, at least for some amount of time. Additionally, MySQL may write to the mysql binary, general or slow log, ...


2

What you have seems fine. I would add the following Run this on the Master. SET GLOBAL innodb_fast_shutdown = 0; SET GLOBAL sync_binlog = 1; SET GLOBAL sync_master_info = 1; This will cause everything that has been uncommitted to be committed on shutdown. Then, it flushes the binlogs to disk. On the Slaves, run this SET GLOBAL innodb_fast_shutdown = ...


1

With your initial feedback (you are using innodb), I can tell you that your innodb_buffer_pool_size is too small (2M), so most of your queries may be using disk instead of memory. As a rule of the thumb, for a dedicated server, the usual recommendation is reserving between 60-85% of the available memory for the innodb buffer. Increase it by setting it in ...


0

Everything you have done is correct in order to drastically defragment innodb tablespaces (there are other ways, but exporting, deleting files and importing certainly works). Trying to optimize the tables (which, by the way, the correct way to do it for innodb is running ALTER TABLE mytable ENGINE=InnoDB- OPTIMIZE table just calls this, and REPAIR table ...


0

Alter table blog change read-more read_more varchar(255) not null Reference: click


0

Do a "which mysql" and/or "which mysqld". These will tell you if your mysql executables are on your path or not. If they are not on your $PATH, go to the root directory and issue a "find . -name "mysql*" | more" to see if you do really have these files on your system. If you do have them, point your $PATH to them in your .profile/.bashrc.


1

It looks like 'page swapping' is the problem here. When you deal with one table at a time, its indexes and parts of the table are loaded into the RAM. If you keep dealing with the same table, it will be fast as the table already exists in the ram. On the other hand, if you keep changing the table that you are dealing with, then the data in RAM has to be ...


0

The MySQL manual explains how to reset the root password. The steps are basically the same for Windows and *nix: Stop/kill the MySQL Server process Create a text file, e.g. ~/mysql-init, containing the following: UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; FLUSH PRIVILEGES; Start MySQL using the --init-file option, using ...


0

Try ordering the table by start_IP ranges then put a clustered index into the table. The idea is you want to have your query only search the relevant uster for the match instead of doing a table scan. Since I assume the IP start < IP end you can use this knowledge to omit those ranges that fall above below the IP your searching for.


1

When it comes to capacity, all your constraints start with Amazon RDS, not MySQL. Why ? I have posts about MySQL in Amazon RDS Aug 02, 2012 : Local database vs Amazon RDS Oct 12, 2012 : When should I think about upgrading our RDS MySQL instance based on memory usage? Feb 26, 2013 : MySQL optimization Your main bottlenecks for RDS are Transaction Log ...


1

The server core is similar (identical?) on the GA and Standard Edition (SE)/Entreprise Edition (EE). What you're getting with SE/EE is stuff like (from here) MySQL Partitioning MySQL Enterprise Backup MySQL Enterprise Monitor MySQL Enterprise HA MySQL Enterprise Scalability MySQL Enterprise Security MySQL Enterprise Audit You also get support and some ...


2

In MySQL 5.5.16 and later, you can use RESET SLAVE ALL to do all that RESET SLAVE does and reset the connection parameters from the memory, this way it doesn't require a mysqld restart.



Top 50 recent answers are included