Tag Info

Hot answers tagged

3

Data is present in data folder of MySQL which is usually in ProgramData for windows installations. look into "C:\ProgramData\MySQL\MySQL Server 5.6"..as programdata usually have the files on by default


3

You deleted the *.ibd files, but entries in the InnoDB dictionary remain. Create empty temporary database. Let it be tmp1234 Move all tables from the original database to tmp1234 Drop the original database (it’s empty by now, all tables are in tmp1234) Create the original database again Move all tables from the temporary database to the original one. Drop ...


3

In MyISAM records are stored in blocks. There are like 20 different block types, some of can be really large. Those are used for BLOB/TEXT values. I never saw that MyISAM record stores a pointer to a BLOB (but I would not insist). In InnoDB BLOB/TEXT values are also a part of the record and stored in-page as long as total record size not more than ~7k. ...


3

To add to the responses here covering the mechanical differences between the two engines, I present an empirical speed comparison study. In terms of pure speed, it is not always the case that MyISAM is faster than InnoDB but in my experience it tends to be faster for PURE READ working environments by a factor of about 2.0-2.5 times. Clearly this isn't ...


2

If you apply the redo log right after a backup copy is taken you can greatly reduce time to restore. Sometimes --apply-log step fails. Due to a bug or tablespace corruption. In either case you don't want to learn that in the emergency. That's one more argument to do the --apply-log after the backup. People don't apply the redo log because it brings in more ...


2

If both tables are big enough so this is really a concern for you then fastest way is using two queries, one for each side of OR. That way you can be sure each query only accesses necessary rows, or even uses only index. If you are worried about "sending" two queries, then you can "glue" them together using UNION - if you get at least one row, then one of ...


1

Yes you can use innodb_file_per_table = 1 for your slaves. Additionally take below points in your consideration If you have same version on MySQL on both Master and Slave. You can directly take backup and setup replication from Master to Slave by adding innodb_file_per_table = 1 on slave in my.cnf Please have a look at How to set Master Slave ...


1

Try this in just 1 line of code: SELECT articleId, max(version) as currentVersion, CAST(GROUP_CONCAT(version SEPARATOR " | ") as CHAR) as verification FROM journalarticle GROUP BY articleId; PD: I added CAST(GROUP_CONCAT(version SEPARATOR " | ") as CHAR) as verification so you can see that is taking the last version ...


1

No, you should not do OPTIMIZE TABLE because it is the same as ALTER TABLE tblname ENGINE=WhateverTheStorageEngineIs; ANALYZE TABLE tblname; Thus, doing an ALTER TABLE tblname ... ;of any kind followed byOPTIMIZE TABLE tblname;` would create two temp tables. BTW when you do OPTIMIZE TABLE tblname; on an InnoDB Table you get this mysql> OPTIMIZE TABLE ...


1

I can't find documentation confirming it anywhere, but I think it's: select count from information_schema.innodb_metrics where name = 'trx_rseg_history_len'; The closest thing to a confirmation is a bug report referring to this counter as the history list length. Also, the current, min and max values match the logs I have recorded. I would be nice if ...


1

romeo, before I craft a reply, I want to ask: Is there any chance you have the opportunity to edit the schema in this case? If you can, you definitely should, because the problems you're running into are because of denormalization. Normalization of the schema From your example table above, it appears this should be split into at least two separate tables. ...


1

Take a look at the InnoDB Internals (Picture from Percona CTO Vadim Tkachenko) Please note the data dictionary inside ibdata1 (the system tablespace) It sounds like you have a broken data dictionary entry on the task table when it existed The only way around would be move all the tables into another database. For starters, suppose you have database ...


1

You're not seeing your databases in phpMyAdmin because you are logged in as the anonymous user. Log in using the new username and password you've created/changed to and you should see your old databases. By default, phpMyAdmin uses the "cookie" authentication mode, which means you're prompted when logging in. If you've changed that to be "config" you'll ...


1

TEXT fields have what the MySQL Documentation (Compressing BLOB, VARCHAR and TEXT Columns) calls "off-column storage" In a clustered index, BLOB, VARCHAR and TEXT columns that are not part of the primary key may be stored on separately allocated (“overflow”) pages. We call these “off-page columns” whose values are stored on singly-linked lists of ...



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