This question is like a continuation of my previous question: http://stackoverflow.com/questions/1722155/am-i-right-that-innodb-is-better-for-frequent-concurrent-updates-and-inserts-than/ But this time I have concrete questions. We know that MyISAM is faster than InnoDb when we don't have many concurrent updates (inserts). When we have ...
Hi, I was hoping someone could help with a complicated date range SQL query. I need to find a whole bunch of records that could be: 1) within the searched date range 2) outside of the searched range but run through it 3) outside left of the searched range, starting before but ending during it 4) outside right of the searched range, star...
Which one of those will take the least amount of time? 1. q = 'SELECT * FROM table;' result = db->query(q) while(row = mysql_fetch_assoc(result)) { userName = row['userName']; userPassword = row['userPassword']; if (enteredN==userName && enteredP==userPassword) return true; return false; } 2. q = 'select * from table where userName=...
In a certain TABLE, I have a VARTEXT field which includes comma-separated values of country codes. The field is named cc_list. Typical entries look like the following: 'DE,US,IE,GB' 'IT,CA,US,FR,BE' Now given a country code, I want to be able to efficiently find which records include that country. Obviously there's no point in indexi...
I need to connect to a remote mysql database in PHP. Specifically I have this as a constant: define("DATABASE_SERVER", "localhost"); ... $db = @mysql_connect(DB_SERVER, DB_USER, DB_PASS); I want to copy the site to my local machine but still connect to the main database. Can I do this? if so, how? All I have is FTP access. ...
Hi there, I've got a large table (~10,000) and I need one column to take up exactly three spaces. It almost always only takes up one space, but I need the other two spaces to be filled in with zeros (it's an integer column). is there a function for that? ...
What is the best way to emulate Tagged union in databases? I'm talking about something like this: create table t1 { vehicle_id INTEGER NOT NULL REFERENCES car(id) OR motor(id) -- not valid ... } where vehicle_id would be id in car table OR motor table, and it would know which. (assume that motor and car tables have nothing in com...
I'm creating a script that'll work on a Lighttpd, PHP5 and MySql5. The point is, it must be full-offline script installed and configured with all the requirements by one installer. It's a bit modified wiki thing. How to do a such thing? ...
I know what they are my question is, how do you link them or are they automatically linked when you have identical names in different tables. Here is an example: Say I have an [orders] table and a [customer] table. Each row in the [orders] table has a customer_id number which is associated with the customer_id in the [customer] table....
Note the table below. I am wanting to set the default value for the newly created BEST_SELLER column to "N". How do I go about doing that? Create Table Mystery (Book_Code Char(4) Primary Key, Title Varchar2(40), Publisher_Code Char(2), Price Number(4,2)) ...
Edit: This may be a 5.0-specific bug. (I'm on 5.0.83). Removing the innodb_log_file_size setting gets rid of the problem. Which makes complete sense. Not. Googling about finds a handful of similar, but not identical problems in 5.0 that were patched later. With the "wrong" settings in my.cnf, this create statement on MySQL 5.0 will...
Setting up a new database that has a comments table. I've been told to expect this table to get extremely large. I'm wondering if there is any particular reason why I wouldn't want to keep this table in the same database as the rest of the data for the site. ...
We have a client that needs to set up N local databases, each one containing one site's data, and then have a master corporate database containing the union of all N databases. Changes in an individual site database need to be propagated to the master database, and changes in the master database need to be propagated to the appropriate ...
How do I set the isolation level of a transaction to 'SERIALIZABLE' in PHP using mysqli? I have looked everywhere and I can't find any information on it. Here is an explanation of the isolation levels. ...
All, I have a time value being stored in a database as a varchar(4) and I need to convert it to real time. for example, if the time is "23:59" I want 11:59PM returned. The problem is that there is no ":" between the hours and minutes. So when I run this query SELECT TIME_FORMAT('2359', '%h:%i'); -- 12:23, wtf?? However if I...
Let's say I have a query like this- SELECT * FROM table_name WHERE venue = "1" OR venue = "10" That's great, works fine. But let's say I have a list of 50 venue numbers. Is there an easy way to do this where I don't have to write out 50 different ORs? Thanks! ...
0/1 can be flexible and can add options such as "2, 3, 4" in the future. Does TinyINT in MySQL take up more space than boolean? Personally, I use 0 and 1 for everything. You can answer this question in the context of regular programming (or mysql, whichever you like) ...
Hi Im trying to create this stored procedure: CREATE PROCEDURE GetExif ( IN @album VARCHAR(50), IN @pic VARCHAR(50) ) BEGIN SELECT CAMERA, FSTOP, EXPOSURE, ISO, FOCALLENGHT, DATETAKEN, GPSLATH, GPSLONG FROM EXIF WHERE ALBUMNAME = @album AND PICTURENAME = @pic END I get this error #1064 - You have an error in your SQL syn...
Hello.I have this real weird exception when issuing a basic SELECT, using MySQL 5.1.32-community from JDK 1.6.0_16 code. The conditions to repeat : at any place in my app, if i change the date of my OS (WindowsXP SP3) and after that hit some refresh button, that is reloading info from database, while doing this multiple times (usually 2-...
I want to create a database with 3 tables. One for posts and one for tags and one that links posts to tags with the post_id and tag_id functioning as foreign key references. Can you explain what an Index would be in this scenario and how it differs from a Foreign Key and how that impacts my database design? ...