Creating An Optimal Query
What I am doing at present is displaying a list of topics such as on a forum main page. That data is in a table say "my_topics" for example
The list I am displaying also references data in another table say "my_images". Now each item in my_topics could reference zero or more items from my_images and they would need to be displayed alongside that topic. So each item in my_images has an id which is an index into the my_topics table.
At present I am dumping the entire my_images table into an array so I can access it without having to do multiple sql queries. I can't use "left join" as far as I know because the relationship between my_topics and my_images is one-to-many rather than one-to-one.Obvious downside is that as my_images table gets larger - dumping it to an array increases the memory usage of the script which eventually leads to it not working.
View Complete Forum Thread with Replies
Related Forum Messages:
Optimal Table Layout
A user can submit a document with up to 20 keywords. Another user will type a query to search each document's keywords. My first idea is to create a table with 20 varchar fields and do a LIKE query on all the fields (WHERE f1 LIKE "query1" OR f2 LIKE "query1" AND f1 LIKE "query2" OR f2 LIKE "query2"). But this seems the worst possible way to do it. I was thinking fulltext search, but it seems since I know there will only be up to 20 keywords, fulltext may have too much overhead. I am expecting about 50,000 documents in the table, using MySQL 5.0. Is there a better table layout and optimal query to search the 20 keywords of each document?
View Replies !
Optimal Way To Log View Counts
We are running into issues when trying to log the number of times a page has been viewed on our website due to recent large increase in traffic. The psuedo-code for every pageview on the site is INSERT INTO pageview (pageid,date) UPDATE page SET viewcount = (SELECT COUNT(*) FROM pageview WHERE pageid = page.id) This is causing us locking issues because the 'page' table is very heavily selected from. I realise that the subquery in the UPDATE statement above could be replaced with a simple 'viewcount = viewcount + 1' but we've had issues where this count has drifted out of sync. What's the best practice way of doing this? Would it be worth moving the viewcount column into a separate table to try and prevent the locking?
View Replies !
Optimal Number Of Tables
I know there are various ways to configure a database setup and therefore affect performance tweaks, but in general is there a limit to the number of tables you can have in a database after which performance would be impacted?
View Replies !
Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that start with the letter "A", "B", "C" .... and so on. I am pretty poor at SQL so I am sure some brains out there can do better than I have here. What I have is working, I just want to make sure that it is optomized. So let's assume I have some query "$query" that I want to run and get an A..Z list based on column "$column". Let's further assume that '$query" produces the following results, and that $column is equal to "last_name". last_name --------------- Anderson Bitmore brown Bogus My AZlist query would look like this: select * from (SELECT count(alist.$column) as a from ($query) as alist where alist.$column like 'a%' or alist.$column like 'A%' ) as a_result, (SELECT count(blist.$column) as b from ($query) as blist where blist.$column like 'b%' or blist.$column like 'B%' ) as b_result, .... (SELECT count(zlist.$column) as z from ($query) as zlist where zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result; And this retuns the following result: a | b |...| z -------------- 1 | 3 |...| 0 Meaning that $query has 1 result where the first letter in $column is "A" or "a", 3 results where the first letter is "B" or "b" and 0 results where the first letter is "Z" or "z". What I am afraid of here is that "$query" is being executed 26 times (once for each letter of the alphabet) . Is there a way to refine this, or is MySQL (4.x and 5.x) smart enough to optomize this on its own?
View Replies !
Creating Complex Query
its a booking form. user selects start date, finish date, and room type. on my table i have roomInfo(roomNo, type, NoOfBeds); occupancy(roomNo,DateStart,DateFinish,GuestNumber); i am trying to do something like: select roomNo from roomInfo where roomNO = single and roomNO not IN ... cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?
View Replies !
Creating A Query From A Form
I'm struggling building a SQL query from the output of a form, i.e. the user inputs into a form which in turn decides the query. I have never done this before and was just wondering if anyone had any links tutorials of something like this!? I have searched but haven't found anything too useful yet. Basically all I want to do is for the user to pick from a drop down menu how they want a leaderboard displayed, i.e. top 50 results, bottom 50, 50 to 100 results, etc. Do I just tie a complete SQL statement with the corresponding LIMIT info inside to a variable. The variable being the value of the chosen item in the drop down menu. Pseudocode as follows:
View Replies !
Creating Table From A Query
I have just put a forum up on my web site. The database tables were readymade via phpbb. Because I want to send newsletters I need a table that just contains usernames and email addresses. The table for users in the phpbb forum is huge so i did sql query: SELECT user_id, username, user_email FROM USERS; Which gave me all the information I need to send the emails on mass when this table is linked to a newsletter application. however I am aware this is just a query. Can I create a new table from this query? So i just have a table of users and emails. If so how do I do it? I am using phpMyadmin.
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Creating A Query That Will Only Return Records With Matching Counterparts
I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this? id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF
View Replies !
Creating Id
Have a database that records data taken in the field and was using an "auto_incremented" integer. I now want to distribute the database to others so they can record data then centralise all the data on one database so people can share records. However will an "auto_incremented" integer on it's own won't suffice? I suppose it might if that field didn't have to be unique but is that best practice? I was thinking along the lines of creating a unique field by combining for 3-4 fields such as time, date, member and "auto_incremented" integer. What is this technique? I am sure there's a lot of stuff already on the 'net explaining how to achieve this but I am a bit stuck as I don't know what search terms to use.
View Replies !
Creating A DB
I use a MySQL DB to store Japanese characters. I didn't specify any character settings or any kind of language settings when creating the DB and tried with the defaults. I use MySQL 3.23.56 Now I have a problem of viewing the Japanese characters stored in the DB. The charcters get stored in unidentified format but when query from the DB and display in the browser it gives the correct values from DB.Can anybody suggest what is the problem and how to solve it. May be I have to give some language settings when creating the database and if so can somebody please reply me with sample script explaining how to do.
View Replies !
Creating A New Db
Im trying to create a new db in phpmyadmin, after already creating the db in cpanel mysql, but im not sure how to create a login, its easy in the dos shell on the pc, but im now using a mac.
View Replies !
Creating A Db With Php
My host will not allow SSH access, or do any modifications to the MySQL database. He set it up, gave me a login and pass, and thats it. Fine. But I am trying to create a table, and here is what I have for code:
View Replies !
Creating Users
I've been having a little trouble creating users cross-platform. I have a little script that works just fine on Solaris 8 using this command: eval "/path/to/mysql --user=root --password=***** --socket=/path/to/socket --database=mysql --execute="INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Shutdown_priv, File_priv, Create_tmp_table_priv, Lock_tables_priv) VALUES ('localhost', 'LocalUser', PASSWORD('******'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"" With the obvious changes to make the statement actually work. However, if I move this over to Linux, I get Invalid Authorization Specification errors when I try to connect as LocalUser after a reload, and using GRANT seems never actually to grant privileges (e.g. no "Y"s in the user table). Does anybody know how I can successfully create a user with my script?
View Replies !
Creating Db Admins
I create a database, foo, then designate a user, pooh, with full privileges, including grant option (pooh is to administer this database, including creating other users): grant all on foo.* to 'pooh'@'localhost' identified by 'zoo' with grant option; Now pooh logs in to the mysql server, and tries to use his grant option to create another user. This fails: grant select on foo.* to 'moo'@'localhost' identified by 'goo'; ERROR 1044: Access denied for user: 'pooh@localhost' to database 'mysql' but this succeeds: grant select on foo.* to 'moo'@'localhost'; Query OK, 0 rows affected (0.00 sec) Now moo can log in without a password. So pooh can create a new user with any of pooh's privileges to database foo, but pooh cannot give the new user a password?! What am I missing? ('set password', or inserts or modify, don't work either) Does it really take root (or another superuser)to create a password?
View Replies !
Creating An Array
I want to select a bunch of email addresses from a mysql table and format them to use with a php mail() function. I can select them from the database, but how do I format them so there's a comma and space after every one?
View Replies !
Creating This Table
I have this schema I exported from a database running mysql version 4.01 I'm trying to create this table on a server running mysql 3.23 I keep getting a syntax error near line 11 (I added the line numbers for clarity. I think it has a problem with the Table Field "ORDER#" I do have it quoted as shown below so why do I keep getting syntax errors? I also have problems with the Table Fields "BY" and "DESC" and they are quoted the same way. 1 CREATE TABLE `tabPO` ( 2 `PONUM` int(11) NOT NULL auto_increment, 3 `VENDOR` varchar(12) default NULL, 4 `STATUS` varchar(12) default NULL, 5 `TYPE` varchar(5) default NULL, 6 `PODATE` datetime default NULL, 7 `ORDERED` datetime default NULL, 8 `EXPECTED` datetime default NULL, 9 `SHIP` varchar(10) default NULL, 10 `CLOSEDATE` datetime default NULL, 11 `CHARGES` double default NULL, 12 `ORDER#` varchar(12) default NULL, 13 `NOTE` varchar(80) default NULL, 14 `SHFEE` double default NULL, 15 `PINVOICE` varchar(16) default NULL, 16 `SEPARATE` int(11) default NULL, 17 `LOCATION` varchar(5) default NULL, 18 KEY `PONUM` (`PONUM`) 19 ) TYPE=MyISAM; .
View Replies !
Creating A Foreign Key
So, far I'm using phpmyadmin to create my table structure and relations. My database storage engine is myISAM. There's options to create primary keys, index, and unique for the column fields, but how would I create a foreign key in phpmyadmin references a parent table?
View Replies !
Creating A Table From Another
how to do a new table out of an old table, but with only uniques entries? The problem is that: I got a table with about 27k entries, but many of them are dublicate like that: id|column1|column2 1|a|bcd 2|b|cdf 3|b|ddf 4|b|cdf 5|c|bcd 6|a|bcd Now I want in that example e.g. entry 4 and entry 6 removed...
View Replies !
Creating Backups
I have designed a peice of code in 2 dif prog langs which create backups of the same database. However both take a fair old while to run because it goes through line by line of each table to create the backup. I have 2 servers: First is where all the live data sits and the second stores all my archived / backup data. Is there any way in SQL to actually copy from one server to another the complete database without human intervention. This probably is a really simple question but i haven't really looked down the path of using just SQL to do it?
View Replies !
Creating Table
I am trying to Create this database, but when try it gives me an error: MySQL said: Documentation #1005 - Can't create table '.databaseattendance.frm' (errno: 121)....
View Replies !
Creating New Rows
is it possible to create a new row to an existing table? if i had a table like this: +------------+------+-------+ | name | sex | fur | +------------+------+-------+ | Pythia | f | black | | Archimedes | m | black | | Kaliope | f | grey | | Bear | f | black | +------------+------+-------+ what is the cmd string to add a row for something else at the end of this table such as owner: +------------+------+-------+----------+ | name | sex | fur | owner | +------------+------+-------+----------+ | Pythia | f | black | NULL | | Archimedes | m | black | NULL | | Kaliope | f | grey | NULL | | Bear | f | black | NULL | +------------+------+-------+----------+ must i rebuild the table again or fall back on a .txt file using LOAD DATA LOCAL INFILE 'pet.txt' INTO pet;.
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 Index
mysql crashed as i tried to index a table that contains about 312'000'000 rows. the exact command was: create index residual_index on residual_system_test(ts); first i thought this will take a lot of time due to the big amount of rows, but after many hours nothing more happend. can mysql handle with such big tables?
View Replies !
Creating DB From File
in the mysql command-line I did: mysql> create database myDB; mysql> use myDB; mysql> source mySource.sql; and I get lines as - Query OK, x rows affected, y warnings (z sec) ------------------------------- My question: Howwhere can I see what were the warnings, so I can fix them? ------------------------------
View Replies !
Creating Either Or Relationships?
How do you establish/enforce an either or relationship in mysql? For example: Table A : clients Table B : client_billing Table C : agency_billing The relationship can be A<-->B or A<-->C but not both A<-->B and C A client can pay themselves A<-->B (1 to 1) or an agency can pay for many different clients A<-->C (1 to many) How do you enforce that in a relational database?
View Replies !
Creating A VIEW Using Php
$query="CREATE VIEW test AS SELECT option_price.option_id,plan_id,name FROM option_name,option_price WHERE (option_name.option_id = option_price.option_id) ORDER by option_name.option_id "; $results = mysql_query($query);
View Replies !
Creating New User
I am fairly new to MySQL. I have tried working with it in the past but not as much as lately. I would like to make a web page where a person fills in their desired user name and password to create a new user in MySQL giving them full access to their own database. How could I do this with HTML?
View Replies !
Creating A New User
I am trying to create a new user with limited permissions. I want them to be able to select, insert, update and delete from the pgvDialogue DB but have no access to other DBs or be able delete/create tables. Here is what I am doing but the user can still delete tables.. grant select, insert, update, delete on pgvDialogue.* To 'newUser'@'%'
View Replies !
Creating Privilege
I own a domain with somebody else, and neither of us are really great with mySQL... So, there is only one login that we both use. I wanted to create a database, and so I logged in, and it says that I have "No Privileges". Error code #1044, when I tried creating one.
View Replies !
Creating A New Table
I'm trying to create a table and, by using phpAdmin I am given this SQL CREATE TABLE `tblDailyEvents` ( `DailyEvent` TINYTEXT( 255 ) NOT NULL , `EventType` TINYINT( 3 ) NOT NULL , `DateOfEvent` DATE NOT NULL , `ResourceLink` TINYTEXT( 255 ) NOT NULL , PRIMARY KEY ( `DailyEvent` ) ) COMMENT = 'Daily Info for home page' However, when I try to action it I get an error telling me to consult the manual with Error #1064. I just don't understand the manual - it's simply too technical for me to understand. Why create this code if it doesn't work? And what should the SQL be changed to?
View Replies !
Creating Triggers
following error: Script line: 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into mysql_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','Tr' at line 4 This is my trigger: CREATE TRIGGER mysqltri After insert on product for each Row begin insert into mysql_product(Pro_Id,Name,Rank,Flag) values(New.pid,New.Pname,'7','True'); END$ delimiter$
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 New Users
I have added a new user to the mysql.user table and gave it the same permissions as the 'root' user (all Y's in the database). When I use these details in an asp connection string I get the following error: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [MySQL][ODBC 3.51 Driver]Access denied for user 'nmssiteuser'@'localhost' (using password: YES)
View Replies !
While Creating Triggers
I am trying to create a simple trigger by query as: CREATE TRIGGER interactiontrigger AFTER INSERT ON InteractionDetails FOR EACH ROW BEGIN UPDATE LeadDetails SET SalesStage= NEW.SalesStage WHERE Id = NEW.LeadId; END when i execute this query i get the following error as: ERROR 1314 (0A000): Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
View Replies !
Creating A .sql File
i downloaded mysql and i created a database and everything but i can't get the mysqldump thing to work and i also dled apache2triad and i have no idea how that works so basically i need someone to help me out on creating a .sql file
View Replies !
Creating Indexes
I have a doubt on indexes. I need to create some indexes on a series of relations I created with mysql. I have to create a index on the primary key and a index on the foreign key for each table. The problem I found is with tables of this type: I have two columns, both belong to the primary key, and only one is a foreign key. Is it different if I create the indexes this way: create index primarykey on table(column1, column2); create index foreignkey on table(column2); or this way: create index foreignkey on table(column1); create index i2 on table(column2); I mean, is it different to create a index on two columns or creating two indexes each on one column?
View Replies !
Creating New Table
Hi, I am using Apache2 + PHP5 + MySQL 4.0.24_Debian-10ubuntu2-log, also MySQL Administrator 1.0.22 is installed. I am running this services on Kubuntu Linux. I am very new to mySQL so I need your help on adding new tables and all that. I am currently toying the MySQL Admin add new schemata and add table on it. I just put name as table name then {apply changes} a confirmation table creation pop-out with this syntax: CREATE TABLE `test`.`Name` ( ) ENGINE = MYISAM; When I press execute, this message appears: Error executing SQL commands to create table. MySQL Error Nr. 1064 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 ') ENGINE = MYISAM' at line 2 .
View Replies !
Creating A Unique Value
I'm trying to create a stored procedure which creates a unique value each time. This one creates the a value based on what length I want to give it and prints it. CREATE PROCEDURE `test3`(IN idlength int) BEGIN declare uid char(30); declare a char(1); set uid=''; while length(uid) < idlength do set a= substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',floor(rand()*64), 1); set uid = concat(uid, a); end while; insert into t2 values (uid); select UID; END $$ What I need to do is to compare this value with the already stored values in the table to check if the new one is unique. Any suggestions on how to do that?
View Replies !
Creating Columns Using PHP
I have a 119 record MYSQL that I am querying through PHP. I would like to display as three columns. Here is some of the code, is there a way to use this code then disply as three equal columns? Code:
View Replies !
Creating A Function
i am trying to create my first function, using mysql 5.0.19, and getting an error. i am typing interactively, mysql > create function helloworld() returns varchar(20) -> begin -> declare l_hello varchar(20); when i press enter on line 3 (declaring the variable), mysql responds with 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 'varchar(20)' at line 3 This example is straight out of a manual / website example. It is simple enough, i don't see naytin wrong with the first part.
View Replies !
Creating Databases
I've successfully created a user and a blank database. I want to grant full access permission to the user I created. How do I accomplish this task?
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 New SQL Table
I've been having problems with creating a new table using a php page, this is the code i use: $query = "CREATE TABLE `pictures` ( `id` int( 11 ) NOT NULL , `gebruiker` VARCHAR( 255 ) NOT NULL , `foto` VARCHAR( 255 ) NOT NULL , `description` VARCHAR( 255 ) NOT NULL );"; $result = mysql_query($query); echo "Table <b>Pictures</b> Created"; But this doesn't seem to work. Because it isn't doing something, not executing the query, how can i let it execute it?
View Replies !
Creating New Account
i have apache server, with mysql, after that from the bin directory in the dos prompt, i typed: mysqladmin -uaymenplusplus password mypassword after pressing enter it told me that couldn't connect to the localhost, check that mysql is running on localhost on port 3306. what is the problem? and also what is the usefulness of the winmysqladmin, when i run it the first time it didn't prompt me to enter username and password, and when i run it the icon in the tray icon showing red light (the traffic light). what is the problem?
View Replies !
|
TRACKER
