I have a database with static tables which require to be updated from CSV weekly. Tables are Mysql MyISAM and by static i mean they are used for read only (except when updated from CVS, obviously). There're about 50 tables and in total about 200mb of data to be reloaded weekly. I can think about 3 ways: Truncate table Load data from ...
Scenario: I have users that want to be able to change their username (unfortunately I didn't think about adding this functionality when I started the site), but the MySQL database has 110 tables. Am I going to have to just make a script that executes 110 queries to update the username in each table (and remember to update the script whe...
We're running a social networking site that logs every member's action (including visiting other member's pages); this involves a lot of writes to the db. These actions are stored in a MyISAM table and since something is starting to tax the CPU, my first thought was that it's the table locking of MyISAM that is causing this stress on the...
It seems that mysql select content (as opposed to e.g. count) queries always take at least a table read lock on myisam tables and a row read lock on innodb tables. Is there a way to issue a select content query in mysql (I could change table type if that's required) without having it to grab any locks? I don't mind if the data returned i...
I have a MySQL 1.5 GB MyISAM-table (1.0 GB data, 0.5 GB indexes) in production which I'm about to convert into InnoDB. Since the table is used in production I'd like to make the downtime as short as possible. My questions: What MySQL configuration options should be adjusted in order to speed up ALTER TABLE table_name ENGINE=InnoDB;? ...
I have a database with about 30 tables and 5 tables of them is write-intensive. I'm considering Convert 5 write-intensive tables to use InnoDB engine and keep the rest on MyISAM engine Convert all tables to use InnoDB engine. I wonder which approach is better? To be more specific The reason I want to keep some table on MyISAM engi...
Hi everyone While I've been writing php for a long time, it was always a skill I learnt myself and I've just hit a minor crisis of confidence over table joins! Assuming a MySQL db auth containing MyISAM tables users and permissions: users - id (auto increment) - email permissions - id (auto increment) - name To join these tables in...
Hi all, I'm importing 4.1mil records into an offline system to do some analysis on a subset of our database. As I'm running the import, I'm trying to check its progress by using: SHOW TABLE STATUS LIKE 'MailIssueElement' What's odd...at different times, I'm seeing higher AND lower values for Rows. I would expect it to only go up. ...
Assume a database consisting of 1 GB of data and 1 GB of index data. To minimize disk IO and hence maximize performance I want to allocate memory to MySQL so that the entire dataset including indexes can be kept in RAM (assume that the machine has RAM in abundance). The InnoDB parameter innodb_buffer_pool_size is used to specify the si...
I'm having trouble with a liferay installation that is suddenly (without any code modifications) failing with "dulplicate id" errors. We've identified that the issue may be solved by changing the mysql engine to InnoDB. I'm planning the change now, but i'm not sure what complications are likely in the process. I've checked for full te...
I was developing a small WAMP web application on my laptop, where I have an instance of mySQL running and I chose InnoDB for my DB engine. After several weeks' development I wanted to make it available to the public and found out the database server provided by my web host does not support InnoDB, only MyISAM. The create-and-populate sc...
I'm getting these about 5 times an hour in my slow query logs: # Query_time: 11.420629 Lock_time: 0.000033 Rows_sent: 0 Rows_examined: 0 SET timestamp=1267487708; INSERT INTO record_lock (record_lock.module_id, record_lock.module_record_id, record_lock.site_id, record_lock.user_id, record_lock.expiration_date_time, record_lock.date_ti...
I have a MySQL database with a fairly large table where the products are. Each of them has its own id and categoryId field where there is a category id belongs to this product. Now I have a query that pulls out products from given categories such as: SELECT * FROM products WHERE categoryId IN ( 1, 2, 3, 4, 5, 34, 6, 7, 8, 9, 10, 11, 12 ...
what are the difference between InnoDB and MyISAM. Which one I can go for? any areas where one provides and other doesnot provide? IS there any technological limitations in any of the both types? Please help me to choose the right type for my project? ...
I have been using MySQL for a number of years, and have got used to the occasional crash (some due to dodgy hardware, other due to dodgy code). When checinking/repairing tables, I have always used mysqlcheck after the server has started up again. Recently I had the luxury of not having to get the crashed server up immediately, so I trie...
I postet this question a few days ago but I didn't explain exactly what I want. I ask the question better formulated again: To clarify my problem I added some new information: I got an MySQL DB with MyISAM tables. The two relevant tables are: * orders_products: orders_products_id, orders_id, product_id, product_name, product_price, pr...
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...
What I've got working and it's what I need to improve on: INSERT form_data (id,data_id, email) SELECT '',fk_form_joiner_id AS data_id , value AS email FROM wp_contactform_submit_data WHERE form_key='your-email' This just gets the emails, now this is great, but not enough as I have a good few different values of ...
I've got an application that requires the use of MyISAM on a few tables, but the rest are the traditional InnoDB type. The application itself is not concerned with transactions where it applies to these records, but performance is a concern. The Rails testing environment assumes the engine used is transactional, though, so when the test...
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...