Performance Needed
I have two tables. One for products and one for reviews.
I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. All good so far.
I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server?
Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that?
View Complete Forum Thread with Replies
Related Forum Messages:
Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition date, account and invoice number are indexed Database size 18 million rows I am querying (selecting) columns of a date and an account our tester program that opens a socket to the Mysql database and does a select for the above n times each time the date and the account is randomized to minimize hits on records closeby. This program will perform over 1000 queries per second. At the end, the Mysql socket will be closed Even when the socket is closed each time, I still get 400 queries per second. When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results. Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.
View Replies !
Query Needed
i have a little problem with figuring out how to make a query for a certain table. The table consists of two columns: word_no and example_no. It is very likely that an entry word_no refers to more than one entries in example_nr. Now i would like to find out how many entries with the same example_nr are there for word_nr x AND word_nr y together.
View Replies !
Timezone Help Needed
I am in Washington State, Pacific timezone. However, our business day runs from 7:00am-7:00am. I have found if I change the system time on the MySQL box to the Japanese Timezone this works in "fooling" the system into thinking 7:00am local time is midnight, the start/end of a business day.. the problem comes when trying to update our intranet website, with the differing timezones..the huge difference in time will not allow updates with our ppl who use front page. (yes, I know notepad would be a better choice then front page. Not my call) I have tried unsuccessfully to change the time zone in the [mysqld] & [mysqld_safe] sections of the startup INI file. When I try to force MYsql into the japan time zone the server fails to start and an error message pops up failed to start unexpectedly,
View Replies !
Help Needed With DB Structure
I'm finally get a grip on using databases in my scripts, but I'm still lacking a bit in exactly how best to construct my databases ... so I'm hoping that I might be able to get some help with that here I currently have a MySQL database with one table called "users." In it will be held the registration information for everyone who signs up to the site: username, password, date of registration etc. etc. Now I want to link that table with another table that will contain information regarding the car that the user drives. This information will be collected from a second form and will contain data such as type, make, year of construction, colour etc. etc. What's the best way of linking these two tables together in order to help with running searches or scripts in the future? I was planning on inserting the relevant username into the second table as well so that I can run searches across both tables ... but is that the best way of doing it?
View Replies !
MYSQL Help Needed
I am having troubles trying to create my first table in mysql I am using version 5.0 I have entered the code below in mysql: mysql>CREATE TABLE table_name ( -> column_1_name column_1_type column_1_details, -> column_2_name column_2_type column_2_details, -> ... ->); and i get the following error: 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 'column_1_type column_1_details,column_2_name column_2_type column_2_details,' at line 2
View Replies !
Packing Needed For MySQL
I am still playing around with databases. So here it goes: Is packing needed for MySQL (5.0)? I have a new DB, which already is 178 MB. Will that continue to grow, when I add and delete data? Does it reclaim freed space and deleted rows? If not, is there a (automated) way to pack tables using SQL or something simple, which I can call from my app?
View Replies !
Select Statement Needed
The sending page allows the user to choose which search criteria he wants, so this (and many other) pieces of the select statement may or may not be present. The statement always starts with SELECT * from logdata where driver = '$_COOKIE[username]' If the user has selected them and supplied the proper data for a search, other elements will be added to the query string. For example, it may look like SELECT * from logdata where driver = '$_COOKIE[username]' AND amount >= $_POST[txtLowAmount] AND amount <= $_POST[txtHighAmount] AND restaurant regexp??? I'm not even sure if that is where a regexp belongs or if that is how it should be used. Ultimately, I would prefer it be non case sensitive. Here are some examples of what I want: If the user string is "CHIL" Chili'strue Charlie'sfalse CHILtrue chfalse
View Replies !
Lemans Terms Needed
So can someone tell me in lemans terms what does LEFT JOIN do in MySQL? I see it more and more and have read the manuals but still don't understand. Maybe it's a mental block going on.
View Replies !
Simple Code Needed
Just need a little code to transfer a user to a custom error page if connection to the database fails. Like: If(no connect){ header("location: error.php"); } Just need the "no connect" part I guess.
View Replies !
MySQL Join Needed
I'm pretty new to MySQL and I don't really get the whole join thing, although I understand the theory behind it I have problems when putting it into practice. Basically I'm using PHP with MySQL back end and I've got two tables: TEST QUESTION The primary key of Test is "testno" which is an auto_increment and question has "testno" as a foreign key, so I need a join that will insert the value of "testno" into question when ever a record is entered into TEST based on the "testname" which the user enters into a form. Let me try and clarify: $testname = $_POST['testname']; sql="INSERT into question test.testno where testname = '$testname'"; Obviously this will not work but what I need in effect is a query which carries out this function.
View Replies !
M2M Relational Query Help Needed
I have 3 tables, below are a list of the significant fields in each. Stories * story_id * story_date Editions * edition_id * publication_id Story_Edition_Jct * story_id * edition_id What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition. Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication. The following query is what I've come up with:
View Replies !
Help Needed Writing Query
I'm trying to make a query to return 10 sorted values, but I need to calculate the value to sort by, ie its not stored as a field in the table. So say I had some data like this... id basicrank time 1 5 40 2 7 33 3 3 55 etc. I want to sort by (basicrank minus (currenttime - time)), so newer records get some priority, returning highest score first. My query could then be something like this, given current time of 60. SELECT id FROM table ORDER BY (basicrank-(60-time)) DESC LIMIT 10
View Replies !
Two (rather Complex) Problems. Help Needed!
Problem 1 I am developing a system where members of a site can submit graphic designs to a themed pack. Members can then view and rate the submissions from 1-5. This has all been accomplished, but I have encountered one problem. When listing the themed packs currently in progress, I want to be able to list how many submissions there are, how many of these submissions have a rating above 3.0, and how many of these submissions are above 4.0. I've been able to flawlessly count how many submissions there are without referring to their ratings with this query: select p.id as id, p.title as title, p.body as body, p.reltime as reltime, p.quota as quota, count(s1.id) as subcount_ns, from packs as p left outer join submissions as s1 on s1.pack = p.id where s1.state > -1 and p.released = 0 group by s1.pack order by p.id asc; How can I also, within this query, count how many submissions have a rating above 3.0 and count how many submissions have a rating above 4.0? I'm not sure how to do this, since I have already joined the submissions table to the packs table. The ratings table (sub_ratings) looks like this: id | subid | userid | rating | time The subid column refers to the submission's id, the userid column refers to the id of user who rated the submission, and the rating is an integer between 0 and 5. -------------------------------------------- Problem 2 For the submission system, some users choose to collaborate with other users on one submission. In this case, both of their userids are grouped together in the "creators" column of the submission, being separated by commas. Here is an example: 1,43,65 This would be an example of three users having collaborated on one design. Is there any possible way that I could, within one mysql query, determine the usernames of each of those users and return it as a column with the requested submission? I'm looking for something along the lines of php's "explode" function, but in mysql. Thanks in advance for the help that anyone offers.
View Replies !
SQL SELECT Statement Help Needed
I have three databases, like so: 1. Visitors: vid(int) | vname(varchar) 2. Events: eid(int) | ename(varchar) 3. Participation: pid(int) | visitor(int from vid) | event(int from eid) The participation table is a criss cross reference table to associate visitors with events. Given the vid(from visitor table) how go i query the database to get all the events a given visitor is participating in?
View Replies !
Help Needed To Join Tables.....
I'm starting into mysql these days and I have some tables created function_detail +------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+------------------+------+-----+---------+----------------+ | function_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | function_name | varchar(128) | NO | | | | | function_description | varchar(512) | NO | | | | | function_image | varchar(256) | NO | | | | | function_image_caption | varchar(128) | NO | | | | | function_extras | varchar(64) | NO | | | | +------------------------+------------------+------+-----+---------+----------------+ option_detail +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | option_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | option_name | varchar(64) | NO | | | | | option_description | varchar(256) | NO | | | | | option_image | varchar(256) | NO | | | | | option_image_caption | varchar(128) | NO | | | | | option_extras | varchar(64) | NO | | | | +----------------------+------------------+------+-----+---------+----------------+ product_detail +---------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default |Extra | +---------------------------+------------------+------+-----+---------+----------------+ | product_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_name | varchar(64) | NO | | | | | product_short_description | varchar(256) | NO | | | | | product_long_description | varchar(512) | NO | | | | | product_series | varchar(64) | NO | | | | | product_heads | varchar(64) | NO | | | | | product_family | varchar(64) | NO | | | | | product_category | varchar(64) | NO | | | | | product_image | varchar(256) | NO | | | | | product_image_caption | varchar(128) | NO | | | | | product_catalog | varchar(512) | NO | | | | | product_status | varchar(64) | NO | | | | +---------------------------+------------------+------+-----+---------+----------------+ product_function_detail +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | product_id | int(10) unsigned | NO | | 0 | | | function_id | int(10) unsigned | NO | | | | +-------------+------------------+------+-----+---------+-------+ product_option_detail +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | product_id | int(10) unsigned | NO | PRI | 0 | | | function_id | int(10) unsigned | NO | | | | +-------------+------------------+------+-----+---------+-------+ product_technology_detail +---------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+-------+ | product_id | int(10) unsigned | NO | PRI | 0 | | | technology_id | int(10) unsigned | NO | | | | +---------------+------------------+------+-----+---------+-------+ these tables are meant for: (let's see if I can explain correctly) Product Details would contain all the details for a certain products Function Details would contain all the functions performed by these products. Technology Detail would contain all the technology embeeded in these products. Some products may have either the same technology or the same functions (with the same descriptions) but affirmatively not the same product details (description product name & etc). For Instance Product A would perform function a,b,c,d and e and would have technology X,Y and Z product B would perform function f,g and h and would have technology W, Y and Z. product C would perform function b,c and I and would have technology O,P,Q and X. How can I link the three tables? Meaning that product_detail ---> function_detail when the linkage between the two of them resides in product_function_detail? Here is something I've tryied SELECT function_id, product_id as producto FROM product_function_detail WHERE product_id = ƈ' ORDER BY function_id asc; SELECT distinct function_name, function_description, function_detail.function_id FROM (select function_id, product_id as producto from product_function_detail WHERE product_id = ƈ' ORDER BY function_id asc)as xref, function_detail WHERE producto=ƈ' BUT so far, no luck.
View Replies !
Wordpress Database Help Needed!
I'm trying to import some content into the wp_posts database, which works, but I can't figure out how to make it automatically publish at a later date. When I publish the imported content manually (eg. edit timestamp and hit publish) it works, but when I try and upload that into the database, it doesn't seem to register. I'm doing some traveling soon and I don't want the blog to lay dormant! Here's my SQL for an example post: Quote: INSERT INTOwp_posts(ID,post_author,post_date,post_date_gmt,post_content,post_title,po st_category,post_excerpt,post_status,comment_status,ping_status,post_password, post_name,to_ping,pinged,post_modified,post_modified_gmt,post_content_filtered ,post_parent,guid,menu_order,post_type,post_mime_type,comment_count`) VALUES (123, 1, ��-11-21 17:45:39', ��-11-21 23:31:15', 'I AM THE POST CONTENT', 'THIS IS THE POST TITLE', 19, '', 'future', 'open', 'open', '', 'this-is-the-post-slug', '', '', ��-12-01 17:45:39', ��-12-01 23:31:15', '', 0, '', 0, 'post', '', 0);` So all times are set in the future, and the post_status is "future". Why is it not publishing when the time comes around?
View Replies !
Stored Procedure Needed
I have a table named players with these fields: playerid {int} teamid {int} jersey {int} playername {varchar(50)} age {int} And a table named pitchcounts with these fields: id {int} gamenum {int} playerid {int} pitchcount {int} Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed and the days rest required and next availability date of the pitcher based on the criteria below: In-House Ages 13-16 (Babe Ruth) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-95 3 Rest ******************************************************** In-House Ages 11 & 12 (Majors-American, National & Continental) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-85 3 Rest No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week. ******************************************************** Travel Ages 8, 9, 10, 11 & 12 -------------------------------------------------------- Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House. If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play. ******************************************************** In-House Ages 10 and 9'S in Majors (Majors American, National, Continental) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-75 3 Rest ******************************************************** Double A In-House (AA) -------------------------------------------------------- No more than 40 pitches in a game or 80 for a week. ******************************************************** Additional In-House Notes -------------------------------------------------------- If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch. Rest Examples: if 20 or less on Monday, available on Tuesday if 21-40 on Monday, available on Wednesday if 41-60 on Monday, available on Thursday if 61 or more on Monday, available on Friday
View Replies !
Libmysqlclient.so.10 Is Needed By (installed) Mod_auth_mysql-1.11-12
Redhat 9.0 Mysql 3.23.56 ==> Running I want to upgarde to 4.0.13 but this is the error it says: [root@localhost downloads]# rpm -Uvh MySQL-server-4.0.13-0.i386.rpm warning: MySQL-server-4.0.13-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-12 libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3 libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2 If i install with -i will it install with the backward compatibility, it should not break those dependencies.
View Replies !
Backups Of Raw Db Files - Indexes Needed?
Right now I am backing up mysql 4.0.14 on Windows 2000 with a scheduled batch file that shuts down the mysql service - copies the entire data directory to a backup directory, and restarts the mysql service. this ackup directory then goes onto tape a couple of hours later via veritas remote backup agent. I have tested a restore of this backup method and it works fine. Now I would like to add some more indexes to a table that has 11 million rows, however the index files will be very large. Is it necessary to backup index files? In case a restore was needed, I could just recreate the indexes manually (via a batch file with the correct index commands). Will mysql choke if the table definition shows an index - but the index file is missing? are there any "bad things" that will happen if index files are missing?
View Replies !
Help Needed For Inserting Using Variables In A Loop
im trying to dod a simple insert into my database but the thing is instead of having stable variables i need to input balues from within a loop. heres the code but i cant figure out whats wrong with it: :mad: <? include "login.inc"; $i=1; while ($i < $num): $query="insert into A(B,C,D) values('$E$i','$F','$G$i'"; $result = MYSQL_QUERY($query); $i++; endwhile; print("thank you"); ?> so wot should happen is E1, F, and G1 are entered into the database the first time round and then E2, F, G2 and so on until i=$num.
View Replies !
Whats Needed On Client PC And Server?
I'm currently using MySQL 5.0.9 with Visual C++ 6.0 and Qt. My project is on the server, with all the dll's needed as well as MySQL with my database. I can only access my database on the server from a client station if this client station also has MySQL installed. It doesn't even execute the queries in my code. Do I need to use server scripts and if so how do I do it? Is there in Windows something like a mysql-server and mysql-client package? Do we need to install MySQL on all of the client stations?
View Replies !
Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table: CREATE TABLE b5_assignment_lookup ( as_id int(11) NOT NULL auto_increment, as_blog int(11) NOT NULL default Ɔ', as_blogger int(11) NOT NULL default Ɔ', as_milestone enum('start','finish') NOT NULL default 'start', as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`as_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ; By running this query, I get the following resultset: mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= ��-09-30' +--------+----------+-------------+---------------+---------------------+ | as_id | as_blog | as_blogger | as_milestone | as_timestamp | +--------+----------+-------------+---------------+---------------------+ | 87 | 89 | 41 | start | 2006-05-01 00:00:00 | |208 | 89 | 41 | finish| 2006-09-02 11:55:27 | |209 | 89 | 103 | start | 2006-09-02 11:55:27 | +--------+----------+-------------+---------------+---------------------+ 3 rows in set (0.00 sec) What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41. What I really need to grok is all bloggers who blogged all or a part of a range of dates. For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02. I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25. The flags are the start/finish, obviously.
View Replies !
Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations. First, here's the situation. I want to store information about all the cards in a Tarot deck. In the deck, there are two types of cards; namely the major and minor arcana. Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type. For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table. Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign. Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters. Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification. So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.
View Replies !
UPDATE / REPLACE - Assistance Needed
I have a table called COMPANY which holds 5,000 records Fields: Company_ID (int:2), Name (varchar:100), State_2letter (varchar:2) i.e. 1, ABC Company, AL I have another table called STATE (51 records) Fields: State_ID (int:2), State_2letter(varchar:2), State_Name (varchar:100) i.e. 1, AL, Alabama I want to change the structure of the COMPANY table to hold the STATE's table STATE_ID record to correspond with the State_2letter. But in the same time change the STATE_2letter to be an INT format too. So, my COMPANY table will look like this... i.e. 1, ABC Company, 1 I just cant find my solution on how to to an UPDATE command correctly.
View Replies !
Help Needed Merging 2 Different But Similar Tables
Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla? Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries. I tried exporting the source database and then renaming all the database names to match the ones I want to merge into but all that happened was a new table was created - no merging. I really just want to select the relevant 3 fields and match them up with the fields in the database table I want to import into. I have an old program that listed all the fields of database 1 in one column and database 2 in another and all I had to do was drag the fields across to make a match and the program did the rest. Perhaps there is a similar one out there? What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are: id - title- body IN other words, just the bare essentials. The target files has a table called jos_content with columns: id - title - title_alias - introtext - fulltext in that order and others but are not important. I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time! It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials.
View Replies !
Help Needed With Load Data Infile Syntax
I'm relatively new to MySQL, and I've never used the Load Data Infile command. I'm having trouble getting it to work correctly. I have a comma separated file generated as output by another program that I can't seem to parse correctly. It contains data similar to the following: VAL1, LASTNAME, FIRSTNAME, ADDRESS1, ADDRESS2, ETC1, ETC2, ETC3 VAL2, "LASTNAME, JR.", FIRSTNAME, ADDRESS1, ADDRESS2, ETC1, ETC2, ETC3 The "LASTNAME, JR." hoses up the import. It imports "LASTNAME into one field and JR." into another.
View Replies !
Manual Cascade Delete Implementation Needed
I am trying to manual clean up a child table when parent table records are deleted. I tried: DELETE FROM agencyImage WHERE imageID IN (SELECT images.imageID FROM images LEFT OUTER JOIN agencyImage ON (images.imageID=agencyImage.imageID) WHERE agencyImage.imageID is null) This failed because you can't delete from a table that you are looking at. So how do I delete the record from agencyImage table if no record exist in the parent images table?
View Replies !
Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria? $query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";
View Replies !
VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table. SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name
View Replies !
MYSQL Query Speed + Explanations Needed
Sometime I need to delete results set of the some query. Like DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) But when I have a table with 100k+ records it become a big problem. Script just hangs up forever As I understand, mysql get each id from table `table` and reexamine SELECT? I tried to create temprary table and filled it with select like DELETE FROM table WHERE id IN (SELECT id FROM `some_temp_table`) But its hangs up too... EXPLAIN do not works for the DELETE ... statement, manual do not helped. So, how does this query work "on the low level"? DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) Any suggestion how can I improve speed for the large tables?
View Replies !
Advice Needed Login/registration System With MySQL 5/PHP
I want to setup a login/registration system for my website with MySQL 5 / PHP. Most of the job should be the standard stuff: register form, login form, forgot password form, etc. Can anyone advice me how to begin ? Are there any good tutorials which i should use or are there freeware or cheap commercial tools which i should use ? Or maybe you recommend me to an awesome programmer you know who does this job at low cost ? - a MySQL/PHP Newbie
View Replies !
Guidance Needed For Making Random Queries In A Huge Table
Bigint id Varchar name Enum gender Tinyint age Varchar city Smallint countryAndstate (All country+state combinations have a unique id number stored here) id is some unique random number generated per user and is the primary key, and gender, age, city and countryAndstate are indexed individually. Now, 70% of our queries are like: "where id=1234" or "where id in(1234,5678)", which work fine. However, the other 30% queries can be: 1. Give me 100 rows randomly 2. Give me 100 rows randomly that satisfy certain conditions (like age=33 or city='Boston' or a combination, like gender='Male' and city='San Francisco' and countryAndState=101)
View Replies !
Joins Across Multiple Tables, Influence Which Item To Take If Only One Is Needed Of Each Kind
I'm trying to get a join across multiple tables to work where I have to walk across several pivot tables to get one of the selection criteria. What I want is the latest entries for each supplied wp_terms (the category) in the wp_posts table. While fetching each one with its own query is easy, I'm stumbling when trying to build a query that returns the latest entry for a list of supplied categories. Group by doesnt seem to cut it, since it takes place before the order by. Here is the query and its output (without grouping, this will return all entries for the specified categories): ....
View Replies !
Models Where The Years Overlap (was "Query Help Needed")
I'm trying to formulate a query for the following example: Table: model_by_year model start_year end_year model_1 2001 2004 model_1 2003 2004 model_2 2000 2005 model_2 1998 1999 model_3 1998 2003 model_3 2002 2006 I want a query that will return all like models where the years overlap. In other words, I'd want to return model_1 and model_3, but not model_2.
View Replies !
Get Goals/assists For Each Game, And Player Name (was "Little Help Needed")
I have the following query: $result = @mysql_query("SELECT g.date , g.weeklabel , g.gmtime , g.home_score , g.away_score , g.inputted , th.teamname AS hometeam , th.id AS homeid , ta.teamname AS awayteam , ta.id AS awayid FROM weekly g JOIN teams th ON th.id = g.home_id JOIN teams ta ON ta.id = g.away_id ORDER BY g.date ASC, g.weeklabel, g.gmtime"); This works fine. However, I have another table called stats. The fields in this table are playerid, match_id, goals, assists. I have another table called league, with fields playerid, playername. I need to join the stats table to this query in order to get the goals/assists for each game, and I would also like to join the league table in order to get the players name instead of playerid. I have messed around with many different combinations and cant seem to get this particular query to work.
View Replies !
IO Performance
I'm running a 5.0.51a mysql server on pentium M. My application is made of 30 myisam tables written every 2 seconds and a very few reads (1 evry hour) from the whole table. Each table is a kind of circular buffer (fixed number of line, current line). Each line of a table contains a medium blob whith fixed size (about 70k). I know the average output (Kb/sec) from the client to th server, says 200 Kb/sec. When trying to measure this flow with 2 invocations of "show global status like '%Bytes%'; followed by division by the elapsed time, I find between 1 and 2 times more (400 Kb/s). When I measure the output to the disk with the iostat command (iostat -d -k 10) I find 4 times more (800 Kb/s) and a lot of read Kb/s (100 kB/s). Is this normal or is there some tunning to do ? During this time, the iowait rate is very high (30%). Is this normal or is there some tunning to do ?
View Replies !
Best For Performance
It's a web application, accessed via internet, with around 100.000 clients and each client have around 10 users. This web app have a database of around 100 tables. Ok, the question is: Is it better to have each client with it's own catalog on the database (each catalog with around 100 tables) or is it better to use one catalog for all the clients (the tables would have a lot more records this way, but only one catalog)? I'm considering performance.
View Replies !
Which Is Better In Performance
country (id) country (1) America (2) Germany (3) Canada (4) China (5) Egypt (6) Russia (7) Korea (8) France (9) Brazil city (id) city (1) Berlin (2) New York (3) Cairo (4) Paris (5) Seoul (6) Peking (7) Chicago (8) Moscow (9) Denver I have two tables like the above. And I have one of these tables below. message1 (id) (city) message (1) (2) I love New York. (2) (5) Kumkang mountains are beautiful. (3) (4) I was born in France. (4) (6) Great Wall is great. (5) (7) Chicago is a big city. message2 (id) (city) (country) message (1) (2) (1) I love New York. (2) (5) (7) Kumkang mountains are beautiful. (3) (4) (8) I was born in France. (4) (6) (4) Great Wall is great. (5) (7) (1) Chicago is a big city. code1 select city, country from message1 left join city on city.id=message1.city left join country on country.id=city.country code2 select city, country from message2 left join city on city.id=message2.city left join ccountry on country.id=message2.country I think The codes above have a same result.(sorry, I didn't test it.)
View Replies !
Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?
View Replies !
Innodb Performance (CPU, RAM)?
I have a table with approx. 150,000,000 ++ rows. It should be approx 80 byte for each row. Now I'm planning to use it with mysql/innodb. Can you guys suggest anything about this. I have many questions... Can Innodb work well with such tables? Will queries (select/update) be slow? What hardware do you suggest, cpu/ram?
View Replies !
Balance Performance
My computer is AMD athlon 64 3400+ with 1.5G DDR. when i execute the following sql in mysql 5.XX. select c.filename, c.topic from articles c where c.topic in (select a.topic from articles a group by a.topic having count(a.topic)>1) order by topic; It eat up all the cpu power , and the mysql server stop responsing until end of this query. Even my apache stop responing too. How to fix this? How can i set the CPU limit of each query connection?
View Replies !
Performance MySQL With ADO VB
I'm developing an VB application which uses the SAX parser and ADO VB to insert.update data in in MySQL. The perormance is very bad. If I use the same data and insert/update to a MS Access database it takes 14 minutes. But using MySQL it takes about 5 hours! Here's the piece of code which takes the most time. Does anyone have an explanation for this? Code:
View Replies !
MySQL Performance
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion times with many simultaneous connections (hundreds). To our surprise we did not find significant differences.Each of our servers (2 and 4 CPU machine) has one 1Gbit network connection. I have feelings that the machines are the network bounded, not the CPU bounded. Could it be true? How to verify it?
View Replies !
Mysqlcheck Performance
I launch the command line: "mysqlcheck --repair dbname" over a database with only one myisam table of more than 10.000.000 rows and 5 GB. The elapsed time to repair is about 1 hour! Is possible to increase speed?
View Replies !
Low Performance With Linux SMP
I use #mysql -V mysql Ver 12.21 Distrib 4.0.15, for suse linux (i686) with a SMP-kernel When I used this version with a kernel not SMP mysql was very very fast but now with the SMP-kernel the mysql is very very slow. I takes several minutes to make an INSERT on a table with near 50.000 Records Is there any problem with mysql an SMP? is a problem of the version I have installed?
View Replies !
|
TRACKER
