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 Complete Forum Thread with Replies
Related Forum Messages:
Reduce Large Sql Queries
I will be working on a project which i can see having quite large tables of data, particularly a transaction table which will store credit and debits from users accounts. Obviously to get a balance I would just need to sum all the transaction on the account and that would produce the current balance. My concern is that the table may become very large and displaying the account balance may end being a large query. Is there a better may to approach this? I know i could have a balance field but I don't like the idea of that in case it become erroneous.
View Replies !
Large Number Of Queries
Ive currently got a loop on a php page which takes ~7000 rows from an sql table, parses information from it and does inserts/updates/deletes depending on the information. At the moment, it does a new mysql_query for each of the inserts/updates/deletes so it ends up doing ~10k queries each time its run. Im wondering if its better to use an array to store the queries and then put them all together (";" seperated) and run all the queries at once in the same mysql_query. One thing to bear in mind is some of the data involved in the queries relies on previous data from queries in the loop, would this cause any problems with the combined query
View Replies !
Large Queries Efficiency Problem
My Query was matching about 300,000 columns against about 1,500,000 columns - with no index. I was willing to wait. What happened, though, worries me. One CPU shot to 99.99% (not worrisome) and the others went idle (wtf?). The server stopped accepting new connections while this one was processing! We have the thread concurrency set to 50 (rarely gets over 15) so I can't figure out why it wasn't starting additional mysqld's to deal with the smaller requests. The server is a Dell 2850 with two 3GHz 64-bit Xeons. We're running MySQL 5.0.22 on CentOS 4.3. The system has 3GB RAM, and MySQL is allowed to take 2GB of that. Obviously, something in the my.cnf file is wrong - any guesses what? We set up initially using the my-huge.cnf example file that comes with MySQL.
View Replies !
Is Using Large Select Queries A Bad Practice
I have been programming a limited bbs system in php, and I was curious as to how I should approach some of the queries. For example, when I do post counts, should I run a query to count how many posts match the member's id, or will that eventually become slow when alot of topics become posted? I thought that maybe I could just simply update a post count value in the members table and use that. Also on the main page I have it count the number of total posts, and displaying the total number of topics as well as replies. Is this a bad thing? I somehow doubt that if I actually use these scripts, that my bbs will become large, but there is always a chance.
View Replies !
Running Large Insert Queries In My Web App
Here is a simplified explanation of something I want to do in my app: A user enters a sentence into a webform. I split the string into separate words. Create a query to see if any of the words match words in my members_keywords table (can be multiple instances of any one word). Query returns an array of member id's. I create a new insert query with one row for each member id returned in the previous select. The thing is, this one process could potentially return thousands or member id's and so the insert query would equally need to insert thousands of rows. I'm really concerned about the impact of this on the server as this process could potentially be running many times a day along with everything else in the web app. Am I right to be concerned? Is there a way to make it less intensive? Do it in batches? Timeouts would be a nightmare. As it is, the query would be ok, but I need to make things future proof.
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 !
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 !
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 !
One Large Call Or Several Smaller Ones
Say have 250,000 rows to play with, and I want to show the results for the last week, the last month, and then the previous 6 months - do I may a query for each month or do I retrieve the results for all 6 month and let PHP do the work for me? Whats the best way? What are the arguments for and against? Anybody got any good resources I can have a read of?
View Replies !
How To Put These Two Queries Together?
Howto put these two queries together? sql = "SELECT DISTINCT medium_id, COUNT(tag) AS count FROM tags WHERE tag = 'test' GROUP BY medium_id ORDER BY count DESC" This query is working. Id like to try something like: sql = "SELECT * FROM media WHERE id IN (SELECT DISTINCT medium_id FROM tags WHERE tag = 'test')" But how can I order the resut by quantity (counts of tag)?
View Replies !
1 Or 2 Queries
I have an established schema of databases and tables. A requested feature came up, that would allow for a user to add an item to the database, and not have it display on the website. Would it be better for the site to run a query along the line of SELECT * FROM db.table WHERE item=item AND view='yes' or Get the data then run a query against a separate table to see if the part is in the not viewable database
View Replies !
2 Queries
it won't run both queries and unite them - it's ignoring 'where approved='yes')? : Code: $sql = "SELECT CONCAT(firstname , ' ' ,lastname) AS FullName, username, homepage FROM customer WHERE approved='yes' AND lastname LIKE 't%' OR username LIKE 't%' ORDER BY lastname ASC";
View Replies !
WHERE (xyz OR Abc) Queries?
I'm just wondering if it's possible to include some sort of "OR" type condition in MySQL queries? I'm assuming it can be done, but I can't seem to find anything, and of course typing MYSQL +"OR" into a search engine doesn't bring the most specific of answers.
View Replies !
2 SQL Queries
2 questions I'm hoping someone will be kind enough to help me with: Firstly, is it possible to do something like: SELECT * FROM (SELECT `table_name` FROM `table_list` WHERE `type` = 4 LIMIT 1); I'm trying to select the table name to use by querying another table. Secondly, I'm trying to use variables to tidy up another query: SELECT @r := TRIM(TRAILING '_' FROM `long_room`) AS 'Room' ... WHERE @r LIKE 'WL214'; Am I using variables in the right way? This doesn't return anything and using "'Room' LIKE 'WL214'" doesn't work. I know I can use the whole Trim statement in the Where clause, but I want a tidier way of doing it.
View Replies !
Max Queries
Using JDBC and a connection pool, MySQL 5.0 on Debian Sarge, does anyone have any prior knowledge or a suggestion - should I limit the number of queries per connection? If so - to what number? (I've already got a maximum time to live for a connection, so at any case I'm not going to reuse a connection forever). Is using and reusing a connection memory-consuming, tending to grow over time?
View Replies !
Best GUI For Queries
I currently use myqlcc instead of query browser, but have been looking for a replacement that might combine some of the best features of both (plus one still being updated). I use the prog for writing and running queries, not so much for administering the db. I don't hear a lot written about them, but these are what I consider to be the best features of mysqlcc, which I can't find replicated in other progs. Can anyone recommend a GUI that has at least some of them? --Table/field highlighting (recognizes real db data and highlights it when entered) --Autocomplete (shows me possible matches from partial entries with a keystroke) --Parentheses highlighting (lets me see what is in a given start/end parenthesis) --update/delete results messages (shows xx rows matched, yy rows affected) --Tabbed multi-threading (of course) --Ability to grab all/some results and copy to the clipboard for export to Excel (not all have this) Anytime I try to google favored applications, I come up with Navicat, which seems to have the budget to buy search results; doesn't make it a good program (does anyone like it?).
View Replies !
Queries Per Second: 135.73
According to Runtime Information: Queries per second: 135.73 Is this an acceptable value, or is this a sign that something is probably wrong? The site gets about 300k page views per day.
View Replies !
Two Queries At The Same Time?
I have 2 queries: The {..} are precalculated values. At the beginning no item can be matched by both where clauses. 1) update tabelle set L = L + {MOVE} set R = R + {MOVE} where L >= {LEFT} and R <= {RIGHT} 2) update tabelle set L = L - {DIFF} set R = R - {DIFF} where L >= {OTHERLEFT} and R <= {OTHERRIGHT} My problem: If I run update 1 first, the update 2 can match items from where clause 1 because the update changed the values from L and R so that they can be found at where clause 2. And if I run 2 first its the same in the other direction. So how can I do the 2 where clauses first, and when mysql knows my items I would like to update so no item gets updatet twice.
View Replies !
How To Log Specific Queries
I've got this DB. Every few minutes new data comes in and is inserted. Now and then for some reason duplicate data comes through - not a big problem - unique key. However I'd like to log the insert failures, due to dupes, but I don't want to log all of the good inserts also.
View Replies !
Join 2 Queries Into 1
I would like to know how to join 2 queries so that the results of these 2 queries show up in the same query: SELECT b.bios_serial_number FROM bios b: SELECT s.system_name FROM system s; Basically I want to create a report that includes both system name and serial number. I'm new to this and none of the JOIn documentation was clear to me.
View Replies !
IN Vs Individual Queries
If I needed to fetch 3 entries from a database, is there a significant performance difference between the following? SELECT * FROM JOBS WHERE myCol IN ('val1','val2',val3'); Versus SELECT * FROM JOBS WHERE myCol ='val1'; SELECT * FROM JOBS WHERE myCol ='val2'; SELECT * FROM JOBS WHERE myCol ='val3';
View Replies !
Sub Queries In Mysql?
Is there a way to do something like subqueries in one query? I have a customer table (Klant) with contacts (KlantKontakt) and a address (Adres) table. The address table keeps a record of each different address for a customer including old addresses (to maintain history). If I want to retrieve a list of customers with their newest address, I have to do a max() to retreive the last address. I can do this in PHP in a seperate query but I like to do it in one query on the mysql prompt. Code:
View Replies !
Multiple Queries W/ 4.0.x (C API)
When executing multiple queries, which return a result set, must mysql_free_result be called after retrieving each result set using mysql_store_result? My concern is that if the result was not freed after each retrieval, that perphaps it may impose a memory leak. Or, does MySql reuse previously allocated memory for subsequent result sets? My app performs several back-to-back select and select/insert queries. Just need to know how to handle this action.
View Replies !
!= Operator In Queries
I have a query with the following syntax to find students that haven't voted: SELECT * FROM Students WHERE Students.StudentID != Voted.StudentID The thing is, the results I'm getting are basically the same as if has just used 'SELECT * FROM Students' alone. The StudentID list of 'Voted' is shorter than that of 'Students', as some students haven't voted yet. Anyone know what I'm wording wrong here?
View Replies !
Combining Queries
How can I get this into one single query? I donot want to run one by one manually. Code: UPDATE TABLE1 SET title = '0' where title = ' ' UPDATE TABLE1 SET name = '0' where name = ' ' UPDATE TABLE1 SET book = '0' where book = ' ' UPDATE TABLE1 SET descr = '0' where descr = ' ' UPDATE TABLE1 SET code = '0' where code = ' '
View Replies !
Executing Queries
I am pretty new to MySql so please bear with me. I was wondering if it is possible to format your queries as a url which can then be run through a web browser. For example, lets say I have a string stored in a table is it possible to do something like this: http://myHost/myDatabase/Select myColum from myTable If that can be done will the result display on the screen?
View Replies !
Difference Between These 2 Queries
Code: select * from foo where (col1 or col2)='foo' and (col3 or col4)='blah' and select * from foo where (col1='foo' or col2='foo') and (col3='blah' or col4='blah') shouldnt they return the same set? or am i missing some logic here
View Replies !
Number Of Queries
My team is working on transfering old db data to mysql. Its been configured somehow where it caps at 1 type of query at a time. Anyway to increase this? At one point it did around 700 at one time, but some configuration messed this up, how can we incrase this?
View Replies !
Less Queries Or Less Data
is it better / faster to have rows that hold more data, (ie putting in a varchar username instead of an integer userID to save having to query another table to get the username) or is it better the other way around? (of course unless the username could be changing which in this application the username cannot be changed)
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
Some Date Queries
I need help writing a query that will pull results from the database that are dated this month.So I want to see all results for December this month and January next month ... and so on. The field name is 'date.
View Replies !
Queries Pr.page
I use php/mysql for website, I am on novice level, - meaning I do not write php classes and such. I keep it real simple, with a simple db, full of records. question is; how many queries is normally run on a complexed site? take DevShed and imagine it was run on mysql, how many queries aprox does it need to get loaded? I am currenlty doing about 10 different queries pr. page to bring out some user details and different records of the user using 3-4 different db-tables.. and I wonder if this is normal, or if it's extremly bad programming / db design..? it loads fast enough, but it just seems alot.
View Replies !
Quotes And Queries
I'm wondering about the proper formatting of queries with repect to quotes. For example: SELECT something WHERE something_else = 'this' AND 'that' or SELECT something WHERE something_else = this AND that
View Replies !
Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form. My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot? Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?
View Replies !
Executed Queries
I am doing database synchronization project . I wanna know, is there any file where the mysql stores the currently executed queries that is list of currently executed queries so that I could extract those queries from there and send them in mail for synchronization. [note: I am using linux platform] I added log-bin=[querylog_filename] in my.cnf and using mysqlbinlog command I was able to view the currently executed queries stored in [querylog_filename].000001 binary file but I was not permitted to access .
View Replies !
Rating Queries
say I query a mysql database with keywords from a user(search engine) that results in many records. Is there a way to rate the results by having the records that have the most matches to the users keywords, with the goal of displaying them in descending order(a search engine)??
View Replies !
Combine Two Queries Cant Seem To Get It!
This is the problem, below is the table I need to query I need to select the id with only the mac address and also get the data of the keyword role. something like this: SELECT id from table where mac address = (macaddress) and select data from table where keyword = role; +-----+-------------+---------------+-------+ | id | keyword | data | flags | +-----+-------------+---------------+-------+ | 263 | record_in | On-Demand | 0 | | 263 | role | default | 0 | | 263 | callerid | device <263> | 0 | | 263 | allow | | 0 | | 263 | qualify | no | 0 | | 263 | callgroup | | 0 | | 263 | pickupgroup | | 0 | | 263 | nat | yes | 0 | | 263 | mailbox | 263@default | 0 | | 263 | username | 263 | 0 | | 263 | mac | 00041324668B | 0 | | 263 | record_out | On-Demand | 0 | | 263 | context | from-internal | 0 | | 263 | dtmfmode | rfc2833 | 0 |
View Replies !
Two Queries Joined
below is the table I need to query I need to select the id with only the mac address and also get the data of the keyword role. something like this SELECT id from table where mac address = (macaddress) and select data from table where keyword = role; Please help Thanks +-----+-------------+---------------+-------+ | id | keyword | data | flags | +-----+-------------+---------------+-------+ | 263 | record_in | On-Demand | 0 | | 263 | role | default | 0 | | 263 | callerid | device <263> | 0 | | 263 | allow | | 0 | | 263 | qualify | no | 0 | | 263 | callgroup | | 0 | | 263 | pickupgroup | | 0 | | 263 | nat | yes | 0 | | 263 | mailbox | 263@default | 0 | | 263 | username | 263 | 0 | | 263 | mac | 00041324668B | 0 | | 263 | record_out | On-Demand | 0 | | 263 | context | from-internal | 0 | | 263 | dtmfmode | rfc2833 | 0 |
View Replies !
Joint Queries
I am verymuch new to Database(MySql) and to the forum too. I have created a database with three tables company,locations,officers And my table description is like this: Company Table __________________________________________ companyid companyname yearfounded __________________________________________ 1 Abc 2000 2 Xyz 1998 __________________________________________ Location table ________________________________________________________________________________ locationid companyid address1 address2 state country ________________________________________________________________________________ 1 1 street1 suit abc District of Columbia USA 2 1 street2 suit 100 Illionis USA 3 2 Abc Street xyz Kentucky USA _______________________________________________________________________________ Officers Table ________________________________________________________________________________ officerid companyid Name Designation phone fax email ________________________________________________________________________________ 1 1 xyz ceo 12345 45678 xyz@abc.com 2 1 wxy director 54786 32456 wxy@abc.com 3 2 pqr vice president 58884 45678 pqr@abc.com ________________________________________________________________________________ Now is it possible to get the information from all the tables at once using a single query. I heard that using joint queries it is possible,
View Replies !
1st Of Mulitple Queries
I've created multiple tables within MySQL, web forms for individual tables in PHP that front end MySQL and need to create queries that manipulate the data stored in MySQL. For example, I have a table "Pets" that lists "Cats", "Dogs", etc. Each of these have a related table with sub-categories: e.g. the Dog table has "Small", "Medium" and "Large" Dogs. Each of these tables is where the user inputs the type of dog (e.g. in "Small Dog" table the user would input a specific pet that falls in this small sub-category). In another set of tables I have a similar process that lists anatomy of the pet. So there is a category table with entries like "External" and "Internal". Each of these have sub-category like "limb", "head", etc. In another set of tables there is a similar process where there are categories such as "Injury" and "Disease" with sub-categories such as "Internal Injury" and "External Injury" where the user can input specific internal injuries that may affect a pet. Each of these tables has just an ID key, the name field and a description/notes field. I have not created any sort of relation between these tables other than names. Don't know if I need to or how to in MySQL. Here is the query(s) I have no idea how to create: Once the database is populated with information on the pets, anatomy and injuries/diseases. I need the user to be able to open up a form that shows individual pets and be able to assign anatomy entries to them (if they have not already been assigned)(Since I'm using web forms, it would be nice to be able to have all previous entries listed and the user just checks a box to signify inclusion for this pet), and diseases/injuries that affect the anatomy. So you may have a small dog, "Spot", that a user has specified certain parts of his anatomy (be able to assign single to multiple anatomy entries to a single pet) and has injuries/diseases affecting each individual anatomy entry (single to multiple disease/injury entries to each anatomy entry).
View Replies !
In Line Queries
I have a MySql database that runs well on a server running MySql 4.1.11, I want to run the same tables and queries on a Server running MySQL 4.0.24 the tables etv have moved across fine but a query which has the syntax SELECT `tbl_pilot`.`Pilot_Name`, `tbl_calendar`.`event_finish`, `tbl_calendar`.`received`, `tbl_calendar`.`event` FROM `tbl_calendar` Inner Join `tbl_pilot` ON `tbl_calendar`.`userid` = `tbl_pilot`.`Pilot_id` WHERE "2006-10-10" BETWEEN date(`tbl_calendar`.`received`) AND date(`tbl_calendar`.`event_finish`) ORDER BY `tbl_calendar`.`received` ASC fails on the earlier version of MySql it does not like the date() function yet that looks valid to me.
View Replies !
Fulltext Queries
I have a table of keywords one record of which contains the keyword "thanksgiving". The table has two indexes - one is of the ID field, one is a FULLTEXT index on the keyword field. I need to create a query that returns that record when a user enters either "thanks" or "thanksgiving" in the keyword entry form. I've tried the following two queries neither of which work... SELECT * FROM tablename WHERE fieldname LIKE 'thanks' SELECT * FROM tablename WHERE MATCH (fieldname) AGAINST ('thanks') Both of the following queries return the record in question... SELECT * FROM tablename WHERE fieldname LIKE 'thanksgiving' SELECT * FROM tablename WHERE MATCH (fieldname) AGAINST ('thanksgiving') What do I need to do to return the record when the shorter version of the word is entered by the user?
View Replies !
Ordering Queries
I am running a MySQL query using product ids like this: SELECT * FROM products WHERE product_id=510 OR product_id=15 OR product_id=76 Sometimes there can be a string of up to 100 product IDs in 1 query. The problem is, I want the results to be displayed in the order that I am listing the product_ids. In other words, I want the results to be displayed in the order 510, 15, 76. However, when I run the query, the results are displayed in a different order such as 15,76,510. How can I get the results to be displayed in the order that I have passed the IDs in my query?
View Replies !
UPDATE ... SET Queries
I have two database tables, one called Transactions and another called TransactionDetails. Transactions has primary key TransactionID, which is a varchar. TransactionDetails has a primary key (TransactionID, TransactionType , TransactionAccountNumber) all three of which are varchars. TransactionDetails is about five times bigger than Transactions. Now, these tables are very old and we've run out of ID numbers to use, so I have written a new C++ program that will create new TransactionID numbers based on what the old ones were as well as some other conditions such as dates and sequence numbers. However, the problem arises that I can't just go editing these tables and putting the new ID numbers in place; I have to keep the data consistent. In other words, if I change an ID number in the Transactions table, I have to make absolutely sure I've also changed every single instance of that TransactionID in the TransactionDetails table to the new ID number as well. As it is now, I have a C++ for loop that looks at the Transactions table and goes row by row and updates each individual TransactionID number in Transactions and then looks through the entire TransactionDetails table for all instances of the old ID number and changes the ones it finds. My code looks kinda like this: .....
View Replies !
About 30 Queries Per Second - With No Users?
In MySQL Administrator under health and "status variables", the "questions" variable (number of queries sent to the server) seems to increase by about 30 every second if I continually press "refresh" and I don't know why. There are no users active on the only website that uses MySQL, so where are the queries coming from? Similarly... in one of my tables - again if I continually press refresh - the number of rows vary between 1700 and 2000... why would this happen? I'm certain that no one is using the site and the behaviour continues even when I restart the MySQL service, restart IIS and restart the server itself.
View Replies !
|
TRACKER
