MYSQL :: Long Queries VS Multiple Short Queries

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 Priority
I have a 120M record table that needs to have indexes added as a
background process. While running the CREATE INDEX command, which is
taking several days, all other SELECT queries on the same machine (on a
different database) are slowed dramatically.
Is there a way to reduce the CREATE INDEX priority so that other SELECT
queries can still function at normal speed?
The OS is linux, and I have tried to 'nice' the caller script, which
works for shorter queries, but presumably the longer queries are
running entirely in the MySQL process that isn't 'nice'.

View Replies !

Restrictions For Long Sql Queries
are there any restrictions for long sql queries in mysql? I havnt found any info about that. Example:

select id from example where id > 1000 and ( ( suche like 'wasser %' or suche = 'wasser,%' or suche = '%,wasser,%' or suche like '%,wasser %' or suche like '% wasser,%' or suche like '% wasser %' or kat1 like 'wasser%' or kat2 like 'wasser%' or kat3 like 'wasser%' or motiv like 'wasser') and ( suche like 'sturm %' or suche = 'sturm,%' or suche = '%,sturm,%' or suche like '%,sturm %' or suche like '% sturm,%' or suche like '% sturm %' or kat1 like 'sturm%' or kat2 like 'sturm%' or kat3 like 'sturm%' or motiv like 'sturm') and ( suche like 'glas %' or suche = 'glas,%' or suche = '%,glas,%' or suche like '%,glas %' or suche like '% glas,%' or suche like '% glas %' or kat1 like 'glas%' or kat2 like 'glas%' or kat3 like 'glas%' or motiv like 'glas') )

View Replies !

Extremely Long Sql Queries
I have a website based on phpnuke and phpbb (run on mysql). Recently i am experiencing extremely high loads on the server. When i checked the sql records i saw several queries like this one :

# User@Host: yehidataf[yehidataf] @ localhost []
# Query_time: 217216307 Lock_time: 0 Rows_sent: 2 Rows_examined: 4
SELECT * FROM nuke_banner WHERE type='0' AND active='1';

When i try to run the query by myself it takes normal amount of time (0.0008 sec or less). What can cause this jump in execution ?

my host said it might be caused by an error with php (loop in the code) but that does not seem logical as a loop should create repeated calls to the sql database (shouldnt it) ?

View Replies !

How To Prevent MySQL Queries From Taking Too Long
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours.
My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries).

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 !

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 !

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 !

Multiple Queries
I want to make two queries with a single script, because I want to be able to output different results, in the event that there is no match for the first query, which searches for a variable passed from a select menu. But there is one option in the select menu that has no match in the db, so I want to return different results, which in this case, is all rows (All Models) for the given column (Models).
I have gotten this to work, but it doesn't seem intuitive. I tried using the if and else clauses with the actual queries, but without success. Would someone be willing to show me the proper way of going about this--how to script it--but produce the same results. Code:

View Replies !

Multiple Queries?
well not really sure how to explain this one.
I wrote a reate me script. One tha tallow syou to add a photo and leave comments for a photo. What I want to do is allow the admin to search thorugh all the comments. sure, easy query to just search comments, but I want them to be allow to search for a photo and grab the commetns on that specific photo.
the photo table is linked to the comment table by photo.picid=comment.subjectid. I also want to grab the user info in this and that is linked to both tables really. but I could use the comment table like this, comment.postedby=user.id.
now the photo names are the users id along with the date. so it would be like so
1_2006_10_25_1340.jpg
so i thought I would let the user search for the user id and grab all photos with that id and load the comments as well. but I am following short. I get the photos, but no comments, or I get the comments but short on photos and the comments are the same comment for both photos. various mixed results
The trick is that on the same search form I will allow them to search just comments, easy, or the date of the comment, or with the user so they can see all of the comments by said user. but getting the difference between all searchers is killing me cause I want to do only one query if I could.
I don't think the table structure is important as you know th erelationship between the tables. but if you need it let me know.
if any of this made sense what idea would you have for the query?

View Replies !

Perform Multiple Queries
I have created a file consisting of many create statements and I want to run this file for optimisation. I want the tables to be created at one time instead of going through them one by one on the command line shell. How can i do this is mysql. I know i can load data into pre-defined tables but how can i create tables using a file

View Replies !

Issuing Multiple Queries
My sql server is version 5.0.45. I am issuing multiple statements in phpmyadmin and they run just fine. The statements end with a semicolon. If I issue the same statements using php, I receive an error pointing at the first semicolon and the beginning of the second statement.Code:

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 '; UPDATE myreport SET q2pr2 = 'M' WHERE id = '66260' AND userid ' at line 1

View Replies !

