What is optimal isolation level for MySql using InnoDB running Moodle 1.9.X
Which InnoDB isolation level should be used with Moodle 1.9.X. The default is REPEATABLE READ, is it save, however, to use READ COMMITTED for better performace? ...
innodb
how to avoid deadlock in mysql
I have the following query (all tables are innoDB) INSERT INTO busy_machines(machine) SELECT machine FROM all_machines WHERE machine NOT IN (SELECT machine FROM busy_machines) and machine_name!='Main' LIMIT 1 Which causes a deadlock when I run it in threads, obviously bec...
mysql full text search
I want to do full text search efficiently in MYSQL using match against. Specially I need to use binary operators(eg. match some text but not other text). However I also need efficient transaction support. But full text search is only supported on MYISAM, not Innodb. What to do? ...
Load a MySQL innodb database into memory
I have a MySQL innodb database at 1.9GB, showed by following command. SELECT table_schema "Data Base Name" , sum( data_length + index_length ) / 1 048 576 as "Data Base Size in MB" , sum( data_free )/ 1 048 576 as "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema ; +-------------------...
Indexing only one MySQL column value
I have a MySQL InnoDB table with a status column. The status can be 'done' or 'processing'. As the table grows, at most .1% of the status values will be 'processing,' whereas the other 99.9% of the values will be 'done.' This seems like a great candidate for an index due to the high selectivity for 'processing' (though not for 'done'). I...
Foreign keys vs partitioning
Hi! Since foreign keys are not supported by partitioned mySQL databases for the moment, I would like to hear some pro's and con's for a read-heavy application that will handle around 1-400 000 rows per table. Unfortunately, I dont have enough experience yet in this area to make the conclusion by myself... Thanks a lot! References: h...
Will MySQL InnoDB ON UPDATE CASCADE constraints update properly during a MySQL REPLACE command?
If I have table B with foreign key references to table A (set to ON UPDATE CASCADE) and I run a LOAD DATA INFILE file.txt REPLACE INTO TABLE A; command, will the references update properly? Please note that I'm not talking about ON DELETE CASCADE; I know a REPLACE command will delete records in table B if I have that set. ...
Replacement relevance sorting for MySQL fulltext in InnoDB tables?
I am using MySQL InnoDB and want to do fulltext style searches on certain columns. What is the best way of sorting the results in order of relevance? I am trying to do something like: SELECT columns, (LENGTH(column_name) - LENGTH(REPLACE(column_name, '%searchterm%', ''))) AS score FROM table WHERE column_name LIKE '%searchterm%' ORD...
How do I re-create a MySQL InnoDB table from an .ibd file?
Assume that the following MySQL files have been restored from a backup tape: tablename.frm tablename.ibd Furthermore, assume that the MySQL installation was running with innodb_file_per_table and that the database was cleanly shutdown with mysqladmin shutdown. Given a fresh install of the same MySQL version that the restored MySQL f...
Innodb setting in xampp doesn't seem locate my.cnf file....
I created a new mysql database and i want to use foreign keys with it... I googled and found out this... InnoDB is one of MySQL storage engines, it supports transactions, row-level locking, and foreign-keys. However, by default, InnoDB is not enabled by XAMPP. To enable it, locate the my.cnf configuration file (normally in C:/xampp/mysq...
Problems getting foreign keys working in MySQL
I've been trying to get a delete to cascade and it just doesn't seem to work. I'm sure I am missing something obvious, can anyone help me find it? I would expect a delete on the 'articles' table to trigger a delete on the corresponding rows in the 'article_section_lt' table. CREATE TABLE articles ( id INTEGER UNSIGNED PRIMARY KEY...
question about MySQL transaction and trigger
I quickly browsed MySQL manual but didn't find the exact information about my question. Here is my question: if I have a InnoDB table A with two triggers triggered by 'AFTER INSERT ON A' and 'AFTER UPDATE ON A'. More specifically, For example: one trigger is defined as: CREATE TRIGGER test_trigger AFTER INSERT ON A FOR EACH ROW...
Preview result of update/insert query without comitting changes to database in MySQL?
I am writing a script to import CSV files into existing tables within my database. I decided to do the insert/update operations myself using PHP and INSERT/UPDATE statements, and not use MySQL's LOAD INFILE command, I have good reasons for this. What I would like to do is emulate the insert/update operations and display the results to t...
InnoDB - Two foreign keys on the same column from one table
Hey Guys, i have a project table which has a image_id field and a newsimage_id field. Both are linked to the image table. But InnoDB doesn't allow me to set a foreign key for both fields to the same column (id). Is there a way I can do this or is it not possible? I'm using MySQL through MAMP. Thanks in advance!! ...
MySQL Full-text Search Workaround for innoDB tables
I'm designing an internal web application that uses MySQL as its backend database. The integrity of the data is crucial, so I am using the innoDB engine for its foreign key constraint features. I want to do a full-text search of one type of records, and that is not supported natively with innoDB tables. I'm not willing to move to MyISA...
How to improve INSERT INTO ... SELECT locking behavior
In our production database, we ran the following pseudo-code SQL batch query running every hour: INSERT INTO TemporaryTable (SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true) Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though i...
Why is MySQL with InnoDB doing a table scan when key exists and choosing to examine 70 times more rows?
Hello, I'm troubleshooting a query performance problem. Here's an expected query plan from explain: mysql> explain select * from table1 where tdcol between '2010-04-13 00:00' and '2010-04-14 03:16'; +----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+ | id | select_ty...
Mysql Database Question about Large Columns
Hi, I have a table that has 100.000 rows, and soon it will be doubled. The size of the database is currently 5 gb and most of them goes to one particular column, which is a text column for PDF files. We expect to have 20-30 GB or maybe 50 gb database after couple of month and this system will be used frequently. I have couple of questi...
Is MySQL InnoDB is appropriate for this scenario?
My MysQL database contains multiple MyISAM tables, with each table containing millions of rows. There is a heavy insert load on the database, so I cannot issue SELECTs on that live database. Instead, I create a replica of the database for queries and conduct analysis on that. For the analysis, I need to issue multiple parallel queries....
InnoDB - roll back all transactions for connection
Is it possible to rollback all statements that have been executed in the same connection thread? Instead of ROLLBACK just reverting the last executed statement. ...