Apologies in advance if this is a silly question but I'm wondering which might be faster/better in the following simplified scenario... I've got registered users (in a users table) and I've got countries (in a countries table) roughly as follows: USERS TABLE: user_id (PK, INT) | country_id (FK, TINYINT) | other user-related fields... ...
Hi all, I'm planning to generate a huge amount of data, which a I'd like to store in a MySQL database. My current estimations point to four thousand million rows in the main table (only two columns, one of them indexed). Two questions here: 1) is this possible? and more specifically: 2) Will such table be efficiently usable? t...
I have the following SQL query: SELECT * FROM table WHERE field_1 <> field_2 Which is the best index structure to use, to keep this query efficient: two indexes on field_1 and field_2 or a single index which includes both fields? EDIT: The database is MySQL ...
I have a simple insert select which insert _TABLE_B_ data in _TABLE_A_ new row INSERT INTO _TABLE_A_(_USERNAME_,_ID_) SELECT _USERNAME_,_ID_ FROM _TABLE_B_ I want to insert a row in a table named _TABLE_C_ each time i insert a row in _TABLE_A_ and add the current inserted _TABLE_C_ id in _TABLE_A_. i'll try to explain it in an ot...
I recently got a reply from a server company asking if we are using an ORM in our application which does all the work of sifting application side (like Rails) or if we write reams of SQL, embedded functions etc which would make the database server do the processing for you. Can anyone explain what is meant by this. Our web application ...
I have to calculate a price based on a rate structure along these lines: $303.00 fixed price up to 500 units $0.023 additional per unit from 501-10,000 units $0.022 additional per unit from 10,001-25,000 units $0.021 additional per unit from 25,001-50,000 units I'm a little lost on setting up a database structure and algorithm (the la...
So I have a huge list of entries in a DB (MySql) I'm using Python and Django in the creation of my web application. This is the base Django model I'm using: class DJ(models.Model): alias = models.CharField(max_length=255) #other fields... In my DB I have now duplicates eg. Above & Beyond, Above and Beyond, Above Beyond, ...
What do these mean in MySQL? I'm using MySQL workbench and I see I can specify a new column as these... (I noted my guesses) PK – (primary key) NN – (non null) BIN – (binary?) UN – (Unicode?) ZF- (???) AI – (autoincrement?) Is AI exactly like an Identity specification in MSSQL? (Can I insert a record without specifying the value and ...
I a function that used a mySQL Data Adapter and inserts a new row. I want the function to return the value of the auto-incremented column of the updated row. Please help. Public Function addRow(ByVal colA as String, ByVal colB as String) As Integer Dim dbConnection As New MySqlConnection("server=xxx; user id=xxx; password=xxx; dat...
I currently have replication setup on MySQL 4.1 (master, named radius) to MySQL 5.0 (slave, named app1). We got a new server and I installed MySQL 5.1 (named app2). I setup app2 to replicate data from app1. If I manually run any queries on app1 they are replicated perfectly to app2, but any data being replicated on app1 coming from rad...
I have some text stored in MySQL database like this "Tom gave Sarah £5 for her book". Up until last Monday this displayed on the site perfectly when pulled from the site, showing "Tom gave Sarah £5 for her book". Now, even though I have not changed/installed/uninstalled/updated anything at all, it has stopped working. The old values in...
I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+...
Hi, I'm currently trying to write a query that will return all users that have not logged time for a given date in our timesheet system. We currently have 2 tables, timesheets and users. I am trying to make a query that will return a list of users that do not have an entry in the timesheets table for a date range. There is only one reco...
Hi there, I've 2 computers connected to Internet. Each one has Xampp installed, with a local mysql database. Is it possible to sychronize these 2 dbs ? I use Navicat. The sync works well with a local database to a server database, using tunneling. But what about the sync between 2 PCs dbs ? Thank you very much for your help, Cheer...
I would like to take surveys (2 or more choices), and place the results in a table. I just want to know how I would tally up the results in a sql query. Even more, should i be doing the math within the query, or just use PHP to do the math? Example Questions Table question_id (int) question (text) answer_1 (varchar) answer_2 (varchar...
What I am trying to do is to add data for a product into a database with MySQL 5.1 and PHP 5.2.12. I have a table for general fields: name, description, price, number of times purchased, and also I have a table for the images that are associated with that product. The goal is to have the same id for every image that corresponds to its...
While learning mysql, I read that you can perform the following statement when adding a column to a mysql table: ALTER TABLE contacts ADD email VARCHAR(60) AFTER name; or ALTER TABLE contacts ADD email VARCHAR(60) FIRST; When would you want to do this? Can column order be utilized for query optimization purposes? Should longblobs b...
Hi All, I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table. What's the bext way of doing this? Many thanks ...
I'm looking into stored procedures at the moment. According to this article (page 8) in the dev section of the mysql website... Ordinarily, it's not normal to put SELECT statements in stored procedures, this is for illustration. I decided that some procedure should simply select from our table, so that when you call the pr...
We have a product developed in C#.Net. It is not a web application but a desktop version. It connects to various databases like MSSQL, MySQL etc. We keep on upgrading the versions of the software for bug fixes as well as new functionality. These updates changes the exe as well as dll used and the database also. We would like to know wh...