MySql DB Multiple Queries
How well can a MySQL Database handle multiple queries, for example if I had 100 Queries at teh same time from multiple users to teh database, would it crash or anything?

On a MySQL Server can host restrict how many queries a DB can receive?

Because I'm planing on having "sometimes" multiple queries oen after the others, mayeb even 500 Queries at the same time for different things and different users.

View Replies !

Multiple INSERT Queries
I have a for loop that generates and submits a query on every itteration. The number of itterations can be can be anywhere between just a couple dozen and a few thousand.
Is there a more efficient use of MySQL than this...
PHP Code:

for () {
INSERT INTO...
}
There must be some way of building the query in the for loop and sending the entire thing out as just one query submission.

View Replies !

Using Joins Or Multiple Queries
I have two very large tables, with relational id's. Would it be faster to
1. use a join on the two tables?
2. make two queries

If I were to make two queries would it be faster to use '...WHERE FIND_IN_SET(...' or to use '...WHERE IN(...'

View Replies !

Multiple Queries In One Shot
I'm actually using MySQL's C library/include files, so if this belongs in the C++ forum, my appologies, though it is somewhat of a newbie question.

Anyhow, I'm trying to send more than one query with one call to the mysql_query() function. However, it seems that every time I attempt this, the query fails. I tried then to use phpmyadmin to manually insert the query, and lo and behold, it works flawlessly. So my question is, are you allowed to use the mysql_query() function to send multiple queries with just ONE call to the function? If not, what COULD be the problem?

if(mysql_query(GameDatabase,
"INSERT INTO skills(name, skill, percent) VALUES ('Galnor', '133', '99');"
" INSERT INTO skills(name, skill, percent) VALUES ('Galnor', '138', '99');"
))

Also, yes, before sending this query I was sure to empty the table, so that is not the problem.

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 !

Avoiding Multiple Queries
I have a form that has 5 pull down menus that are identical that give all the results of a "categories" table (the user can choose up 5 categories to assign to the article). The form submits them as $_POST['cat1'], $_POST['cat2'] etc. where the values are the cat_id key value from the categories table.

the next page is a preview that should show the corresponding cat_name value for each key submitted. Code:

View Replies !

Multiple Count Queries -> One Report
I'm getting a bunch of data out of a database in the form of a lot of COUNT statements eg:

SELECT COUNT (*) AS "Value1" FROM tablename WHERE x=1;
SELECT COUNT (*) AS "Value2" FROM tablename WHERE y=5;
etc ... there are about 30 queries.

I'm using PHPmyAdmin to do this. Currently I have to put each statement in separately: If I put the whole load of them, PHPadmin only displays the last value.

I don't need a great deal of detail, but if someone can suggest a way I can accomplish this so I run one multi-line query and get all my values at once that would be great. Perhaps there is a better tool than phpMyadmin? Or maybe I should be looking at VIEWs or STORED PROCEDURES? If you can paint the broad outlines, I can fill in the gaps.

View Replies !

Merging Multiple SELECT Queries
I have two tables, one storing the info of the users ( one user in each record ) and the other the ids of several users in a single record i.e. (4,2,9,6), (4,2,8,3) etc.

For convenience sake, i'll call it table1 and table2.

What is need is to retrieve the records from table two and from the user ids, get all the users first name from table1.

My problem is that i want to keep it within one query. I have tried looking up union and joins but they don't seem to meet my needs.

What i would like to achieve is maybe (user1_first_name, user1, user2_first_name, user2 etc.)

Is this possible at all?

View Replies !

Multiple Queries In One Recordset/connection
I am doing an insert, for which I want to get the auto-increment value back as a recordet in the same connection, I have the following:

$query = "";
$query = $query . "INSERT INTO ";
$query = $query . "myTable ";
$query = $query . "(name,summary) VALUES ";
$query = $query . "('" . urlencode($_POST["frmName"]) . "','" . urlencode($_POST["frmSummary"]) . )";
//echo "Query: " . $query;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

I want to add to the query:

$query = $query . "SELECT DISTINCT @@Identity AS 'newId' FROM myTable";

When trying a semicolan seperator, I get an error (this is also true in my query analyser - it doesn't work there either.) Is this even possible, and if so, what am I doing wrong?

View Replies !

How To Send Multiple Queries At A Time?
I need to send multiple queries and try to be efficient. Is there a way to send multiple queries all at the same time? rather than doing multiple mysql_query?

View Replies !

Multiple Queries(checking A Value Against Many Tables)
I'm trying to develop a system where users sign up with user id's and PIN's.
1.I need to check if the PIN(pin) is valid(i.e exists in pins_table)
2.I need to check if the PIN has not been used(i.e does not exist in the members table)
3.I need to check if the chosen id has not been taken(i.e does not exist in the
members table).

Here is the portion of code I came up with to achieve this(after the initial ceremonies):

View Replies !

Complex Searching And Multiple Queries
I have a database of a list of companies and descriptions where users can login and see reports from companies they are subscribed to.

1) I want users to be able to search for a keyword and have it search the ENTIRE database for the keyword and return all rows.

