Creating An Efficient Database
i'm currently writing a web based catalogue system in php using a mysql database.
the catalogue has a number of products in it from different brands.
i would like to know if it is more efficient to have each brand in a separate table then a "master" table just listing the brand name and corresponding table
or
all the products in one large table and a "master" table listing each brand in the large table. the large product table would of course have a field to state which brand the product was from.
the efficiency would be based on users being able to access the database via html browsers using php and also search the database.
View Complete Forum Thread with Replies
Related Forum Messages:
Efficient Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created seperate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?
View Replies !
Creating Database
I just rebuilt my server with Windows 2003 server and I am trying to create a MYSQL database with no success. The server specs are: Windows 2003 Server (IIS6) PHP - version 4.3.10-win32 MYSQL - version 4.0.23 The show databases command shows mysql and test.The command create database forums brings me to a -> prompt but doesn't say that it did anything? The command grant all on forums.* to EGeorge@localhost inentified by password brings me the the same -> prompt but also gives me an hourglass that stays there forever until I kill the process.
View Replies !
Creating Database?
I have mysql installed and running. i want to run a phbb on my server. these are the instructions i am using. i create the databse all right bu when i get to grant command i get syntax error near 'INDENTIFIED BY 'PASSWORD' WITH GRANT OPTION'. I type in GRANT ALL PRIVILEGES ON gators. TO 'hjscm'@'localhost' INDENTIFIED BY 'PASSWORD' WITH GRANT OPTION; [root@axentraserver adminuser]# mysql -u root -p mysql Enter password: (enter the password from oe-mysql.conf here) [...] Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> CREATE DATABASE db_name; Query OK, 1 row affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost' -> IDENTIFIED BY 'db_pass' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> exit
View Replies !
Creating The First Database
I don't use PHP but .net , but I think you can create a database and colomns directly with a control center ? I have installled MySQL 4 and MySQL Control Center 0.6.3 beta + the ODBC i have started winmysqladmin and enter user = rene / password = rene at the first launch now I open MySQL control center .... connect to the server on the tab general I enter Name = rene Host = localhost User = rene password = rene then I test and get an error >> the memory cannot be read this programm will close i am trying to create a database with mySQL since one week anyone knows how it is normally possible ?
View Replies !
Creating A Database?
I've installed MySQL (5.1.30) on my PC using Xampp (version 1.7.0). I haven't created a log in or username, or anything like that. I can access MySQL to look at databases and work on existing databases, but I can't create a new database. When I go to the mysql> prompt and type; Code: CREATE DATABASE ijdb; I get the following error; Quote: ERROR 1044 <42000>: Access denied for user ''@'localhost' to database 'ijdb' I've also typed every variation I could think of, of the following at the mysql> prompt; Quote: mysql -h localhost -u root -p and everytime I do it I'm given the -> prompt, as if MySQL is waiting for more code. When all this didn't work, I opened phpMyAdmin (3.1.1) to check that there is a user called "root" with full privileges - there is. I also created a password for this user and entered it into Quote: mysql -h localhost -u root -p but I still get the ->. What do I need to do so that I can create a DataBase?
View Replies !
Creating A MySQL Database?
I'm a complete newbie with MySQL and only really started trying to learn about it today but i'm confused as to how i'm supposed to create a MySQL database. I tried downloading the MySQL Administrator as it sounded like the right program to create and edit a MySQL database but it seems to want a server to connect to or something. I was told i would be able to create and edit MySQL databases on my local system and then upload them to my web host when i get one, is this possible and if so can someone give me some idea of how and what programs/runtimes/whatever i will require in order to do it.
View Replies !
Creating Database Through Hibernate
If i first create this database manually everything is fine. But for testing and production this database needs to be able to be dropped and created at startup. My configurations are as follows: <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost/testdb"/> <property name="username" value="root"/> <property name="password" value="pass"/> </bean> <bean id="sessionFactory" parent="baseSessionFactory"> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop> <prop key="hibernate.hbm2ddl.auto">update</prop> <prop key="hibernate.show_sql">false</prop> </props> </property> <property name="mappingResources"> <list> <value>com/irise/user/User.hbm.xml</value> <value>com/irise/project/Project.hbm.xml</value> <value>com/irise/group/Group.hbm.xml</value> </list> </property> </bean>
View Replies !
Creating A Test Database
Go to Windows Start menu, choose Run, type: c:Program FilesMySQLMySQL Server 5.0inmysqld-nt.exe After I type that, it displays the command prompt for about 10 seconds, then flashes away. Now, it tells me I am ready to create a test databse using the "mysqladmin" It says, Go to Windows Start Menu, Choose Run, type: c:Program FilesMySQLMySQL server 5.0mysqladmin" -u root -p create testDB; ...then hit enter. It should prompt me to enter a password, I get an error saying I might have typed something wrong. Take a look at the line it tells me to enter. I notice only ONE (").
View Replies !
Creating A Variable Database
I'm making an application that will show a user an html form (using php and mysql). The form is created by that person's manager, which will have custom fields on it. There will be roughly 20 managers, and each manager may have different forms, but some of the fields will be the same (like ID, name, etc). What i've done so far is created 4 tables, items (which are the field names on each of the forms), ItemsSelect(which handles any select html), User(which just has the userid and accesslevel in it, plus 'userfieldstoshow' which tells that user which fields they'll be seeing', and ItemUser, which tells exactly what userid links with each itemid. I probably could have explained that better, but it just basically creates a dynamic form each time a new person loads the page, which shows them which fields to fill out. The PROBLEM is here is i can't figure out which way to store the data. I'm starting with 1 table, and each of the columns will be labeled something arbitrary like 'field 1', 'field2', and that will link to the itemid, and store all the data for which item is in that table. Things that are common I will make, like ID, name, phone, what have you, but I can't decide if I should have a finite amount of fields to be allowed to added (as there will be 20 managers, each with X number of custom fields).
View Replies !
Creating MySQL Database
I'm developing an e-commerce site and have got this for my database: CREATE TABLE `products` ( `prodnum` varchar(10) NOT NULL default '', `name` varchar(50) NOT NULL default '', `category` text NOT NULL, `image` varchar(255) NOT NULL default '', `description` text NOT NULL, `price` text NOT NULL, `dateadded` timestamp(14) NOT NULL ) TYPE=MyISAM; What I want is to create an admin section where I can add a new Product using a PHP page with a form - add product number, name, description, product image, price and date added. Then I want to be able to specify which Product Category the product is added to using a Drop Don box in the form. Is my SQL correct to do this, or should Product Category be a separate table? How do I get the form to post the data into the correct category? It works OK as above except for displaying the product in the correct category. Also, the date does not display on the page correctly. It comes out on the page as 20050602014511.
View Replies !
Creating A Database If Not Exists
I have just started with MySQL and i have question about creating a database. I want to create a database only if it is not already created. So is there some sql that i can write like there is for creating a table "create temp table if not exsist" I would like to write something like "create database temp if not exsist" or is that not possible whene creating a database. If not why?
View Replies !
Creating A Database Of Pictures
I want to setup a database for my pictures on my site so that people can access the database and retrieve pictures from the database instead of me placeing them all on the page which would make my page huge.
View Replies !
Creating A MySQL Database
I've been trying to create a mysql database on my school's server to no avail. It seems as if I need a root user to do it for me; I'm wondering if there is such a way to get around being a root user.
View Replies !
Stopping Anyone From Creating A Database.
How can I stop people from creating a database on my MySQL server? eg if you used PhpMyAdmin you can access my server on my computer by changing the host name variable to my IP address. How can I stop that from happening so that only I can create databases.
View Replies !
Creating A Database :: Don't Have Permissions
I put the commands in the terminal. mysql -h localhost -u metsuo -p Enter password: ******* then I get to where it shows "mysql>" then I type CREATE DATABASE shinko; and it says I don't have permisson as '@localhost' and I'm in root. Should I give write access to the directory?
View Replies !
Creating Relational Database And Normalization
I need help with creating a relational database and normalizing it. I've been studying the process of creating a relational database, however I am unsure if I am doing it correctly. Below is a sample list I have created (on paper)of my database design. Can someone review and let me know if I am doing everything correctly and give me suggestions for improvement? Code:
View Replies !
Creating Mysql Database On My Machine
I got complete application files and database files which is in the format of as .sql and i need to recreate this application on my matchine i have already setup application on my iis server but when it comes to setup the mysql database from those .sql files....
View Replies !
Error 1044 Creating Database
I try to add a new schema to my database (MySQL 5.0.11). No matter what I do, I always getting error 1044: access denied for user 'root'@'%' to database 'test' I did it in this way: mysql -u root -p password: ************ Welcome (.....) mysql> create database test; ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'test' mysql> Using tools like MySQL Administrator or SQLyog showing me, that the root user has all rights granted to all tables, but all programs are not able to create a new database.
View Replies !
Creating User Login With Database
I'm fairly new to using databases and I am wondering how you go about creating a user login with your database. I need to create a login system for a website. Would you use a CREATE USER statement every time you want to add a user, or is that a way for having developers access the database only? I also thought of creating a user table that would contain the users name and password, but how secure would that be?
View Replies !
Creating A User For Only One Database For The Web/ FORGET IT
If I wanted to create a user for only my website to only have access to one database and they will be connecting via php, what is the best way? I should manually insert into the users table, correct? INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); This is the example from mysql docs, but the whole page is huge and seems complicated. Will this mean that only a user coming from the local host will get in this way? And where would I specify the database? I'd like to have just php connecting to the database asside from root and that user only accessing one database to do inserts/updates/deletes and selects.
View Replies !
Creating A Database For An Organisational Chart
I'm considering making my organisation's organisational chart dynamic using PHP and MySQL. I'm just thinking about how to implement the database... The chart resemblies something like... ----------------------------Department ------------------------------Director -----------Section1--Section2--Section3--Section4 -------------HOS--------HOS--------HOS--------HOS SubSection1-SubSection2 ----Staff--------------Staff ----Staff ----Staff etc... where HOS is Head of section. If I had a table with all the section names (inc department name) with a forgein key reference to a table called levels so we can identify what type of section it is - e.g level 1 - Department, Level 2 - Section. How could I express in the database that SubSection2 is under Section1?
View Replies !
Granting Acces And Creating Database
here's a grant statement. GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON $dbname.* TO '$host' IDENTIFIED BY '$passwrod' I copied it from the mysql manual so it "should" be correct but when i run it i get this error: Access denied for user: 'majdkgf_majdgfx@localhost' to database 'test4' Now the problem is, if i create the database first, i need the user set, and if I put the grant before creating the database, i need the database.
View Replies !
Creating A Database Named Login
I am logged into mysql as root and i typed this: mysql> CREATE DATABASE login database; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1 mysql> CREATE DATABASE login database -> As you see I get a error when tryign to create a database called "login database" This is my first tiem creating a database so i dont know how to fix it maybe you guys do?
View Replies !
Creating A Database, Doesn't Appear In Left Hand Panel
I have access to my phpMyAdmin control panel. On the left hand side of the split screen it says "home" and a drop down box with existing databases listed. In the right hand side of the screen are links to MySql documentation. I can't find a box to run the command. CREATE DATABASE mydatabase_db; ???? If I select an existing database from the drop down menu on the left, I get the table contents of that database in the right hand side of the screen and below that a box to run the Mysql statements in, will running a create statement in the box interfere with the database I just chose ?? Secondly if I create a new database how do I make it appear in the drop down list of databases on the left ? I have run a create database statemen and it has said database created successfully but it doesn't appear in the drop down box on the left ?
View Replies !
Creating / Managing Database(s) Cross Platform
Is it possible to create / edit database(s) in my iBook laptop and copy the database files straight back to the Windows 2000 server? Coz I wanna do the design and testings in my laptop and 'migrate' it to the server once it's finsihed debugging.
View Replies !
Creating First Database On Mysql 4.0.21 Apache 2.0.52 Windows Xp
started mysqladmin 1.4 then i started mysql monitor by clicking on it in the mysql folder and tried to create a database like this mysql> create database testdb; mysql>got error error 1044:access denied for user: '@localhost' to database 'testdb' do i need to set up a user or is there like a default user sign in i need to do? or some kind of configuration i have missed?
View Replies !
What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be. Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content". Field Type Null Default post_id int(10) No post_date datetime No 0000-00-00 00:00:00 post_text text No post_title text No post_cat_id int(4) No 0 post_description text No post_name varchar(200) No post_type int(4) No 0
View Replies !
How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL. select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n); assuming: id is an indexed field. table is a VERY big table (100 000+ - 1 million+ records) what do you think is the largest number of values you could pass to the in() function without completly flattening your server?
View Replies !
Most Efficient YES/NO...
I) Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A. II) Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields. I'm looking for the query that will be fastest and most elegant to implement.
View Replies !
Quickest And Most Efficient
i run an online game. I want to give each user a ranking, based on how high thier score is.If for example, i had 20,000 players, i dont want to have to update them all one by one, it may strain the server and take a long time. Is there another way i can assign a rank number (rank 1 has the top score and so on)
View Replies !
Efficient UPDATE
I have a table with the following structure; CREATE TABLE my_table ( id_1 int(11) NOT NULL , id_2 int(10) NOT NULL , stauts tinyint(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id_1) ) Engine =InnoDB'; The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query; UPDATE IGNORE my_table SET id_1 = 74240, id_2 = 5 I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.
View Replies !
Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation. On a typical "route" there could be hundreds of points. My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route". Sample data is along the lines of : - <trkpt lat="54.016942977905273" lon="-1.4903640747070313"> <ele>82.330078125</ele> <time>2006-09-03T07:35:41Z</time> </trkpt> - <trkpt lat="54.016938870772719" lon="-1.490332055836916"> <ele>0</ele> <time>2006-09-03T07:35:42Z</time> </trkpt>
View Replies !
Efficient Storage Of IP Address
I am establishing a database for the purpose of logging access to my secure webserver and am wanting to make the database as efficient as I can because it will be doing a lot of work when the site goes live. What is the most efficient way in a MySQL table to store remote IP addresses? What data type should I use? Should I just go with a basic VARCHAR(15) to allow for 4 sets of 3 digits with 3 decimal separators, or is there a better way?
View Replies !
Efficient Placement Of Fields
is there any noticable efficiency is ordering the position of the field types?. Like say i place the join keys in a table at the end, and varchars and text at the top of the table does it really matter? Also when i do a query like select * from table where id IN (1), where 1 is usually a primary key int is that quicker than doing where id=1 or is there no difference and is it handling the int as a string or as an int?
View Replies !
How To Make Index Most Efficient
I have a large table (> 3,000,000 records). Each records contain a primary key like 'id' and a lots of attributes like 'age', 'department'. I want to build some indices to accelerate my query. I read the document which says that too many indices may slow down the INSERT and UPDATE operations. So is there any rule on how to set indices in such table? If I create an index for every field, would that be a very bad idea? If I create an index on each of two fields but not on their combination, will the indices contribute to queries on the combination?
View Replies !
Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer. I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.
View Replies !
Efficient Way Of Mass Indexing?
I have been working on a program that will populate and index a database. The populating doesn't take too long, but the indexing does. My question is: Is there a better approach to indexing this table than the one I'm using right now? I'm doing this through QtSql. headers is a QString array of 48 column names I want to index. for (int i = 0; i < headers.size(); ++i) { q.exec("ALTER TABLE mysqltable ADD INDEX(" + headers[i] + ");"); } example run for 20000 rows: viper,david $ time ./main -r 20000 Database Connection Established 0.520u 0.060s 0:21.27 2.7% 0+0k 0+0io 0pf+0w
View Replies !
Is This INSERT With SELECT As Efficient As Possible?
I'm writing a pretty complex web app and will be repeating many times over a query very similar to below and need to know if there is a more efficient way to do it. If anyone has input, I'd be happy to hear: INSERT INTO table (somecolumn) VALUES ((SELECT id FROM other_table WHERE foo = 'bar'))
View Replies !
Is This An Efficient MySQL Setup?
I would like to offload the MySQL server from my dedicated box in order to speed up page loads. I don't have an additional dedicated server so my only option is to get a VPS. But is offloading the MySQL server to a VPS, albeit a modest one, even worthwhile? I understand this is a very broad question because I am not providing any details but that being the case I am expecting a broad answer
View Replies !
Efficient Way To Left Join?
What is the most efficient way to do a table join, but even if there is no matching foreign key, still return the table on the left. SELECT col, col2, COUNT(col3) FROM tbl1 t1 LEFT JOIN tbl2 t2 ON t1.id = t2.id GROUP BY t1.id But this only works if there is a matching foreign key on t2, I would like all t1 rows to return regardless of whether they exist on t2 or not.
View Replies !
Most Efficient Way To Extract Limited Data
I am currently using the following code, is it the most efficient way to extract and sort the 6 items from the database. The database currently holds over 2,500,000 rows and I want to extract the data as efficiently and quickly as possible. Code:
View Replies !
|
TRACKER
