InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure.
This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row.
It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query.
Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day.
So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions?
My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Server Tuning
I run MySQL version 3.23.41 with RedHat v7.2 ... lately I noticed that our server with 768MB is running constantly at between 95% - 98% memory usage ... my question is, are there any generic tuning tips I can apply to reduce this ... e.g. can I direct MySQL to use disk area instead of main memory. Although I do not notice the actual database applications running slowly, other applications do not appear to be running as quickly or as stable as previously.
Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
Write/Move/Save Pdf To MySQL Table?
Is upload the only way to pass a file to a table? I have a contract.pdf which is generated via php (FPDF) and saved in a temporary file. I need that file then moved /written to the appropriate customer table in our database (MySQL ) so that the temporary file may then be overwritten by the next Customer/Order. While I am able to upload the file via <input type="file" name="uploadpdf> and related uploader.php, asking our web-users to “browse” for the file does not suit our needs, and of course adding a default value <input type="file" name="uploadpdf" value="http://localhost/crm/modules/Orders/contract.pdf"> doesn’t work either. As the file is always the same (modules/Orders/contract.pdf) how may I manage that move automatically, (say with a ‘Confirm Order’ button) to the database?
Innodb And Server Date
I have an innodb database running a week now. I have noticed that the time of my linux server is not the correct (about 9 minutes ahead). I was wondering if by synchronizing my server's clock with an ntp, I would confuse any innodb logs, because the correct time would be set 9 minutes earlier from the current incorrect time.
InnoDB & Multiple Mysqld's On 1 Server
i've been weighing the pros and cons of running multiple concurrent mysqld's on one server, to have better control over what databases are on what physical disks. System: 4 processor sun box running solaris with eighteen 36Gb drives. The situation is that i have a bunch of databases on one server that can all be classified as either external use or internal use. The internal use databases are consistently hit pretty hard, and we want this to have minimal impact on the external use databases. Currently we're using 64 index MyISAM tables, and with carefully choosing mount points for various physical devices, we have the databases separated as we want them. It is my understanding that with InnoDB, all tables are put into the configured InnoDB file(s) together, which would violate what i am trying to accomplish. The only solution i have come up with to control the physical location of InnoDB databases is to run multiple mysqld servers, each one with its InnoDB files on the desired device. Has anyone experienced any success or failure with this sort of configuration?
InnoDB & Multiple Mysqld's On 1 Server
i've been weighing the pros and cons of running multiple concurrent mysqld's on one server, to have better control over what databases are on what physical disks. System: 4 processor sun box running solaris with eighteen 36Gb drives. The situation is that i have a bunch of databases on one server that can all be classified as either external use or internal use. The internal use databases are consistently hit pretty hard, and we want this to have minimal impact on the external use databases. Currently we're using 64 index MyISAM tables, and with carefully choosing mount points for various physical devices, we have the databases separated as we want them. It is my understanding that with InnoDB, all tables are put into the configured InnoDB file(s) together, which would violate what i am trying to accomplish. The only solution i have come up with to control the physical location of InnoDB databases is to run multiple mysqld servers, each one with its InnoDB files on the desired device. Has anyone experienced any success or failure with this sort of configuration?
MyISAM On Master And InnoDB On Slave Server
Is this possible to have a MyISAM table on master server and change its storage engine (to InnoDB) on slave server in a replication environment? If this is possible, what should I consider before changing MyISAM to InnoDB on slave server? Here is the table structure:
MySQL Enterprise Server With InnoDB For Mission-critical Data?
However recently the company I work for has the requirement to upgrade their database system. The data here really is mission-critical and we can't afford to loose any of it, and we need 24-7 uptime, with good performance (we have lots of clients so there'll be heavy load), any downtime will cost us. What I'd like to hear is peoples own experiences with MySQL Enterprise, good or bad, and if it's up to the job. Looking around the net I've seen lots of supposed expert DBAs saying that there's no argument; you should go with one of the "big 3", these being DB2, Oracle or MSSQL. However I get the feeling Oracle and DB2 would be a massive task to configure, and MSSQL - well it runs on Windows, we can't have any downtime no matter how good people say it is these days. So my idea was; InnoDB on 3 servers - a master and 2 replication slaves, with one slave off-site down a 10Mb line (wide enough pipe?). We use the on-site slave to run our daily backups, and point our reporting web sites at it too. the off site is for worst-case scenario disaster recovery. How much would we be better off spending more ÂŁ on one of the "big 3"? Would our data be safer on these? Would we get better performance with these? Is there a better model I could use for the MySQL setup?
Optimize And Tuning
i was reading a post from long ago, and suggest for better performance at the mysql server to check the queries adn then make an optimization. i have this db that's eating lots of resources, and usually reaches 90% for the cpu and almost everything is used by the mysql. i supposse it´s a matter of tuning vars, wich one would you suggest to tackle first, and what others next in an myisam engine
Perfomance Tuning
I have just installed redhat linux 9 which ships with mysql 3.23.56. Mysql has to be setup so that it can use innodb tables, and data inserts (blobs) should be able to handle at least 8M at a time. The machine has two P III 933MHz CPU's, 1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig swap and 3 equal size ext3 partitions. What would be the recomended setup for good performance considering that the db will have about 15 users for 9 hours in a day, and about 10 or so users throughout the day who wont be conistenly using the db. Code:
Perfomance Tuning
I have just installed redhat linux 9 which ships with mysql 3.23.56. Mysql has to be setup so that it can use innodb tables, and data inserts (blobs) should be able to handle at least 8M at a time. The machine has two P III 933MHz CPU's, 1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig swap and 3 equal size ext3 partitions. What would be the recomended setup for good performance considering that the db will have about 15 users for 9 hours in a day, and about 10 or so users throughout the day who wont be conistenly using the db. My configuration looks like this so far: ++++++++++++++++/etc/mysql.cnf++++++++++++++++++++++++++++++++ [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock max_allowed_packet=16M #InnoDB innodb_data_file_path = ibdata/ibdata1:2000M:autoextend innodb_data_home_dir = /var/lib/mysql [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ++++++++++++++++/etc/mysql.cnf++++++++++++++++++++++++++++++++
Monitor / Tuning Of MYSQL
Can anyone tell me of documention/tutorials/software for monitoring and or tuning MYSQL databases. We currently run something called foglight (Quest Software) which tracks and monitors our Oracle Instances (sends us emails about things). I think we can code stuff to monitor the MYSQL databases that we are due to take over at some point, however I am not quite sure what to look for.
Performance Tuning For Inserts
i am inserting, usually, about 1-5 million records into a 50-350 million (depends on which DB i'm using) DB. The table has about 75 columns in it. I am getting, on average, processing time of about 6 seconds per thousand which is unacceptable. I need to speed this process up. Right now i am building a string in Java with "insert into <tableName> values(.......);" but I'm sure there's gotta be a faster way. Would it be faster to write all the additions to a seperate file and then load it using 'LOAD DATA LOCAL INFILE...'?
Tuning MySQL To Beat MS Access
I use MS Access to analyze survey data and I'm hoping to migrate to MySQL. I'm currently running MySQL 4.1 on a Windows 2000 multi-function machine. The following query has one of the simplest structures I would ever use, so I'm using it as a speed test before using MySQL for my much more complicated SELECT queries (as many as 45 joins, mostly using the same tables as below): SELECT count(r.r_id) FROM (r INNER JOIN q ON r.q_id = q.q_id) INNER JOIN a ON r.a_id=a.a_id; Full SHOW CREATE TABLE and SHOW INDEX FROM output for these tables is below. MS Access runs this query in just under 2 seconds. At first, MySQL took about 20 minutes. Indexing the bejeezus out of everything got it down to about 6 minutes; running ANALYZE TABLE on everything got it down to 2.5 minutes; and switching all the tables from InnoDB to MyISAM (duh) knocked it down to 7 seconds. Code:
Heavy Traffic System
I am developing a very heavy traffic site. It may happen that at same time (exactly same) two user will try to register with the same username. What will be the approach to prevent this situation. Which user will be able to register and which one will be denied? Do I need to use any locking facility? If so is it a wise decision to lock entire table?
Update Heavy Data
We have an os commerce application with a database of 7mb is working fine on our local server but when we are trying to update the data from local to online server via phpmyadmin we are unable to upload it there. Is any other way or idea to upload the file to server? "we can do it by root access of server but we dont have that acess in our hosting"
MySQL Slow With 4GB Memory - Tuning Required?
I am using MySQL 4.1 on Linux (IBM Open Power Server). The Server has 4GB memory, 380GB hard drive. I have a Java based application that runs in Tomcat in a Windows environment using SQL Server. As part of a customer project I have got the application to work on Tomcat on Linux with MySQL. Both databases have a large amount of data in some of the tables, e.g. 500,000+ records in some of the tables. Some of the tables have indexes on them. There is a specifc part of the application that is slow when running against MySQL (10 seconds to write a record to a table with 500,000+ records in it.) The same bit of functionality running against SQL Server is instance, e.g. 2 secomds max. The code is doing an insert statement. Once the insert has taken place we need to get the ID for the inserted record. This is using SELECT MAX....... The ID column is set as the Primary Key and is indexed. Code:
MySQL Handling Heavy Loads
Has anyone used MySQL in a public web site environment that receives 1 - 1.5 million sessions/day? Sessions are mostly content and light commerce. Site spikes to 3 million sessions/day about 4 times/year. Trying to find a good example of MySQL holding up under this kind of load.
Mysql Heavy Load On Databases And Connections?
I'm preparing for a lot of potential traffic to a web app. My question is. Is there any restriction on the amount of databases you have on a single mysql installation on a linux server. Could there be any performance issue if for example I had 100,000 databases all being connected to at different times? What is the upper limits of a standard LAMP dedicated server? What i mean is how many simultaneous connection can it handle before it starts to slow down for the users?
MySQL 4.0.13 Memory Problem Under Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
MySQL 4.0.13 Memory Problem On Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
MySQL 4.0.13 Memory Problem On Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
MySQL 4.0.13 Memory Problem Under Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
Mysql Version Is More Able To Handle Heavy Traffic
we are currently running mysql 4.1. we have problems with a php/mysql website that generates a lot of traffic and relies heavily on mysql (it's a "find your old schoolmates" community website). Sometimes it just says "too many connections", or just takes a long while before rendering the page. Would upgrading to mysql 5 help ? What are the best mysql settings in order to optimize the performance?
InnoDB, Can't Create Table
I had error message "Can't create table *.frm (errno:121)" when I was trying to create an innoDB table in MySQL Server 4.0.15. I know that the reason is the .frm files was deleted by some one which left data files. Does anybody know how to report and repair this sort of error.
Innodb Table Full
DBD::mysql::st execute failed: The table 'Transactions' is full at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm The table type is InnoDB.
InnoDB, Can't Create Table
I had error message "Can't create table *.frm (errno:121)" when I was trying to create an innoDB table in MySQL Server 4.0.15. I know that the reason is the .frm files was deleted by some one which left data files. Does anybody know how to report and repair this sort of error.
Table.innoDB Missing
I am a novice user of MySQL so please excuse my ignorance. I recently installed MySQL and have a third party tool using it to update data in a database. Recently it started creating erorr messages: table.innoDB missing Looking at the database in phpMyAdmin the table affected is marked as in use. Trying to get in reports the error again. This problem seems to have only affected 2 out of the three 3 tables. I have tried flushing the tables, restarting mysql, dropping the tables, restarting the computer and restoring from a backup and I still keep getting the same errors. I also did a search of my hard disk and found not one single *.innoDB file, so I suspect I am missing something fundamental.
Count Is Wrong For Innodb Table
I'm running a mysql database using innodb. When I use phpmyadmin to browse records of certain tables, the count keeps changing on me for some reason: Showing rows 0 - 29 (12,340 total, Query took 0.0003 sec) The "12,340" count would sometimes go up, and sometimes go down. It never stays the same. Is there a problem with my database? I can' figure this out. It shouldn't be doing this should it?
Table Type: MyISAM & InnoDB
I just ALTER my table type to InnoDB, my table is having ~ 200.000 records. And time to execute one small query is longer than MyISAM?
What Type Is Each Table? (MyISAM Or InnoDB)
I'm have about 200 MySQL databases on my server. Each database has 114 tables. All of the tables SHOULD be InnoDB, but I've found a few that are MyISAM. I'm trying to write a perl script to give me a list of all the MyISAM tables. Is there any sort of select statement that will give me the table type? (MyISAM or InnoDB) The only way I can figure this out is doing a 'SHOW CREATE TABLE' on each table and then looking at the table type from there. It would be 1000 times easier if I knew a select statement that would give me just the table type.
Multi-Table Delete & InnoDB
I have a system where several tables have a foreign key referencing a main table. When deleting a main table entry, I was planning on having multiple delete statements for each table. However, I realized that I could use foreign key Constraints to handle the deletion of rows in the dependent tables. I have been using DBDesigner4 to visually design my database and I have used it for creating the initial db. I actually like it quite well and I've had good success with it so far. I've also been using SQLyog for additional access to data during development. Even though DBDesigner shows that my dependent tables have a foreign key, when looking at the create statement, it simply calls them INDEX. When looking at the same statement through SQLyog it calls the FK simply KEY. When looking at the foreign key constraints, it shows in the CREATE & ALTER statement calling the foreign key FOREIGN KEY and then adding the reference and ON DELETE info (such as cascade). So, my questions are these. First, should I delete the existing KEY and then ALTER the table and add the FOREIGN KEY & constraints OR can I simply alter the tables and add constraints to the existing KEY index? How important is it that I use the constraints as opposed to simply having delete statements for each table? Is it simply for efficiancy and speed? Code:
Alter Table Tab Engine = InnoDB
I check the manual that alter table tab engine = InnoDB might cause unsafe result. So what is the the best way to alter a table engine quickly? In the manual, we have to say "auto_increment = xxx" what does this have to do with alter engine ? And also not all tables have a column that is auto_increment
Duplicate Key Entry On InnoDB Table
I have a table that contains a primary key, designated as VARCHAR (1). I cannot enter the same letter of the alphabet, using lower and upper case, to create 2 entries. For example, 'A', and 'a', both cannot be specified as primary keys.
Altering Huge Innodb Table
I need to add an index to an Innodb table 110gb big. When I last tried it took forever and made the db unusable to visitors. My solution was to create another table with the same structure with the extra index and copy everything over with a php script. This is working so far but is taking a long time and still hitting the performance for visitors. Is my solution the best way or is there another?
How To Estimate Innodb Table Size?
Innodb maintains extra info for ACID requirements so the data size is much larger than what we get from theoratical analysis: #records* (#bytes per record it should be). So my question is: how to estimate the table size?
InnoDb Table Count Incorrect??
This is the error I get as a warning in phpMyAdmin when I have removed a row from a table. Am I doing something wrong? 3.11 The number of records for InnoDB tables is not correct. phpMyAdmin uses a quick method to get the row count, and this method only returns an approximate count in the case of InnoDB tables. See $cfg['MaxExactCount'] for a way to modify those results, but this could have a serious impact on performance.
Order Of Fields In InnoDb Table
During my extensive reading a couple of weeks ago,I read that MySQL tables should have their fields in a specific order.All the foreign keys should be first followed by the 'hard data' fields. explain if this is, in fact,necessary and, if so, why.I can't recall where I read it or I would go back there again. said it should be like this: unique_id | business_id | address_id | first_name | last_name | 00001 | 00005 |00003 | Joe | Soap |
Creating Table Type Innodb
I am trying to create tables with type innodb. I created a database and all the tables of type ISAM. Now I am at the point of making relations and establish refrential integrity. For that ofcourse I have to change the tables in the Innodb type. I did that with the commad "alter table tablename type = innodb". Command runs fine but the table type doesnt change. I tried to create a new table of type innodb but its not working either. I dont know what the problem is. Do we have any command to enable the innodb property or what?
Custom InnoDB Table Space In Windows
In Windows, there are lots of config files for MySQL. When installing MySQL, the config wizard created a single, autoextending ibdata1 file for InnoDB tablespace. However, I want to customize the tablespace. Instead of innodb_data_file_path = ibdata1:10M:autoextend I would like to have something like this innodb_data_file_path = ibdata1:650M;ibdata2:650M;ibdata3:650M But when I make this change in the file my-innodb-heavy-4G.ini the server fails to start. How can I change the default config for InnoDB and customize it to my needs?
Converting Big Table From Type MyISAM To INNODB
i'm trying to convert a big table (approx 200,000 rows) from type MYISAM to INNODB but i found problem after converting the table type. I tried 2 ways to convert it, TblA - myISAM (original table with 200,420 rows) TblB - (to be INNODB table) 1. Copy structure of TblA to TblB, alter TblB to INNODB type, then insert records from TblA into TblB - the error is, TblB (203,373 rows) becomes more rows than TblA after these operations! 2. Copy structure + data of TblA to TblB, then alter table type of TblB to INNODB type - the error is, TblB (1xx,xxx rows) becomes less rows than TblA now! I don't know what's wrong with these operations, after all, i cant get a INNODB type table that has same rows number with the original table.
Lock InnoDB Table For Reading Having AUTOCOMMIT=1
I'm using MySQL 4.1.7 and InnoDB tables. I'd like to lock some tables for (write and) read of other users during a transaction, like this: START TRANSACTION; LOCK TABLES T1, T2; ... ... UNLOCK TABLES T1, T2; COMMIT; Since I have AUTOCOMMIT=1, this does not work: "You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES" (according to http://dev.mysql.com/doc/mysql/en/lock-tables.html). Do I have an alternative, having AUTOCOMMIT=1, to lock tables for read access during a transaction? I considered SELECT * FROM table FOR UPDATE, but this does not seem to perform a read lock.
Lookup FK Info On MyISAM And InnoDB Table
I'm wondering if there is a way to retrieve Foreign Key declaration information on both MyISAM and InnoDB tables. Of course, I understand that MyISAM doesn't support Foreign Keys, however, it does still parse FK declarations within a CREATE TABLE statement, and moreover, it even creates an index on the column that is the Foreign Key. So my question is for this sample CREATE TABLE declaration: CREATE TABLE table_name ( id INT NOT NULL AUTO_INCREMENT, other_id INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (other_id) REFERENCES other_table (id)) TYPE = InnoDB --- OR --- TYPE = MyISAM ; Can I get the Foreign Key information (meaing, the table and the column that a FK is keyed to) from MySQL? Doing a "DESC table_name" or "SHOW KEYS FROM table_name" indicates that the FK column is Indexed, but doesn't really give much more information other than that. The only way I seem to be able to get *some* sort of FK info is if i do a "SHOW CREATE TABLE table_name" (and even so, this only works on InnoDB tables. For MySQL tables, it will show my foreign key declaration as just a simple index).
Can't Open File: 'table.InnoDB'. (errno: 1)
I enabled/initialized innodb for my MySql database using innodb_data_file_path = ibdata1:10M:autoextend in my.cnf i created tables.. but some tables (others are working fine) when i try to access them is giving me Can't open file: table.InnoDB'. (errno: 1).
MyISAM Vs InnoDB While Creating A Table Using Select Query
I have created a table using the select query. **** for eg: create table table_name1 as select * from table_name2 **** now the created table is by default MyISAM format, what changes should i make to the query so that the created table is InnoDB.
Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query: $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC"); And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are: rel_id post_id category_id category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio. So pseudocode would be something like: SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).
|
TRACKER