2) For those returned rows, I want to cross-reference the permissions table and have it:
---A) return the company name and description if they are subscribed
---B) print that they are not subscribed to this company.

Here is a simple version of the database:

user
====================
id | username
====================
1 | bob
2 | john

reports
=======================================
id | description | company
=======================================
1 | Green | Crayola
2 | Green | Bic
3 | Blue | Papermate

permissions
===========================
user_id | report_id
===========================
1 | 1
1 | 2
2 | 1
I need to put these two statements together(shown below). I need to have it search the DB and return all rows, then check to see if the person has access to that company. If they do, show it to them, if not print that a company was found but they are not subscribed to it. So if John was logged in and searched for "Green" his results would return two results, one would show that he could see the report from "Crayola" and it would also show that the company "Bic" was found, however he is not subscribed.

Search results:
1) Crayola - click here to see the report
2) Bic - you are not subscribed to this company.

Here is a look at the MySQL I have been trying to use:

PHP

$query1 = "SELECT *
FROM `emt_report`
WHERE `company` LIKE '%$P_search%'
OR `description` LIKE '%$P_search%'
OR `market1` LIKE '%$P_search%'
OR `market2` LIKE '%$P_search%'
OR `market3` LIKE '%$P_search%'
OR `market4` LIKE '%$P_search%'
OR `market5` LIKE '%$P_search%'
OR `market6` LIKE '%$P_search%'
OR `location` LIKE '%$P_search%'
OR `date_year` LIKE '%$P_search%'
OR `date_month` LIKE '%$P_search%'
OR `source` LIKE '%$P_search%'"

PHP

$query2 = "SELECT u.id
     , u.username
     , p.user_id
     , p.report_id
     , r.id
     , r.company
     , r.description
     , r.market1
     , r.market2
     , r.market3
     , r.market4
     , r.market5
     , r.market6
     , r.location
     , r.date_year
     , r.date_month
     , r.source
     , r.video
     , r.audio
     , r.pp
     , r.execsum
     , r.report_url
     , r.exec_url
  FROM user as u
INNER
  JOIN user_reports as p
    ON p.user_id = u.username
INNER
  JOIN emt_report as r
    ON r.id = p.report_id
WHERE u.username = '$username'"

From above, how can i have search by query1 and then have it cross reference query2?

View Replies !

Multiple Stored Queries - From Access To Mysql
i have an access database queries, and i am trying to move them to mysql ...

i have a stored query called "Rooms Available", and a stored query "Rooms Reserved"

Rooms Available
---------------
SELECT Rooms.RoomNum
FROM Rooms LEFT JOIN [Rooms Reserved] ON Rooms.RoomNum=[Rooms Reserved].Room
WHERE ((([Rooms Reserved].Room) Is Null));

Rooms Reserved
--------------
PARAMETERS pDate1 DateTime, pDate2 DateTime;
SELECT Booking.Room, Booking.Arrival, Booking.Checkout
FROM Booking
WHERE (((Booking.Arrival) Between [pDate1] And [pDate2]-1)) Or ((([Checkout]-1) Between [pDate1] And [pDate2])) Or (((Booking.Arrival)<[pDate1]) And (([Checkout]-1)>[pDate2]-1));

as you can see the "Rooms Available" query join the second one to get results .

View Replies !

How To Run Multiple Queries "simultaneously"
I'm in a position where I have to clean up an old database to make it compatible with some new data. Part of it entails going through each and every table and updating an "account ID", which has changed with the new data set.

I'd written a simple code snippet to generate large chunks of SQL, like so:

UPDATE myTable SET account_id = X WHERE account_id = Y;

but then it dawned on me (and thank heavens it DID!) that this method won't necessarily work. e.g. if my query set had these two functions:

UPDATE myTable SET account_id = 56 WHERE account_id = 100;
UPDATE myTable SET account_id = 54 WHERE account_id = 56;

The second UPDATE will update the rows previously set by the first query - and that I *don't* want.

What I really need is to be able to execute an entire set of queries that act on the original database table content "simultaneously", so that the second query in my example will only update the rows that ORIGINALLY had an account ID of 54.

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 !

How Many DB Queries Is Too Many?
Is it ridiculuous to run 20 DB queries on a page that isnt accessed that often? What is the general rule when it comes to db queries? How many is too many?

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 !

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?