Tag Info

New answers tagged

0

You are far better off maintaining a foreign key constraint if your data warrants it. MySQL will automatically create an index - the FK must reference a UNIQUE KEY (obviously, can be PRIMARY - from here). See here for a host of reasons to as to why applying data constraints in the database is a VERY good idea. The index on the T table will increase ...


0

That's not how it works. Do it correctly first, then optimize. Use a foreign key if you need a foreign key. Then measure performance. Find the hot spots. Optimize those. If the foreign key happens to be large performance drag (doubtful) then remove it. Also, you didn't ask "What will be the impact of having incorrect data?"


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


-2

I found that solution: SELECT id_, articleId, version FROM ( SELECT id_, articleId, version FROM mf3_liferay.journalarticle ORDER BY version DESC ) as sub GROUP BY articleId ORDER BY ArticleId DESC But I am not sur regarding performances issues...


0

Try SELECT a.* FROM journalarticle a INNER JOIN ( SELECT articleId, MAX(version) as v FROM mf3_liferay.journalarticle GROUP BY articleId ) b ON (b.articleId = a.articleId AND a.version = b.v) Update: as ypercube pointed out, version is double which is approximate type, so conversion to exact type is needed : SELECT a.* FROM ...


0

To retrieve the ArticleID and its latest version, you can use this query: SELECT articleId, MAX(version) FROM journalarticle GROUP BY articleId; If you need to get all field from the table, you can use this query: SELECT * FROM journalarticle WHERE CONCAT(articleId, "##", version) IN (SELECT CONCAT(articleId, "##", MAX(version)) FROM ...


0

The short answer is: "Never use OPTIMIZE TABLE on InnoDB tables." There are exceptions, but they are rare and esoteric. You have not listed such a situation.


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


0

InnoDB does not support optimize, so does an "empty" alter table instead. If you just did an alter changing structure of the table, then optimize is meaningless. It might have some meaning for INPLACE algorithm of alter in newer MySQL versions, but it would be better do the normal alter instead in the first place.


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


0

Depends on what version you have. Here is the STATUS value: Innodb_history_list_length Description: History list length as shown in the TRANSACTIONS section of the SHOW ENGINE INNODB STATUS output. Scope: Global Data Type: numeric Introduced: MariaDB 5.5 / XtraDB 5.5.8-20 (quoted from:) ...


0

If you have special requirements for the values of your id, do not use AUTO_INCREMENT. There are too many cases (crashes, DELETEs, REPLACEs, INSERT IGNORE, etc) where the numbers will behave in unexpected ways.


0

If you have cfg and ibd file follow the below approach, Drop the table from the mysql server Create a table with similar structure alter table discard tablespace Move the cfg and ibd files into the data directory(that you have copied from another server) Alter table import tablespace


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


0

The MySQL vs. MongoDB argument is in many ways a relational vs. NoSQL argument. Each one excels in certain scenarios and is ill-suited for others. And because of their very different structures each one contains features not found in the other.


0

I have found that if tables get too large the server load can go up dramatically and CPU usage skyrocket.


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

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


-2

All the data used to be in default location of pc in fact wherever we are located doesn't matter so the default location is Ubuntu 9.10 Moving the database from /var/lib/mysql to /data/databases/mysql you can go and check your data there. It will be stored there and sometimes data may be hidden we can unlock it for seeing as a guest. If we are admin then we ...


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


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


0

Try a no-op ALTER: ALTER TABLE tablename ENGINE=InnoDB; (Suggested in this forum.) (Edits) I do not know why the problem happened. Here is another 'fix' that might work: CREATE TABLE b_new LIKE b; INSERT INTO b_new SELECT * FROM b; RENAME TABLE b TO b_old, b_new TO b; DROP TABLE b_old;


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


0

Yes, there are many differences. The biggest for me is that InnoDB supports foreign keys MyISAM does not. Some will argue that MyIsam is faster for reads, but if you want to take advantage of compression for example you need Innodb. My default position is to start with InnoDB whenever possible, then if you cannot use this for example when using spatial ...


0

MyISAM only -- Normally a record is a continuous stream of bytes in the .MYD file. This includes TEXT and BLOB columns. An index has a byte offset (or record number) to point into the .MYD file. After row(s) have been DELETEd, there can be holes in the .MYD. MyISAM prefers to fill in the holes before appending to the .MYD. However the hole(s) may not be ...


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


0

InnoDB has no tool to do anything like what you describe. InnoDB has a limit of 767 bytes per column in an index. That has room for a VARCHAR(255) utf8 or a VARCHAR(191) utf8mb4. Also, InnoDB wants to put long VARCHARs in a different block if the whole record is bigger than about 8KB. Will this be common? (Don't blindly use VARCHAR(500) when you can ...


0

You need the answers based on the storage engine in use Does inserting a row count as a disk write? For MyISAM, I would say yes because a MyISAM table keeps its total row count in its header. Inserting a row into a MyISAM would consequently impose an additional write to the table header to update the row count. For InnoDB, the disk writing activity to ...


0

A "write" is distinct from a "disk write". Writes include writes to tmp tables. This pattern avoids having to do subtracts (for Handler%, not for Innodb%): FLUSH STATUS; perform some query; SHOW SESSION STATUS LIKE 'Handler%'; Innodb_% values are only GLOBAL; Handler% are SESSION, then added to the GLOBAL tallies. Handler% is done at an engine-agnostic ...


0

"Near" is not relevant. This is because of the caching of both data blocks and index blocks in the buffer_pool. The caching leads to writing/reading in orders that your logic does not account for. Do not use the internal auto-generated PRIMARY KEY; always explicitly specify a PRIMARY KEY, even if you add the secondary keys later. If your PRIMARY KEY is ...


0

"Compressed MyISAM" -- what is that? Maybe "Archive"? Compression is not needed; the tables are too small, and will quickly be fully cached. FOREIGN KEYS -- Why? You have debugged you code, haven't you? There will be no dangling things to check. MEMORY is not unreasonable, if you write 'reload' code that executes whenever you bring up the server, and any ...


0

I'd be careful about commenting them all out in case some defaults might be undesirable. For example, we have a mix of MyISAM and TokuDB tables, so we have same settings configured for each, knowing that they have to coexist together (ie myisam key cache size, tokudb cache size).


0

These two lines are important. Basically InnoDB can't allocate the buffer pool requested. 2015-06-01 10:50:30 22061 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 2015-06-01 10:50:30 22061 [ERROR] Plugin 'InnoDB' init function returned error. Is this server dedicated database server? What other services are you running there? Monitor ...


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


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


0

The answer is in the log itself 2015-06-05 11:44:02 18075 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.6.20-log' socket: '/data/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) MySQL had finished starting up at 2015-06-05 11:44:02 Look at the error message 2 minutes later 2015-06-05 11:46:03 18075 [Warning] InnoDB: Cannot open ...


0

Running OPTIMIZE TABLE on all the tables in the Master is a very bad idea. Why ??? Since the Master has innodb_file_per_table disabled, running OPTIMIZE TABLE on all the table in the Master would make ibdata1 grow. I discussed this in my old StackOverflow post like 5 years ago : Howto: Clean a mysql InnoDB storage engine? Can I run OPTIMIZE TABLE ? ...



Top 50 recent answers are included