Using Keys Properly
I have a couple questions on how to properly create keys. Basically i know nothing about them, how they work, how it speeds things up, and most importantly, where to create them to help speed up my tables.
I do have multiple tables that are used and joined together, i just dont know where to make keys. I heard that if you do it improperly, it can slow down your database too?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
CREATE TABLE And Primary Keys And Keys
I am going through Mayank Gandhi's article on "Build An Automated PHP Gallery System In Minutes" partly because I want to do just that and also to learn more SQL. The way he creates a table is with: CREATE TABLE gallery_category ( category_id bigint(20) unsigned NOT NULL auto_increment, category_name varchar(50) NOT NULL default Ɔ', PRIMARY KEY (category_id), KEY category_id (category_id) ) TYPE=MyISAM; The article deals more with PHP, so there is not as much detail in the SQL as I would like. I don't understand why you would use category_id as a PRIMARY KEY and also a KEY. Nor the syntax of the KEY line. That appears redundent to me. I can't find documentation addressing this on the MySQL web site or in the books that I own.
Triggers As Primary Keys And Foreign Keys?
I have created a sequence and then a trigger to create my primary key. I need to connect to the Customer table via the foreign key Customer_id. However, I get an error saying: * ERROR at line 8: ORA-02270: no matching unique or primary key for this column-list This is my code:
UPDATE Will Not Add Properly
I have an online comic engine I'm working on, and I just noticed that, as opposed to adding one view (like it should) it will always add two. Can anyone help me out with this? I've already determined it's just this line of code, and that it's not being called twice. $query = "UPDATE `".SQL_COMICS."` SET comic_views = (comic_views + 1) WHERE comic_id = '".$comic_id."' LIMIT 1"; mysql_query($query) or die(mysql_die_message()); echo($query);
Properly Querying A Relational Db.
I've recently been working with more relational dbs and I was just wondering...am I working with them properly? The reason I ask is because I'm wondering if there are easier/more efficient/more "proper" ways to be working with a relational db. So here is an example: two tables, a project table and an employee table. The project table stores employee id in emp_id and is linked to the employee table by a foreign key (a one to one relation). SELECT P.project_id, E.first_name, E.last_name, P.name, P.code, P.start_date, P.end_date, P.imdb FROM project AS P, employee AS E WHERE E.emp_id = P.supervisor The thing is eventually we'll start to have tables with 4-5 foreign keys and I'm wondering if this is still "proper". Honestly it may seem a stupid question to some of you experts :-p
Getting Utf8 To Work Properly
I have made a few tables in a database, and I'm trying to get my Japanese text to display properly. I have the collation set to utf8_general_ci, and I even added the mysql_query("SET NAMES 'UTF8'"); code into the php page. But even with all of that in, it still won't display properly. The characters show up as セ*チクにんじゃたいけつ and other junk like that. Now this was working before until I added a second mySQL connection into the page, which inputs the second table. I'm not sure if you can make 2 connections on the same page, but I did and it pretty much messed everything up. I took the second table away, and things were still messed up. My question is how can I get the Japanese text to display properly? It does display properly on the phpMyAdmin page, but I get garbage on the actual page.
Search Does Not Match Properly
i had created a search function on a web application. the problem is, i try enter some of info to it, certain text can be found but certain text can be match although they are in database. this my query: $keywordu = strupper($keyword); $keywordl = strlower($keyword); $sql = "SELECT E.* FROM employes AS E, dvi_problem AS DV WHERE AS.empId = DV.empId AND (E.emp_name LIKE '%".$keyWordl."%' OR DV.reference LIKE '".$keywordi."''%".$keywordl."%' OR E.emp_name = '%".$keywordu."%' OR DV.reference LIKE '%".$keywordu."%')" $query = mysql_query($sql, $db) or die('Fail to search.'); i test with direct copy the data from database, some string i copy can search it but some other string i copy direct it can work wo... i try show out the variable $sql value, it show with not error, the keywordu n keywordl aslo show out the value when i print out the $sql variable.
Mysqld Is Not Running Properly
I have installed mysql server & client version 3.23.54a from Redhat installation 9.0. I could install the RPM 's with out any problem. I can see 'mysqld' program . But when I run it shows a message like mysqld started [ok].When I checked 'ps aux' , I could not see any process running with mysqld. Also I could not run any programs like mysqladmin etc. All of them fail. does anybody know what is the mistake ? What should I do ? Also when I try to stop mysqld it fails.
Designing Databases Properly
After what seems like far too much agonizing, I think I've decided to build my own php "shopping cart" and hack my own interface to paypal IPN, because all the other solutions I've come across are massively oversized and overdeveloped for my needs. But I could use some help in setting up the structure. I'm not sure of the best way to handle "options" with my various products. First of course I set up a basic database with product fields like: sku, price, name, etc. etc. The tricky part is that some products will have special options, some dropdowns and some text fields, and I know I won't be able to predict all these in advance, like: item color, accessory color, font, initial, initials ... In addition, some products will call for extra product photos and others won't ... And then I will need some way of storing all these extra options for a given customer's order when it comes through.
Extended-insert Not Working Properly
I am having a problem with using the --extended-insert option when using mysqldump.As I understand it, this option creates multiple insert statements within the .sql file for tables that contain more than 1 row of data. According to documentation I have found, this substantially speeds up the restoration process.The main database I am concerned with uses the InnoDB engine, however I also want to backup the mysql database as well (which, to my knowledge, uses the MyISAM engine). However, when I execute the database dump using this option, there is only 1 insert statement, and all values are seperated by commas.When trying to restore my databases, this causes my system to eventually freeze up due to the inefficiency of inserting multiple rows this way.I am using Windows XP, 1.8 Ghz, 512 MB of RAM. The command I am issuing is as follows: mysqldump --opt --single-transaction --u root --all-databases > "C: estbackup.sql" I have also issued the following command, with the same result: mysqldump --extended-insert --single-transaction --u root --all-databases > "C: estbackup.sql"
Properly Re-starting Replication Process
I've just tried to re-start a replication process and the slave's tables came up marked as crashed. I'm doing a repair on one of them but there are a lot of databases/tables so I can't check them all (or is there a table entry somewhere that flags crashed tables?) Here is the sequence I used: Code:
Storing Large Integers Properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type?
Two Digit Numbers Not Formatting Properly
I have a table I am using to populate drop-down menus. It is for languages. each language has a specific code that must be saved to the field in question.They work fine except for two codes: 00 and 01. I can't change the codes to anything else. They have to be 00 and 01. THey are saving to the spreadsheet as 0 and 1 however.
Inserting Dates Properly Formatted
I have a dataset that has all it's date formatted as MM/DD/YYYY. I want to be able to insert this into a table that is using a field set as 'date'. How would I go about properly formatting the MM/DD/YYYY to insert into the table? Right now when I insert I end up with all the dates storing wrong. IE: 9/6/2005 stores as 2009-06-20.
String Keys Vs. Numeric Keys
I know that using numeric keys is more efficient than string keys but can someone tell me how much more efficient it is. It would be a lot easier for me to use a string as the key, but someone recommended me to use a numeric key.Is it bad practice to use a string key?
Mysqlcheck: Clients Are Using Or Haven't Closed The Table Properly
I'm using the command mysqlcheck --all-databases --auto-repair --silent and I see these warnings (listed below) on a daily occurrence. Is mysqlcheck causing these warnings? I can't see how a table can close inappropriately. How can I prevent this. Hoping to here from MySQL users who have came across this. I'm using MySQL 4.1.11 on RHEL. Code:
Getting Text Retrieved From Mysql To Fit Tables Properly
I have been working on a really simple mysql/php set of pages. Page 1: Input 3 peices of data (Title, Date and Text) Page 2: Display all news that has been inputted I have it working, except for one problem. My website is built with html, and tables. I want it so that when the text gets to the edge of the table, it drops down a line and starts again on the next line - so it doesn't carry on and on. Theres no problem with title and date - they're just a few words, but the text part of the news is usually 5 or 6 lines long. This means that it wont all fit on one line without ruining the entire website display. Code:
Keys
Do the Primary key and foriegn keys have to share the same column name under mysql?
Keys
I have two columns: patientID and QSCODE. I want to add and update records. Sometimes patientID will be NULL and sometimes QSCODE will be NULL, but never both. I want to be able to update the QSCODE based upon the patientID. What should I do about keys? I'm confused... I'd like to use patientID as my key, which is normally a randomly generated value. Is there a way for me to generate this on the fly?
Do I Really Need Foreign Keys
I am currently designing a database that is to be used for ordering products from a site. member, order-states, order, order-lines, product, review I am considering using foreign keys between the tables. I am not sure if this would be the best way to do it or not. Q1: If I use fk which tables need to be innodb, the ones containing the fk, the ones the fk are from or all of them. Q2: If I were to delete a member do all related table entries get removed or does that only happen with the cascade keyword. This is important as you would still need a record of the transactions in the orders table. Q3: What benefits are there to fk using innodb over myisam and simply storing the value from one table as a field in the other. Hope these questions aren't to trivial and feel free to point me to tutorials. Thanks.
Foriegn Keys
In a Mysql database Say I had two tables <news> and <pictures> and I wanted to hold the Picture_id from the pictures table as a foreign key in the news table how would this be done? Can anyone point me in the direction of any tutorials?
Aggregate Keys
CREATE TABLE `tbl_vel_product` ( `family_id` varchar(25) NOT NULL default '', `catagory_id` varchar(25) NOT NULL default '', `colour_id` varchar(25) NOT NULL default '', `price` int(11) NOT NULL, `qty_in_stock` int(25) NOT NULL, PRIMARY KEY (`family_id`,`catagory_id`,`colour_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Where my primary key is several foreign keys from other tables. I have a webform that returns this product key and I wish to search for the product with that key.. How do I construct a query of the form.. SELECT * from tbl products WHERE [family_id, catagory_id, colour_id] = [URL_PARAMETER]...?
Primary Keys
Just a quick question: does it affect performance (efficiency, etc.) at all when using a table that does not have any of fields set as the "primary key"? I'm wondering mostly because I have some database tables that don't really need a primary key at all and space-wise I would probably benefit from leaving them off, but I have no idea if somewhere that can have a negative impact.
Addition In Db Using Three Keys
I have three variables defined in the db as: MonthCode CHAR(1) DateCode CHAR(2) TimeCode CHAR(1) Other variables in the db include prices and other numbers. I need to sort with these three variables concatenated as if they were a search string and then add up the prices and other numbers for this unique group: i.e. MonthCode.DateCode.TimeCode MonthCode DateCode TimeCode M 30 A M 30 D (= not the same unique grouping as above) A 30 D (= not the same unique grouping as above) The result then must be: M30A .....sum of all variables using an expression SUM('Price') M30D .....similar sum as above but probably different result A30D .....similar sum as above but probably different result I have used the SUM('Price') in the query statement succesfully but it adds up individually: M30A = xxx M30A = www (this sum and the one above should be added together) A30D = zzz
Foreign Keys - To Use Or Not To Use?
I'm developing a large app with more than 100 tables and expecting very large traffic. Performance is an issue for me. According to the MySQL doc, using Foreign Keys in InnoDB (which I'm using) will affect performance. I'm wondering how much does performance suffer and is it worth it to NOT use FKs and let the app code handle the table dependencies at the risk of getting orphaned records?
Foreign Keys
I have a table called Offer, where the primary key is ID number and there are two foreign keys - Item ID and Member ID (these are the primary keys from the Item table and Member ID). When I insert data into all the tables, how should I enter it so the foreign keys get the data from the Item and Member tables so it appears in the Offer table.
Foreign Keys
I'm reading the documentation on foreign keys for MySQL: Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order. The above quote baffles me. What in the world are they talking about? What do they mean by "first columns" and what do they mean by "same order"?
Primary Keys
I have two problematic tables, proj and bgroups. At this point, both table have primary keys that are "id" fields. And bgroups table contains foreign key from proj table, which is accordingly the id field in proj table. It's a cascading relationship. So I also have projnum field in proj table and bgroup field in bgroups table, both are numbers. I need to do something (maybe make them primary keys?) to them so that there couldn't be a record in my database that would have a duplicate combination of data from the two fields. Meaning the data is allowed to be duplicated in both tables, but couldn't create a record in proj table if there is already a record with the combo of projnum and bgroup numbers. How can I do this ? - is this something that could be done to via few sql commands or do I need to write code to check for the combo before attempting to write to database? My database has many tables and most of them depend on the current relationship described above; I wouldn't want to rebuild it all again...
Problems With Keys
In a quite extensive attempt to change the charset on something like 50 columns over 20 tables, I have run in to a problem. All data is actually stored in utf8 but mysql thinks it is latin1. So I follow the recommended approach by creating a PHP script on my laptop that does all the tricks for me, so it will be a safe and quick change on the live server when I am set. The conversion looks essentially like:
Foreign Keys
Is there a way to view all foreign keys in a table?
Primary Keys
How is it that even though I have the column "username" in my database set as a Primary key, using my PHP script to add new users to the database works without any errors even when signing up using an existing username. I have a database full of the same usernames!
Using Foreign Keys
I'm experimenting with a few tables that rely on mutual ID keys (ie - for orders table, retrieve customer details from customer table, based on customerID key available in both). I've noticed that in the Query Browser tool, when creating a new table there's a place to define foreign keys (in the Table Editor GUI). How might I put this to good use and where can I find more reference on the issue?
Foreign Keys ....
I know how to create tables and databses, but I'm not familiar with using foreign keys. I have a MySQL book, but it wasn't very clear on how to set up foreign keys...Can someone give me a a quick example? I'm working on a contact list at the moment...I'm planning on having person_id, First_Name, Last_Name in one table and Address, Phone, etc in another...I know I'd probably link the two by the person_id....
Foreign Keys In SQL
I would like to know starting from which version of MySQL are Foreign Keys supported. Furthermore, I would like to know starting from which version of MySQL they work without any errors. And, at last, if you know a free webhosting provider which offers that version of MySQL.
Foreign Keys
I have the following problem: I want to create a table containing at least one foreign key... I learned how to do it at my university, but it doesn't run... ? what is the right syntax... bonus: how can I manage this when altering a table?
Foreign Keys
I have a database which has many tables, but my question relates to these 4 tables: USER, CONTACT, PHONE, EVENT. USER has a 1 to many relationship with CONTACT. CONTACT in turn has 1 to many relationship with 2 tables PHONE and EVENT. PK of CONTACT is supposed to be the FK of both EVENT and PHONE. I can create the relationship successfully between CONTACT and either one of these tables but not both. If I make the relationship between, say PHONE, then I cannot create the FK in EVENT and vice-versa. I get Error 1005 and 150. I have also pasted the error report at the end. Code:
Searching On Two Keys With OR?
Which version is targetted for optimization of OR searching on two keys, that is, "select * from sometable where f1 = 123 or f2 = 123", as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html ?
Foreign Keys
I am using MYSQL 4. I understand that it allows for foreign keys. Could someone show me an example of how to declare a foriegn key. I tried a combination of statements but I always got a syntax error. Here is what I am trying for example ... Create table x ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, whatever int )Type=Innodb; Create table y ( IDTr INT NOT NULL, constraint FOREIGN KEY IDTr REFERENCES x(ID) )Type=Innodb; How would I create a foreign key linking IDTr to x(ID)?
Foreign Keys
I need to use foreign keys in a mysql database.But I don't know wich versions and wich engines support foreign key.
Foreigns Keys
I'll be doing only INSERT and SELECT. I might occasionally do a manual DELETE of a row or two. Is it worth it to use INNODB in order to set foreign keys? I can use multiple-table DELETE for the rare occasions when I want to delete. So are foreign keys worth it? Or should I just go with MyISAM to keep things simple and more portable?
Foreign Keys ?
what is the real benefit of using a foreign key? Let's say I have 2 tables, one with references to categories. my_categories id | name 1 | sun 2 | rain 3 | wind my_info id | name | cid 1 | Mt. Doom | 2 2 | Dodo Hills | 2 3 | Balaba desert | 3 4 | Tilula plains | 1 I can join two tables using: SELECT A.*, B.name as category FROM my_table A LEFT OUTER JOIN my_ref_table B ON (A.cid = B.id) resulting in this display: 1 | Mt. Doom | rain 2 | Dodo Hills | rain 3 | Balaba desert | wind 4 | Tilula plains | sun In this case, where does the use of a foreign key would improve a search?
Searching On Two Keys With OR
Which version is targetted for optimization of OR searching on two keys, that is, "select * from sometable where f1 = 123 or f2 = 123",
Primary Keys
How is it that even though I have the column "username" in my database set as a Primary key,using my PHP script to add new users to the database works without any errors even when signing up using an existing username.I have a database full of the same usernames.
Question About Keys
Would it be "normal" for a primary key to exist as a foreign key in multiple tables at once?
Foreign Keys
I'm struggling to get foreign keys to work successfully on a script to create a particular database. I have all the tables concerned set to TYPE=INNODB. I think my problem is with the indexes that need to be applied and their ordering. My table needs to take two foreign keys, both of which are primary keys in the referenced table.
Foreign Keys
can you have a foreign key in more than one table. Ie. could I put a MechanicID (which is the key field of the Mechanictbl) into tblJob and tblOrders?
Foreign Keys
Here is what I am trying for example ... Create table x ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, whatever int )Type=Innodb; Create table y ( IDTr INT NOT NULL, constraint FOREIGN KEY IDTr REFERENCES x(ID) )Type=Innodb; How would I create a foreign key linking IDTr to x(ID)?
Foreign Keys
I'm designing a database and have gotten a little stuck on the use of foreign keys. I have two tables: Manuscript and Page. A Manuscript has one or more pages, so there is a one-to-many relationship there. My question is this - do I need to have both a foreign key in Page that references Manuscript AND vice versa, or can I get away with just using one? Is there any reason I would want to have it go in both directions?
|
TRACKER
