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...
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...
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 ...
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? ...
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...
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 ...
What are the differences between MyISAM and Inno DB types in MySql? ...
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...
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 ...
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? ...
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...
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? ...
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...
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...
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. ...
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 ...
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...
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...
Is there a way to calculate the maximum number of columns? Thanks. ...
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...