This question is related to this one. I have a page table with the following structure: CREATE TABLE mydatabase.page ( pageid int(10) unsigned NOT NULL auto_increment, sourceid int(10) unsigned default NULL, number int(10) unsigned default NULL, data mediumtext, processed int(10) unsigned default NULL, PRIMARY KEY (pageid...
I have a very simple table with two columns, but has 4.5M rows. CREATE TABLE `content_link` ( `category_id` mediumint(8) unsigned NOT NULL, `content_id` int(10) unsigned NOT NULL, PRIMARY KEY (`content_id`,`category_id`), KEY `content_id` (`content_id`,`category_id`) ) ENGINE=MyISAM; When I run a simple query like: SELECT ...
sometimes i get an error like "table is marked as corrupt and shld be repaired". that DB (tables) is using MyISAM. recently that keeps happening. what could be the causes? most recently i am executing a batch insert INSERT INTO table (..., ..., ...) VALUES (...), (...), (...) ... and it just hung. or took very long to complete it seem...
I'm attempting to add searching support for my PHP web app using MySQL's FULLTEXT indexes. I created a test table (using the MyISAM type, with a single text field a) and entered some sample data. Now if I'm right the following query should return both those rows: SELECT * FROM test WHERE MATCH(a) AGAINST('databases') However it retur...
I have two MySQL (MyISAM) tables: Posts: PostID(primary key), post_text, post_date, etc. Comments: CommentID(primary key), comment_text, comment_date, etc. I want to delete all the comments in the "Comments" table belonging to a particular post, when the corresponding post record is deleted from the "Posts" table. I know this can ...
My database experienced some corruption last week, and the technicians from the web hosting company changed all my tables to MyISAM and performed repair on those tables. However, my application requires InnoDB tables. I've tried the method mentioned in this website However, I get this message #1025 - Error on rename of './user_db/#sql...
When recovering a MyISAM table the only file that is strictly needed is the data file (tablename.MYD) - the rest of the files (the index file tablename.MYI and tablename.frm) can be recreated from the data file using REPAIR TABLE. Assume I'm using InnoDB (with the "innodb_file_per_table" setting) instead - what is the minimum set of fil...
I have a c program running on ubuntu connecting to mysql (5.0.51a-3ubuntu5.4-log). The programs main task is to process records from a small (<5000 rows) myisam table. If the row is processed successfully it is deleted. If not it is retried at a latter date. After a number of failed attempts it is deleted. The deletion uses the tables p...
I'm trying to use rsync to backup MySQL data. The tables use the MyISAM storage engine. My expectation was that after the first rsync, subsequent rsyncs would be very fast. It turns out, if the table data was changed at all, the operation slows way down. I did an experiment with a 989 MB MYD file containing real data: Test 1 - recop...
When using MyISAM the configuration setting key_buffer_size defines the size of the global buffer where MySQL caches frequently used blocks of index data. What is the corresponding setting for InnoDB? ...
MyISAM uses table level locking which means that SELECT:s are blocked while INSERT/UPDATE:s are running. To mitigate the problem of blocked SELECT:s I've been recommended to configure MySQL with these parameters: low_priority_updates=1 concurrent_insert=2 What are the drawbacks of using low_priority_updates=1 and concurrent_insert=2...
Below is the ammount of rows for 2 tables in my mysql DB 1 is myisam the other innodb, can someone tell me why the innodb one has this ~ in front of the number? These numbers came from phpmyadmin 10,308 MyISAM ~118,011 InnoDB ...
Hi, I am trying to add a spatial index to a table column named Location of type BLOB. If I try this: ALTER TABLE route ADD SPATIAL INDEX(Location); I get: Error: BLOB/TEXT column 'Location' used in key specification without a key length But in the official docs for MySql 5.1 (the version I am using), it clearly says when...
I have an older web application that uses a MySQL Database (MYISAM). I noticed recently that the performance of the application was drastically reduced. After checking on my indexes, I noticed that the cardinality for all of them was reporting zero. I was able to fix this by doing an ANALYZE TABLE on each table. But I'm curious, what ...
How to select the MySQL's engine name of some table. MyISAM or InnoDB. Can we do it by a simple sql query? ...
I had all my tables in myISAM but the table level locking was starting to kill me when I had long running update jobs. I converted my primary tables over to InnoDB and now many of my queries are taking over 1 minute to complete where they were nearly instantaneous on myISAM. They are usually stuck in the Sorting result step. Did I do som...
I have one really long running read. It is a cronjob run once a day, but the whole DB gets locked down when it is running : mysql> show full processlist; +--------+------+-----------+------+---------+------+--------------+--------------------------------------------------------------------------------------------------------------------...
I have a 3 large tables (10k, 10k, and 100M rows) and am trying to do a simple count on a join of them, where all the joined columns are indexed. Why does the COUNT(*) take so long, and how can I speed it up (without triggers and a running summary)? mysql> describe SELECT COUNT(*) FROM `metaward_alias` INNER JOIN `metaward_achiever` ON ...
table: postid|userid|post|replyto post sql SELECT * FROM table WHERE postid=12 total replies sql SELECT COUNT(*) AS total FROM table WHERE replyto=12 the expected result is the "post table" + how many replies to the post. replyto field is the target postid. somehing like : postid|userid|post|replyto|totalreplies Is there a possibi...
I am migrating data between two tables using the query below. I had table locking issues, so I am considering changing the table engines from MyISAM to InnoDB. Which tables do I need to do that for? Just the table I am writing to, or both the table I am writing to and the one I am reading from? INSERT INTO table1 ( field1, field2, fie...