Hot answers tagged mysql
7
The recent 5.6 version has added this feature.
See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer
Typical Usage:
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET ...
4
You might want to consider moving the data outside the database if:
You are having problems with backup durations
It causes disk or network throughput problems on your database server
You have a requirement to access the data without using the database
In general, I'd suggest keeping it in the database unless you can come up with a good reason not to.
...
4
This will work fine. The only caveat is that if you, in the future, create a view on the master with the same view name, in the same schema, replication will stop due to the conflict.
Another potentially useful thing you can do -- quite safely -- on a slave server is adding indexes to base tables to help your reporting queries, even if the same index ...
4
The MySQL optimizer cannot optimize expressions in this format:
WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f))
It's not a matter of getting the indexes right -- it appears that it's just not implemented.
The optimizer does not understand that this is equivalent to...
WHERE (col_1,col_2) IN ((a,b))
OR (col_1,col_2) IN ((c,d))
OR (col_1,col_2) IN ...
3
@NathanJolly's comment about using privileges is really the correct answer, since if a user actually has the privileges to delete or update the table, they could likely find a way to work around the suggestions below.
The tactics discussed below should be considered a "soft" immutability implementation -- it's only really good for keeping honest people ...
2
The best index for this query is (u1_id, t)
My initial guess was right, that you have indexes on (t) alone and on (u1_id) alone. You haven't told us the exact EXPLAIN output (which index is used), so the most probable explanation is that mysql is choosing to use one of these existing indexes or none at all (doing a full scan of the table), which yields the ...
2
Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?
Short answer: Yes, but they probably shouldn't.
Depending on how your application works, it is possible (even likely) that writing to the slave will break replication. Even if it doesn't, over time it's likely that your databases will develop ...
2
You are doing the right way,
In many-to-many relation your must have a third table mapped by the ID of the two other tables. The two Id must be foreign key, and the association of the two is the primary key of your association's table
You can also store in this table the information that are related on the relation.
2
MariaDB developers claim that it's a drop-in replacement, and it's true until version 5.5.
MariaDB 5.1-5.2-5.3 can replace MySQL 5.1.
MariaDB 5.5 can replace MySQL 5.5
The small "incompatibility" issues usually don't apply, however, they are documented here:
https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/
There are also a lot of bug fixes and ...
2
Percona xtrabackup documentation indicates that you should point xtrabackup_55 to the backup-my.cnf file taken with your backup, rather than your live my.cnf file.
If you're doing the prep on the same server as the backup was taken, I'm not sure if it will make a difference.
2
Try refactoring the query as an all-out JOIN
SELECT B.* FROM
(
SELECT 502 sale_id,2 product_id
UNION SELECT 502,1
UNION SELECT 502,3
UNION SELECT 504,4
UNION SELECT 504,2
UNION SELECT 504,3
) A INNER JOIN sale_product_inventories B
USING (sale_id,product_id);
The index has to be utilized in this case.
Why did it not use the index ...
2
No, but you can use a Stored Procedure which queries the information_schema and composes the DROP TABLEs dynamically.
CREATE PROCEDURE `test`.drop_old_tables()
MODIFIES SQL DATA
COMMENT 'Drops tables older than 1 week'
BEGIN
DECLARE `eof` BOOLEAN DEFAULT FALSE;
DECLARE `db_name` VARCHAR(64);
DECLARE `tab_name` VARCHAR(64);
DECLARE ...
2
Use the below Query:
select CONCAT('drop table ',TABLE_NAME)
from INFORMATION_SCHEMA.TABLES
where update_time < (now()-interval 1 week)
Reference
Additionally, you may add the above query to a prepared statements in order to execute the drop(s) as is shown in the answer from here
2
All the metadata about tables are stored in the db named information_schema.
Try this:
SELECT
TABLE_NAME
FROM
information_schema.TABLES
WHERE
UPDATE_TIME < (NOW()-INTERVAL 1 WEEK)
UPDATE: i forgot to mention this will get you all the tables that you need to drop, after that you can build the DROP statements.
2
Personally, I cringe when I hear about upgrades gone wrong due to human error. Most have success stories when upgrading MySQL in succession (5.0, 5.1, 5.5, 5.6) Some create their own disaster movies by moving datadir, uninstalling old MySQL, installing new MySQL, putting back the datadir and think everything's OK ... NOT !!!
MySQL Upgrade
MySQL created ...
1
It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).
An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.
Because Company_id is now part of Department's Primary Key, it must also be part of the ...
1
You have a trade-off you need to be aware of.
Granted, it is true that a log flush happens with each INSERT involved with autocommit=1. Nevertheless, are there any consequences of setting autocommit=0 ?
Think about the redo logs (ib_logfile0,ib_logfile1) and the undo tablespace (inside ibdata1). Change information must be stored somewhere in case the ...
1
There is no order, subject to standard operator precedence.
SQL is a declarative language. You tell the engine "what to get", "how to do it". So the engine does not evaluate left to right in code order, nor does it generally short circuit.
For example:
the engine attempts to match the predicates against a suitable index. The index key columns order may ...
1
According to this bug report (resolved as "not a bug"), you shouldn't put settings for mysqld in the [client] section of your .ini file.
Put the max_allowed_packet option in the [mysqld] section instead.
See Using Option Files for more details.
1
I was looking over the comments from @Federico's answer.
When you ran mysql -V, you got the client program's version. Thus, the mysql client is definitely 32-bit. You need to run mysqld -V. This will give you the server's version (i.e., the version of mysqld)
EXAMPLE
[root@***]# mysqld -V
mysqld Ver 5.0.81-community-log for unknown-linux-gnu on x86_64 ...
1
Wouldn't this be sufficient for your update, since the subquery providing the update value evaluates to 0 if there aren't any answers for your question.
UPDATE tbl_questions q
SET q.answercount = (SELECT COUNT(*) FROM tbl_answers a WHERE a.qid = q.id)
WHERE q.answercount = 0
1
Here are some of my inputs to stand "FOR OUTSIDE DB"
It purely depends upon your need. If your business wants to keep them always available and recoverable with no down time then you might have to think a proper option to keep them available 24/7, but keeping them in Database the capacity management of DB server will be in question.
If no priority for your ...
1
The best I can immediately mention is to simply run EXPLAIN on a query.
There is a graphical way to do this
pt-visual-explain
mk-visual-explain (Source Code)
As for steps to optimizing a query, a parse tree is created. Rather than explaining the parser here, please read my post from Mar 11, 2013 (Is there an execution difference between a JOIN condition ...
1
Normally you want to throw away as many rows as you can, as quickly as you can. That doesn't seem likely here, unfortunately.
All you have is 'deleted = 0' for each table, so MySQL will pick a table to start with. In your explain, it picked user_details, resulting in 1.5M rows being used.
Do the other tables have fewer active rows? If so, you'd want to ...
1
Errcode: 13 is a file permission issue.
If your datadir is /var/lib/mysql, do the following:
cd /var/lib/
ls -l
Check the file permissions as make sure /var/lib/mysql is owned by mysql:mysql.
If it is not, then do this:
chown -R mysql:mysql /var/lib/mysql
If you ran sudo, you are probably root. Make sure root can write into /var/lib/mysql
Give it a ...
1
Several points:
1) Store the data in two columns.
2) Normalize 'type' to a separate table, so you'll only put an INT into your index rather than a varchar(255).
3) Switch to UNSIGNED INT for the ids, and maybe TINYINT for the types.
4) Tell the app people to learn about multiple WHERE clauses for their SQL and give them a good index.
3) Once you do ...
1
You can use MySQL Workbench to connect and migrate your schema/data from MS Access to mySQL.
First you need to configure an ODBC Data Source:
Click Start, and then click Control Panel.
In the Control Panel, double-click Administrative Tools.
In the Administrative Tools dialog box, double-click Data Sources (ODBC). The ODBC Data Source Administrator dialog ...
1
In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.
However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write ...
1
memlock was the issue.
turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql.
changed memlock settings in '/etc/security/limits.conf', and limit to 8GB.
then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log.
a few ...
1
The following is valid for both engines (InnoDB and TokuDB).
The first schema will contain two indexes: a clustered primary key index (gameId) and a secondary index (createDate).
The second schema will create three indexes: a clustered primary key index (idx), a unique secondary index (gameId), and a secondary index (createDate).
Therefore, each insert ...
Only top voted, non community-wiki answers of a minimum length are eligible
