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 Complete Forum Thread with Replies
Related Forum Messages:
Efficient Country/Province City Setup
I currently have a program that does a global query of all of the data. I want to break it down so as to drill the user down to thier local area. for example: Country Province Region City As you can see, a country can have many provinces, a province many regions and a region many cities or a province many cities. What is the most effecinet way to do this in MySql? I'm OK with doing single tables. ie: INSERT INTO Province VALUES (NULL, 'Ontario', 'ON'); but I'm unclear if that's the way it should be done. I'm using PHP on the client side.
View Replies !
Most Efficient, Stable Version Of MYSQL?
We're still using mysql 3.23.56, so it's time to upgrade! It has never once crashed on us, so we've been content with it so far. However, some of the inefficient query optimizations make us want to upgrade. Does anyone know what the current most efficient and stable version of MYSQL is? We don't require views or cursors or stored procedures or any of that... Just whatever's the most reliable and best optimized for fast query execution of fairly simple SQL. I know that 3.23 has some issues with using indices correctly during optimization (most notably in ORDER BY ... DESC). Is that problem fixed in 4?
View Replies !
MySQL Setup Error
While installing mysql server on a windows 2000 machine without a firewall, I get an error numbered 1045. Access is denied for user 'root'@'localhost' (using password: NO).
View Replies !
Inital Setup Of Mysql
I'm trying to get mysql working on redhat 9.0, Origionaly I got "can't connect to local mysql server through socket /var/lib/mysql/mysql.sock" I ran /usr/bin/mysql_install_db which corrected this! Then it started and immediatly stopped using the command /usr/bin/safe_mysqld. I looked at the error log in /var/log/mysqld.log which said can't initialize innodb_data_file. No idea what that meant so I put:- [mysqld] skip-innodb in the file /etc/my.cnf Now the only error says /usr/libexec/mysql: can't find file ./mysql/host.frm I've created a file called /usr/bin/host.frm and set the permission the same as the other files in there, but it still cant find it? The name of the machine which is just localhost is in there, anyone know what needs doing next?
View Replies !
Error With Mysql Setup
After an installation of Namo WebBoard messed up my MySQL installation - I have been unable to get it fully working again. My setup is as follows: Apache 1.3 / MySQL 4 / PHP 4 When I use phpmyadmin I get the following error: #1045 - Access denied for user: root@localhost' (Using password: YES)
View Replies !
MySQL Setup On XP Localhost
I have got the server working on Win XP Pro according to Winmysqladmin.exe. I have a password in my.ini but when I go to CMD to login to root, it gives me: "ERROR 1045: Access denied for user: 'root@localhost' <Using password: YES>" I am the system admin. I am however not using 'localhost' as such since I am behind a router that has assigned this workstation: 192.168.0.3. The USER and HOST are clearly shown in winmysqladmin.exe, like it knows about it, but the mySQL.user database shows only root USERS on "Build & Localhost" and two blanks also...the only way I can currently log on. Is this a no Win situation? I've been at this for 3 days going in circles and I suspect UNIX developers are exceeding happy to hear this. Yeah, I'm using IIS 5.1 instead of Apache too.
View Replies !
MySql User Setup
I have mysql working, and i have a user set up (i did this ages ago). How do i create a new user in mysql?. Can i create a new user for a specific database? or will each user have access to all databases?
View Replies !
A Scrimmage Setup With MySQL
I have only dealt with MySQL databases in the past, so I'm not sure if this is the best way to go about this or not, but feel free to inform me otherwise. What my plans are is to create a web-based form (I assume using AJAX) that will allow you to enter multiple fields of data, which will then go into the database, and when another person submits data that compliments yours (i.e. they match up to create a scrimmage,) then both parties will be notified and the data will be deleted from the table. Is it possible to do with PHP and MySQL
View Replies !
MySQL Relication Setup
I have 3 servers each of them have MySQL DB- Primary, secondary and tertiary. There is another application that will access the database. It will always try to connect to the primary, if primary is down it will try to access the secondary, if secondary is down then the third one. I'm planning to set this up in this way. Say the servers are S1, S2 and S3. There will be 3 real time sync links S1 ---> S2 Master-Slave Setup S2 ---> S3 Master Slave Setup S1 --->S3 Master Slave setup (This link will be active only if S2-->S3 link is down. Any thoughts on this. Would this suffice? Any possibility of data corruption and are there better ways of doing this?
View Replies !
MySQL Cluster Setup
I have a MySQL CLuster setup like 2 SQL Data Nodes , 2 Load balancer & 1 Management Node. [ All have Individual IP Address] Using Virtual IP [For my 2 Load balancers ] my Front End WEB Application accesses data from the Cluster. My Doubt is say transaction 1 -- started accessing data from [SQL Data node A] and transaction 2 -- started accessing data from [SQL Data node B] Say suddenly [SQL Data Node A] goes down. Now what happens with the transaction 1?
View Replies !
Download.php Setup With Mysql
I am trying to make a download.php which would let users to download files after 5 seconds. The only problem is that my /download directory has many sub directory. For example if someone wants to download FireFox, then firefox.exe is located in /download/FireFox/firefox.exe and I want somewhat like this www.domain.com/download.php?fi... as an output URL to download. I know that I can use MySQL and setup a nice database to do something like this as long as fileid is unique. Any ideas on how to setup the MySQL database so that I can easily add multiple files from different sub directory at the same time.
View Replies !
Question About MySQL Setup On A Mac
I'm a fairly experienced web designer, but a newbie to PHP/MySQL. I have the Sitepoint book by Kevin Yank called "Build Your Own Database Driven Website Using PHP and MySQL" and I'm ready to start learning! I already have a mySQL db setup through my web host and I can access it using PHPMyAdmin via the web. However, the book recommends (and gives instructions for) setting up Apache, PHP, and MySQL locally and says that for learning purposes it is better NOT to use PHPMyAdmin. I hear that, but at the same time, for security reasons, I'm not all that hip on turning on Personal Web Sharing and making my computer into a server. It seems to allow access to my Sites folder which is where I have my local copies of sites I'm working on and I don't really want anyone else to access those... I'm hoping someone here can tell me which way to go... is using PHPMyAdmin really going to hamper my learning experience, or should I just use it (and feel safer)? Or are my security concerns really not that much of an issue? If anyone can give me any guidance on this, much appreciated.
View Replies !
How To Setup A Remote Mysql Database
i have a vb front end application and im using mysql as my database. i would like my vb application to connect in database through internet. so i setup my other computer ( windows 2000 ) as a server, i installed a webserver, enabled http, but when i try connect my application an error occured. which maybe mysql has not been setup correctly. i would just ask if anybody here knows how to setup a remote mysql database so my vb can connect to it.
View Replies !
ODBC Setup On MySql Server 5.0
I've installed the MySql server 5.0 successfully, opened and verified it working (the instance is running, i've used the server window to login and used a few commands in it). I've also installed the MySql Query broswer along with the 5.1 odbc driver. However intalling the driver didn't populate a User DSN and I've no idea how to add it myself i.e. the settings! The driver is present in the driver tab.......
View Replies !
Difference In Server MySQL Setup?
I have two versions of the same database - one is here local on my development set-up, the other on the live server. I literally just dumped the dev db and used the resultant SQL to create the live version, so they're about as exact as I can make them. Both have a record in "tblphotogs" with the value of the "addr" column equal to "123 Here Street". However, the query "SELECT id FROM tblphotogs WHERE addr = '123 Here Street';" only works on the dev db. Is this some bizzare setup thing that I've just never come across before? In both db's, the addr column is VARCHAR set to 250 characters. I've tried using an index and a fulltext on the addr column on the live server and it still doesn't work. I assume I'm missing something very simple, but I'm about to start ripping out my hair. This is the last thing I need to suss out before I can bill out my final invoice on a large site. I like the project, but I'm ready to move on, you know? Anybody come across this before? Have any idea what's going on? I should state that - with the exception of this specific query - everything else has ported seamlessly between development server and live server. Both systems are using MySQL 4.1.13, I believe...
View Replies !
How To Setup Mysql Zip Package (XP,Apache,PHP 5.2)
I downloaded mysql-noinstall-4.1.22-win32.zip, and mysql manual also. When i read the manual i found quite a few modifications that i have to do in order to run my database properly. I don't want to use installer, couse my apache, php setup is on partition that i never format, and i need to make just one modification when i reinstall windows. I would like to do the same thing with MySQL, i mean, i don't want to do install it wrong way, and just need couple of advices on how to properly make all modifications to *.ini files, so i can store them somewhere safe, so when i format my computer i can set them back to the folders, and just run mysql from Bin folder. my folder is on D partition in folder www. Here's the structure d:wwwApache2htdocs <- apache htdocs folder d:wwwphp <- php folder d:wwwmysql <- mysql folder My database can use as much processor and memory power it needs to run a heavy scripting tool.
View Replies !
Optimizing MySQL For A New Server Setup
We are currently switching over to a newer more powerful server. I am wondering if anyone could give me their input on what would be the optimal configuration of MySQL for the following conditions.... Dual Intel Woodcrest Dual Core 4 GB RAM NCQ SCSI SAS RAID drive MySQL 5.0.45 PHP 5.2.3 There are 2 databases on the server which combined total approximately 4 million records across 120 tables with a disk footprint of about 1GB. Traffic fluctuates from between 3,000-6,500 page views per day. After we finish the upgrade we expect traffic to grow by 1.5 to 2 times that since the server will not be overwhelmed as much. Code:
View Replies !
How To Setup A MySQL Table To Keep Track Of Stats?
I have a new system Im building that stores entries of what people are searching for on my sites. I want to be able to keep records of how many times a keyword was searched for daily, and from that I can calculate weekly and monthly. At this point I have one entry per search phrase with the number of hits the search phrase has gotten, and the last time it was updated. As I start to take the program out of testing and move in more into a used tool Im getting worried that my idea of switching to mutable entries for the same search phrase would be a bad idea as within the last 15 days I have stored more then 3 million unique search phrases, and a unknown number of hits. So should I make a rolling database that stores each search then rolls that data over to another database once a week that users can use, or is their a better way of doing it where I only keep one entry per search phrase and am still able to keep records how daily search amounts so that I can track trends, etc in search phases on my sites?
View Replies !
Mysql Setup/usage/understanding Docs
Someone have a link to some howtos or docs that easily walks you through basic setup and usage of mysql? I do not need to know the ins and outs of mysql, just enough to get it up and running smoothing/securely and basic navigation info.
View Replies !
Mysql Data Soruce Name Setup Dialog Window
my program connects remote database with ado. i change ado settings in runtime. when ado doesnt connect remote database, "mysql data soruce name setup dialog" window comes to screen. i want to change host when connection failure.
View Replies !
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
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 !
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 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 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 !
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 !
Efficient Ways To Retrieve Specific Rows.
My site used forum software that I wrote myself (in Perl) which, up until now, has used flat files. This worked fine, however lately I've been wanting to do more stuff with user accounts, and had been eying MySQL for over a year. Finally I've decided to start off small by converting the forum's account system to a MySQL database (and convert the rest later after I'm comfortable). So far everything is working fine, and I've figured out how to create the table, insert records, modify records, and so on. However I had a question on what was the most efficient way to retrieve information about a user as I read through the flat file containing messages. As each message is read I want to find that user's relevant information, build it into my output, then continue on to the next message. Now here's my problem. Since I'm reading a flat file in a way that was once trying to be somewhat memory efficient (on really slow server way back when, trying to avoid arrays and hashes) I'm finding that I'm having to send separate questions to MySQL as each message comes up. Say from 1 to 20 very simple questions to complete printing the page. Also, sometimes the questions might be repetitive - since I'm not storing any of the results in memory, if a user appears twice I ask about it twice. Would I really be better off trying to find a way to consolidate all distinct users into a single question? Or is MySQL efficient enough that this isn't really much of a concern?
View Replies !
|
TRACKER
