Which Is More Efficient? One Big Query Or Lots Of Little Queries?
Which is more efficient/faster/better?
running one big query with lots of joins, or lots of little queries in a loop?
so:
1.) one big query:
select cat.*, subcat.* from cat join subcat on ...
or
select * from cat
while $row = $cat_result->fetch()
select * from subcat where catid = $row['catid']
????
View Complete Forum Thread with Replies
Related Forum Messages:
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
Lots And Lots Of Threads :: Slow Server
currently, mySQL threads are using all my CPU - causing site slow down. We have 2x 3.2Ghz processors and only around 10k visitors a day. It seems that the server is creating lots of mysql threads, whereas on another server that I monitor (running completly different setup) no matter how many visitors there is only 1 mysql thread running, sometimes that goes up to 90% CPU usage, but theres only 1 thread, whereas on the new server, the threads seem to take up all the CPU no matter how many visitors we have, we currently have around 6 mysql threads that are using 60% cpu and a few using around 9% (not sure how thats even possible). If there were more threads they would use a lot less, but if there are less then they use a lot more. Basically, I want to know a) why is one server creating lots of mysql threads and why is the other only creating 1 and b) how can I reduce the CPU load.
View Replies !
Lots Of Data Lots Of Users..
If I have around 5,000-10,000 pieces of data (which will get updated daily) that I need to display in a grid and will expect 1000+ visitors a day viewing this data, what is the best way to go about doing this? Would it be better to export all the data to a database and then import to grid or just put the data into text file or something and import to grid from that?
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 !
Slow Query->Efficient Query
I have a query that is running really slow !!!! I have joined on Key fields and indexed the tables fully but it is still solw. -------------------------------------------- select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual from details d join poten a on a.id = d.id left join recieved p on d.id = p.id left join users u on a.signed = u.userid where d.paid > '01-Dec-2005' and d.authorrceived is not null and d.authorrefused is null and ((d.payment starting 'E' or d.payment starting 'e') or (d.payment starting 'Q' or d.payment starting 'q' and p.target = '500')) order by d.paid, a.signed, d.id
View Replies !
Multiple Queries Within 1 Query
In the past, I have queried the database, grabbed one tidbit of information, then performed a second query to find the second tidbit, based on the information gathered from the first query. As you can probably guess, this method, although effective, is very slow. All of this comes from one table, in this case, named 'horse.' I thought that INNER JOIN was the way to solve this delimma, but my query causes errors. I am hoping someone can get me started down the right path. Here is my query: $result = mysql_query("SELECT p1.name,p1.dob,p1.color,p1.dam,p1.starts,p1.wins,p1.seconds,p1.thirds,p1.earnings,p1.mareFamily,p1.gender,p2.sire AS damSire FROM horse AS p1 WHERE sire='$prog_name' INNER JOIN horse AS p2 ON(p1.dam=p2.sire) ORDER BY p1.name ASC"); This cause the following error: 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 'INNER JOIN horse AS p2 ON(p1.dam=p2.sire) ORDER BY p1.name ASC' at line 1 Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource I am using MySQL 4.1.13-standard.
View Replies !
Lots Of Dbs
Is there a problem (performance, anything else) with having lots of databases? I'm building a site where each user can have his own forum, which means a single database per user that signs up. This can quickly grow to more than 1000. I need to know if it's sustainable.
View Replies !
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
View Replies !
Backslash In Data/query And Like Queries
mySQL 4.0.20a WIN32 Need help with backslash syntax/like queries.. Query1: select * from dbtable1 where Filespec like 'http://www.10291.com/%' returns : http://www.10921.com/sikhnet/register.nsf/ram/radioG2/$File Query2: select * from dbtable1 where Filespec ='http://www.10921.com/sikhnet/register.nsf/ram/radioG2/$File' returns 0 records Query3: select * from dbtable1 where Filespec like '%\%'; returns 0 records What am I missing here....?
View Replies !
Lots Of Data With Pictures
i have lots of data for products (with different categories) and pictures that i would need it entered into a database on the website. How would i go about doing so? i was thinking about an excel sheet with each category as a sheet but i have no idea what to do about the pictures that will go with each product, (since i would not want the pictures in the mysql database.)
View Replies !
Insert Lots Of Text
I am trying to use a mysql database with php to create dynamic pages. I have no problem creating a page which holds small bits of information but i would like to have a dynamic page where a lot of text is dynamically stored. I would then be able to alter the pages of my site using the database rather than going into dWmx. at present i can get it to store quite alot of text but it cuts off at a certain limit, also it is also no longer formatted when it is retrieved. the field is set to text. Id be very grateful if someone can tell me if i am going the right way about this.
View Replies !
Lots Of Tables In Database
Lets say that you have alot of tables in your database, but each table doesn't store too much information (just a few small records). Does it still drain process if you have alot of tables?
View Replies !
I Need A Filter For My Queries To Extract The Character " From The Query
Initially we designed an upload module, which used a longblob field on the datastructure level to store the file. it is working fine. Now i need to extract the text of a file before it is uploaded on the database, i am able to extract the text from the file that is being uploaded but if it contains any kind of double quote character ( " ) then the query sees it as end of line in the query.
View Replies !
Lots Of Aborted Connection In 5.0.18 Version
After a long troubles in installing mysql 5.0 in windows 2k3 (Problem with that cannot connect using localhost) i finally upgraded mysql on November 25th 2006. From that day till today I may have got 100s of aborted threads/connections in my server log. I am not sure about that and not sure about the reason also. Could you please help me in correcting the same. Below is the error message I am recieving always 061206 12:55:00 [Warning] Aborted connection 6130 to db: 'mydatabase' user: 'myusername' host: 'localhost' (Got an error reading communication packets) No Idea why this is happening daily. Per day there will be atleast 5 threads gets disconnected.
View Replies !
Changing Usernames On Lots Of Tables
I have a database intensive web site and we allow people to change their usernames. When someone changes their username, over 32 queries have to be made in order to change their username to the new one, because there's tons of tables. Everytime we add more tables that number only continues to grow. So we decided to use account id's (primary key) instead, replacing the usernames, and using JOINS to grab the username each time it's needed for display. Eventually, to change someones username, only one query would needed. Now TONS of CPU is being used due to all the joins. All indexes are in place and types are always const, ref, and/or eq_ref. What would be the best solution to this problem? Would subqueries be better?
View Replies !
Deleting LOTS Of Duplicate Entires Except Keep 1 Of Them
I have a database that stores all my RPO codes and it's descriptions. Everything was fine until I discovered (assuming) a search engine BOT must have run the script I wrote that imports the entire list into the database. I ran it once to import the list of 23,000+ RPO codes. But now it seems it was run 28 times more. So rather than 23,000+ in my MYSQL Database, I now have almost 652,000!! MOST of each duplicated 28 times or so!! Manually deleting 629,000+ Entries is NOT what I call fun, nor efficiant so I assume you can easily do this using a SQL QUERY.. I want to do the following: 1. Search for 2 fields in my database "RPO_CODES" and "DESCRIPTION" 2. DELETE ALL of the duplicate fields where "RPO_CODES" AND "DESCRIPTION" duplicate other entries 3. However keep 1 FIELD of each.. I only want to delete the DUPLICATE ENTRIES.. Example: Entries. 1. RPO Description 1. L98 5.7Liter V8 Engine for Camaro's and Corvettes 2. L98 5.7Liter V8 Engine for Camaro's and Corvettes 3. L98 5.7Liter V8 Engine for Camaro's and Corvettes 4. L98 5.7Liter V8 Engine for Camaro's and Corvettes 5. L98 5.7Liter V8 Engine for Camaro's and Corvettes 6. L98 5.7Liter V8 Engine for Camaro's and Corvettes 7. L98 5.7Liter V8 Engine for Camaro's and Corvettes 8. L98 5.7Liter V8 Engine for Camaro's and Corvettes 9. L98 5.7Liter V8 Engine for Camaro's and Corvettes 10. L98 5.7Liter V8 Engine for Camaro's and Corvettes So it ends up with just 1 entry ...
View Replies !
Lots Of Updates And Inserts - Use Innodb?
I'm working on a browser game and have been using MyIsam for my tables so far, but I've read about Innodb and it seems like something I probably should use for at least some tables. It's a browser game, so I keep track of players stats in different tables (about 5-10), and each of these tables are updated a lot. Whenever an item or something is bought, equipped, sold, there has to be updates in different tables. There will (hopefully) be lots of players online, each equipping and managing their items and such, playing against each other and so on. I try to "cache" or save static information instead of getting fresh data from the database whenever I can, so I think the number or Inserts/updates will actually be pretty high compared to selects. The tables are locked table wise with MyIsam (?), and row-level with Innodb, or so I have read... I get the impression I should use Innodb on the tables which are updates/inserters frequently, and leave MyIsam for less intensive tables like archiving and player logs. The tables are pretty small, the total size in GB with 1 million players (!) is probably less than 5 gb if they are all MyIsam.
View Replies !
Loading Lots Of Data Into Mysql Table
So I need to load lots of data into my database. So I discover LOAD DATA INFILE. Great! This little gem loads my CSV in blazing times (compared to parsing the file and doing INSERT for each row). Its still slow on large files, but just barely acceptable. Only one problem. It truncates fields to 256 characters, even on a text field.
View Replies !
Fulltext Search Lots Of Load On Server
I have 300k rows in my db, 1 gig of ram. I do fulltext indexing on a large field (textual). It appears that fulltext indexing puts a lot of strain on my server. I start bumping up iowait numbers pretty hard. Has anyone seen this, anything I can do. Alternative is to build tables of words as PHPBB does. Any performance tradeoffs. I do know the functionality tradeoffs.
View Replies !
Replace One Word With Another In Lots Of Rows In A Database
Is there in any way (with MySQL or PHP or software) to replace one work with another in all rows in a table? I have about 7000 product descriptions in Swedish and I need to translate them into English. The good thing is that all the description only contain a few words (I think that I just need to replace about 30 words in total to translate everything). The bad thing is that I don't know how to do that. So I want to select all rows with the language id for English (currently all English decriptions are in Swedish) and then replace all occurances of a specific word with another specific word. So UDPATE description ???? WHERE language_id = 5 is what I've figured wold be a start for an MySQL command... the problem is that I don't know what to put instead of ????. Would it be possible to do that in PHP or MySQL? Could you help me with any kind of command (is that how you say it?) or any other help?
View Replies !
MySQL Consuming Lots Of Memory During Transactions W/ Many Statements
I am having a problem with MySQL consuming a lot of memory and eventually throwing an Out of Memory error and restarting itself. The symptoms are that swap usage continues to rise until some breaking point. The application is a typical web application w/ 2 web servers running Apache/Tomcat connecting to a dedicated DB server running only MySQL. This seems to occur as a result of running many statements in a single transaction, both against InnoDB tables and MyISAM tables. In one case, I'm writing all user actions to an audit table (MyISAM), all from a single application thread doing approximately 5K inserts (as single INSERTs on a single connection) every few seconds. In the other, I'm doing a variety of select/update/insert/delete statements against InnoDB tables, but again totaling several thousand in one transaction. Both of these problems occur even when there is relatively low activity elsewhere on the system. Code:
View Replies !
Slow Queries When Using Views Compare To Direct Table Queries
I’ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that I’m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one: MySQL Code: ...
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 !
Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc. I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables. My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page. Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?
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 !
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 !
Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this: Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined. The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.
View Replies !
Long Queries VS Multiple Short Queries
I have a php script that requests a very long query from our mysql database. It has lots of joins and accesses at least 5-6 tables. My question is should i break it up into smaller separate queries or leave it as one long one, in regards to best practices?
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 !
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 !
|
TRACKER
