myisam

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. I don't need 100% accurate information at the time of read). The task in question will be doing over 1 million database...

How can MyISAM tables be used more safely?

I like InnoDB's safety, consistency, and self-checking. But I need MyISAM's speed and light weight. How can I make MyISAM less prone to corruption due to crashes, bad data, etc.? It takes forever to go through a check (either CHECK TABLE or myisamchk). I'm not asking for transactional security -- that's what InnoDB is for. But I do w...

create an index on a mysql column based on the length its contents?

How do I create an index on a column in mysql v 5.0 (myisam db engine) based upon the length of its value its a TEXT data type up to 7000 characters, do I have to add another column with the length of the first column. Any help kindly received, cheers ...

Are there any pitfalls / things you need to know when changing from MyISAM to InnoDB

One of my projects use the MyISAM engine in MySQL, but I'm considering changing it to InnoDB as I need transaction support here and there. What should I look at or consider before doing this? Can I just change the engine, or should the data be prepared for it? ...

mysql replication - table locking?

I am currently working for a company that has a website running mysql/php (all tables are also using the MYISAM table type). We would like to implement replication, but I have read in the mysql docs and elsewhere on the internet that this will lock the tables when doing the writes to the binary log (which the slave dbs will eventually r...

MySQL MyISAM & innoDB Memory Usage

Hi all, Does anyone know how much memory MyISAM and innoDB use? How does their memory usages compare when dealing with small tables vs. when dealing with bigger tables (up to 32 GB)? I know innoDB is heavier than MyISAM, but just how much more? Any help would be appreciated. Thanks, jb ...

MySql: MyISAM vs. Inno DB!

What are the differences between MyISAM and Inno DB types in MySql? ...

Is there a REAL performance difference between INT and VARCHAR primary keys?

Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables. My argument, as detailed here, is that the performanc...

MySQL transaction support with mixed tables

It seems like I will be needing transaction with MySQL and I have no idea how should I manage transactions in Mysql with mixed InnoDB/MyISAM tables, It all seems like a huge mess. You might ask why would I ever want to mix the tables together... the anwer is PERFORMANCE. as many developers have noticed, InnoDB tables generally have bad ...

What does table level locking locks during inserts in MySQL?

I have read many times that MySQL will perform table level locks on MyISAM tables during inserts. It's OK with me if that lock is ment for serializing insert/delete commands only. Inserts are very infrequent on my website any way. But will that lock block all Select queries too until the insert command finishes? ...

Changing tables from MyISAM to InnoDB make the system slow

Hi I am using Mysql 5.0.x I have just changed a lot of the tables from MyISAM to InnoDB With the MyISAM tables it took about 1 minute to install our database With the InnoDB it takes about 15 minute to install the same database Why does the InnoDB take so long? What can I do to speed things up? The Database install does the follow...

mysqli_use_result() and concurrency

According to the documentation at mysqli_use_result One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched. Does this only pertain to myISAM tables or also for InnoDB? ...

How to apply an index to a MySQL / MyISAM table without locking?

Having a production table, with a very critical column (date) missing an index, are there any ways to apply said index without user impact? The table currently gets around 5-10 inserts every second, so full table locking is out; redirecting those inserts to alternative table / database, even temporarily, is also denied (for corporate po...

MySQL - Concurrent SELECTS - one client waits for another?

Hi, I have the following scenario: I have a database with a particular MyISAM table of about 4 million rows. I use stored procedures (MySQL Version 5.1) and one in particular to search through these rows on various criteria. This table has several indexes on it, and the queries through this stored procedure are normally very fast ( <1s...

What causes MyISAM to become corrupt?

I've heard that MyISAM tables can become corrupt, what sort of actions are most likely to corrupt them and how can you safely fix said corruptions. ...

Migrating php4/mysql4 to php5/mysql5: switch to InnoDB?

I have a legacy web application php4/mysql4 (MyISAM, db contains some cms, some user data, some calendar application). Now I am going to migrate to a new server with php5/mysql5. Should I change to InnoDB while migrating a mysql database? - expected advantages / disadvantages / risks? - Is MyISAM deprecated or will it some day be? Or ...

MySQL MYISAM concurrency issue with two processes

I have probably overlooked something simple, but I have been trying to understand what is going wrong here for a few weeks and I need a fresh set of eyes. I have a CGI application that processes Paypal Payment notifications. When someone subscribes I actually get a subscription IPN followed by a payment IPN. On is right behind the other...

Testing for concurrency and/or transactional integrity in a web application with JMeter

I'm rather new to working with multiple threads in a database (most of my career has been spent on the frontend). Today I tried testing out a simple php app I wrote to store values in a mysql db using ISAM tables emulating transactions using Table Locking. I just wrote a blog post on the procedure Here: Testing With JMeter From my...

Max column limit in mysql using myisam

Is there a way to calculate the maximum number of columns? Thanks. ...

MySQL MyISAM table performance... painfully, painfully slow

I've got a table structure that can be summarized as follows: pagegroup * pagegroupid * name has 3600 rows page * pageid * pagegroupid * data references pagegroup; has 10000 rows; can have anything between 1-700 rows per pagegroup; the data column is of type mediumtext and the column contains 100k - 200kbytes data per row userdata...