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 ...
I've got 2 tables: +-----------+ +------------------------------------+--------------+ + persons | | photos | +-----------| +---------------------------------------------------+ + id | name + | id | person_id | path | title | +-----------+ +---------------------------...
Hi, I need to implement something like a full text search on a couple if index's in a large products table, using innodb, MyISAM is not an option due to its lack of transactions and relationship support. I can think of a couple of ways of doing this, plugin, stored procedure, search table with keys and copied index's in MyIsam format. ...
I have a website which has thousands of (ever increasing) resources in it. I implemented the usual Sql Full text Search and it was working fine until recently. I noticed some performance issues with it. I am using MySql Database with C#. NET as the back-end code. I just need few valuable suggestions from you so that I can take those i...
This is a question that I've had forever. As far as I know the order of indices matter. So an index like [first_name, last_name] is not the same as [last_name, first_name], right? If I only define the first index, does it mean that it will only used for SELECT * FROM table WHERE first_name="john" AND last_name="doe"; and not for SELEC...
Im looking for a really nice jquery image streamer/slideshow plugin. Something unobtrusive, that can take a group of images and fade through them and then update the list when done. Something thats not basic, something that you can sit back and watch and incoming stream of uploaded photos. ...
situation: A user inputs a user name and password. now the user enters a user name called Game_Admin. Thus tries to imitate being a head power of the site and scam others. At the moment My log in script check for Length Characteristics, and to see if it already exist. My question how do I go about checking to see if a player enters a sp...
$columns = "name, address, city, state, zip"; $values = "'$name', '$address', '$city', '$state', '$zip'"; $query = "insert into customer values ($values)"; $statement = $dbh->prepare($query); $statement->execute(); So I am completely new to this. Kindergarten examples and baby talk will not be offensive! : ) The form executes with no ...
Say I have 50 rows in a MySQL table. I want to select the first ten (LIMIT 10), but then I want to be able to select the next 10 on a different page. So how do I start my selection, after row 10? Updated query: mysql_query(" SELECT * FROM `picdb` WHERE `username` = '$username' ORDER BY `picid` DESC LIMIT '$start','$co...
I am using a MATCH condition to match against a int field and a varchar field. According to http://bugs.mysql.com/bug.php?id=22343 when mixing binary and non-binary column types the match becomes binary and thus case sensitive. My question is how would I go about making the search non-case-sensitive? I have tried using MATCH (lower(a),b...
Is there a way to keep a timestamped record of every change to every column of every row in a MySQL table? This way I would never lose any data and keep a history of the transitions. Row deletion could be just setting a "deleted" column to true, but would be recoverable. I was looking at HyperTable, an open source implementation of Goog...
I'm working with a bit of a dodgey database at the moment, there are foreign keys defined in all the wrong places all over the place, I'd like to remove them all and then start from scratch. I don't wish to remove the column, just the foreign key relationships. How can I remove all foreign keys from an entire database? (Or table by tabl...
Hi. Sorry for the horrible title, not sure how to explain this. I have a query that looks like this: SELECT `name`, `latitude`, `longitude`, `indoor`, `address`, `phone`, `email`, `website`, `closed` FROM rocks INNER JOIN (SELECT DISTINCT id FROM rock_types WHERE type="DWS" or type="Top rope") AS types ON rocks.id=types.id ...
Hi guys, I'm building a small messaging system like you have in facebook. Only thing is that I want to know whats the best way to get on about it. The database design to be precise in this case. Like all users would have an inbox and sent items folder. Logically something in one persons sent folder would actually be in someone elses inb...
I'm having a bit of a strange problem, I'm trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference. Here is a pict...
In MySQL I can use the RAND() function, is there any alternative in SQLite 3? ...
Hi All, I have one procedure which retrieves all the records from one table which has more than 20000 records and two columns. the column i m trying to retrieve has the index on it. But still it takes more than 1-2 minutes to execute. Can anyone provide any input on this. ...
I'm using mysql there are two tables vote app_id user_id 2 1 3 1 2 2 app id title content 1 xx xxxx 2 yy yyyy 3 zz zzzz I want to sort app table by the number of user's vote . in this example , the result should be id title content 2 yy yyyy 3 zz zzzz 1 xx xxxx any suggestion? ...
Hi, I have three tables, each contain an auto-incrementing PK. I need to select the latest (ie, ORDERBY DESC) entries from the mix of these tables. I'd like to do it in one query, with two joins. My idea was is to somehow select a table, order it by id DESC, then somehow merge the results. Does anyone have a way (or probably a better id...
We recently had an issue I'd never seen before, where, for about 3 hours, one of our Mysql tables got extremely slow. This table holds forum posts, and currently has about one million rows in it. The query that became slow was a very common one in our application: SELECT * FROM `posts` WHERE (`posts`.forum_id = 1) ORDER BY posts.crea...