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...
I know it's generally a bad idea to do queries like this: SELECT * FROM `group_relations` But when I just want the count, should I go for this query since that allows the table to change but still yields the same results. SELECT COUNT(*) FROM `group_relations` Or the more specfic SELECT COUNT(`group_id`) FROM `group_relations` I...
Hi, I have a database efficiency question. Here is some info about my table: -table of about 500-1000 records -records are added and deleted every day. - usually have about the same amount being added and deleted every day (size of active records stays the same) Now, my question is.....when I delete records,...should I (A) delete the...
I work on a big web application that uses a MySQL 5.0 database with InnoDB tables. Twice over the last couple of months, we have experienced the following scenario: The database server runs fine for weeks, with low load and few slow queries. A frequently-executed query that previously ran quickly will suddenly start running very slowl...
Hi, I have a problem with an SQL database I'm creating. I'm trying to switch it over to use INNODB but I can't seem to get the syntax (or possibly logic) correct for cascading. Here's a part of the code erroring. It does not like line 40. Error output in the usual cryptic (to me at least) form: ERROR 1005 (HY000) at line 36: Can't ...
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? ...
Hey, I have two tables: CREATE TABLE `Car` ( `car_id` int(11) NOT NULL AUTO_INCREMENT, `car_name` varchar(25), PRIMARY KEY(`car_id`) ) ENGINE=INNODB; CREATE TABLE `Tire` ( `tire_id` int(11) NOT NULL AUTO_INCREMENT, `tire_size` int(11), `car_id_FK` int(11) NOT NULL DEFAULT '0', PRIMARY KEY(`tire_id`), CONSTRAINT `R...
I have read several times that after you delete a row in an InnoDB table in MySQL, its space is not reused, so if you make a lot of INSERTs into a table and then periodically DELETE some rows the table will use more and more space on disk, as if the rows were not deleted at all. Recently I've been told though that the space occupied by ...
Hi, I know that deadlocks occur inevitably when using transactions in Innodb and that they are harmless if they are treated properly by the application code - "just try it again", as the manual says. So I was wondering - how do you detect deadlocks? Does a deadlock issue some special mysql error number? I am using PHP's mysqli extensio...
When an InnoDB table is created without a primary key a "hidden" auto inc key is created and used for indexes. Does anyone know if this key is multi-master safe? If setting up auto incs in a database that was to be multi-master the standard mysql approach is to set auto_increment_increment and auto_increment_offset to appropriate values...
I have a MySQL database that has on the order of 400 million innodb_data_pending_reads and innodb_pending_writes. My other databases are consistently at or near zero, so I noticed this large outlier. What can cause this situation? What adverse affects can it cause? How can I troubleshoot the situation to bring this down? ...
I have a huge list of URLs in a MySQL InnoDB table, and worker processes that query MySQL for a set of URLs to process. The URLs should immediately be marked as being processed, so that other worker processes do not waste resources by starting to process the same ones. Currently I first do this to get some URLs: SELECT DISTINCT url FRO...
I have an existing InnoDB table which already has foreign keys pointing to different tables. But when I try to create a foreign key pointing to the Primary index, I get an error (check data type). The table is User with User_Id as the Primary. I want a foreign key Manager_ID which is a FK to User_Id. Both of INT Both of Length 10 Uns...
I'm in the process of swapping over a database for a rewrite of my program and part of that is writing both a conversion script and a script to create new tables. I'm renaming tables, changing indexes and generally altering most of the table in some way, part of that is that I'm changing from MyISAM to InnoDB tables. The conversion scr...
Hi All, I am working on a development copy of a MySQL / InnoDB database that is about 5GB. I need to restore it pretty frequently for testing alter scripts, and using the mysqldump file is taking quite a while. The file itself is about 900MB and takes around an hour to load in. I've removed data inserts for unimportant tables, and do...
I'm trying to add a row to an InnoDB table with a simply query: INSERT INTO zip_codes (zip_code, city) VALUES ('90210', 'Beverly Hills'); But when I attempt this query, I get the following: ERROR 1114 (HY000): The table `zip_codes` is full Doing a "SELECT COUNT(*) FROM zip_codes" gives me 188,959 rows, which doesn't seem like too m...
Here's an excerpt of my current database (changed the table-names for an easier understanding): Pet(ownerFK, id, name, age) Owner(id, name) Where id is always a surrogate key, created with auto_increment. I want to have the surrogate key Pet.id to be "scoped" by Pet.ownerFK or in otherwords, have a composite key [ownerFk, id] as my m...
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 a MySQL InnoDB table on a RedHat Enterprise Linux 4 server, and after trying to import a database previously backed up using mysqldump I got a "the table is full" error. The table currently has 463,062 rows in it, and the ibdata1 file on disk is currently 3.37Gb. A quick "SHOW VARIABLES;" shows that the innodb_data_file_path is ...
Does this cause a race condition with MySQL (InnoDB): Start Transaction. Try to get record. If record doesn't exist, return. If record exists, delete it and add a log entry saying that is was deleted. End Transaction (commit/rollback). Is it possible for another process to start just before the delete step in 2b, detect the presence ...