I have a denormalized table product with about 6 million rows (~ 2GB) mainly for lookups. Fields include price, color, unitprice, weight, ... I have BTREE indexes on color etc. Queriy conditions are dynamically generated from the Web, such as select count(*) from product where color=1 and price > 5 and price <100 and weight > 30 ... et...
For a forum, should I use MyISAM or InnoDB for the table that stores the text? I know that MyISAM supports full text searching, which sounds like it's what I should go for, but while reading, I've come across this very confusing sentence. You should use InnoDB when transactions are important, such as for situations in which INS...
I have a 10M-row table product with fields like color (int), price (float), weight (float), unitprice (int), etc ... Now users from Web dynamically generate queries to lookup data from this table with random conditions (color is a must have here) and order-by such as select * from product where color=1 and price >5 and price <220 and .....
I read a comment on another post that InnoDB can be optimized for speed by not flushing binary logs on every commit. This is the first time I hear about this, so what are these binary logs and what's this flushing all about? Why should I do it (and when not to do it) and how? ...
I'm trying to create a basic search functionality for MySQL InnoDB. I know there is Full-text search, but I just want to make an easy and simple solution for now. What I want is that when a user search for example "BMW Car" I want to find results not just like "BMW Car" but also "Car BMW" "BMW Z4 Car" and so on.. Is there an easy way to ...
I have a php query that runs fairly often like this one: $query = 'SELECT * FROM work_orders ' .'WHERE ' . "((end_time >= ?" . "AND start_time <= ?) " . "OR (start_time <= ? " . "AND end_time >= ? ) " . "OR (start_time >= ? " . "AND end_time <= ? )) "; And a table defined as: CREATE TABLE IF NOT ...
How many records can a MySQL MyISAM table store? How many InnoDB can? ...
I have encountered a problem when designing the table schema for our system. Here is the situation: our system has a lot of items ( more than 20 millions ), each item has an unique id, but for each item there can be lots of records. For example for the item with id 1 there are about 5000 records and each record has more than 20 attrib...
Hey all. I've got the following set of tables which is variable and adds up every day: data-2010-10-10 data-2010-10-11 data-2010-10-12 data-2010-10-13 And so on. All the tables have the same structure and what I'd like to do is select stuff from all tables at once. I'm unable to use a MERGE table since I'm running InnoDB. Anyways, I'm...
I would like to know if it's possible in innodb in mysql to have a table with foreign key that references another table in a different database ? And if so, how this can be done ? ...
Server shutdown from power failure. Mysql will not start now. Disk is not full. Syslog is below Oct 11 15:03:31 joe mysqld_safe[24757]: started Oct 11 15:03:31 joe mysqld[24760]: 101011 15:03:31 InnoDB: Operating system error number 13 in a file operation. Oct 11 15:03:31 joe mysqld[24760]: InnoDB: The error means mysqld does not have ...
I REALLY hope somebody can help me with this as it's been hard to get help for it elsewhere, including mysql.com Anyway... I'm having a problem with a InnoDB (table was initally MyISAM, but converted it to InndoB awhile ago) table; I am trying to run this query: SELECT posts.id, posts.post_title FROM rss_posts AS posts INN...
In MySQL, I sometimes type "show innodb status" to see that a long-running query is doing something. The bottom has, under "ROW OPERATIONS", a line: 2000.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2000.00 reads/s What are the units for these? Records? InnoDB pages? OS disk pages? (Are they the same thing?) ...
I have a table where I'm esentially only writing. Think of it as a "transactions" list, where I keep all the details, and I also have a "total" stored in another table. That total is redundant, for performance, but it can be re-calculated if needed from this transactions table. What is better from a performance point of view, keeping ...
In a table with 5 millions rows, a SELECT count(*) FROM table would be instant in MyISAM but would take several seconds in InnoDB. Why is this that way? Why haven't they optimise count in InnoDB like MyISAM? Thanks. ...
I have heard about this problem and now I am looking for more specific information? How does it happens, what are the reasons for that, detailed explanation of the mechanism of the deadlock to try to avoid it. How to detect the deadlock, solve it and protect the data from being corrupted because of it. The case is when using MySQL with ...
Hi, I have a simple database table with 170MB overhead. Is this something I need to worry about? When I run optimize on it, it tells me that innodb doesn't support optimize and so recreates the table but still has 170MB overhead. Is this something I can comfortably ignore? cheers! ...
Hi All: I have a question about MySQL InnoDB. For example: I have the following table created: mysql>CREATE TABLE IF NOT EXISTS `SeqNum` ( `id` varchar(10) NOT NULL, `seq_num` BIGINT(30) default 0, PRIMARY KEY(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.00 sec) mysql>INSERT IG...
I have two tables: Users and Friendships. The actions I have in the website are: Register Log in Select friends/people from users table Add people as friends There are a little more actions but in general i wanted to know : The Question: Should I use InnoDB or MyISAM for the tables ? ( Or InnoDB for one and MyISAM for the other ? ...
Hello, Recently ran into a problem after putting my codeiginter application into a clustered environment. Users are able to get site rewards at point levels, after they click submit to retrieve the reward I have code that grabs the current point level of the reward is at and save that in a table with the user_id and what point level the...