Query Slows Down After Multiple Executions
I am running mysql 4.1 and am working with multiple selects and deletes on a very large innoDB table (50 million records)
basically, i am doing a Select statement on this table based on quite a few joins with other tables with a LIMIT 10 in a c# application then doing some programming logic with the Dataset I get and finally performing a delete on these 10 records. This Select is looped over and over again until all the records are deleted and nothing is found using the select. The wierd thing is that the first 100 or so times it loops it executes the SELECT statement in about 1 seconds. Then all of a sudden the query begins to take a matter of minutes per execution.
The problem does not go away even with a server restart. The only thng I have found that solves the problem is simply waiting a matter of a few hours then I will be able to execute the query in the 1 second timeframe for another 100 cycles or so.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this: create table myTable( token varchar(255) not null primary key, token_count int default 1, frequency int ) type=INNODB; A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic: (1) See if the String to be inserted is present in myTable.(Uses SELECT) (2) If not present the insert into myTable (uses INSERT) (3) If yes update the table after adding the current frequency (USES UPDATE) After processing each file issue COMMIT. This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key. My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance. A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency. After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).
Index Slows Down The SELECT
I have a table with about 15 different columns, of various types. It has 25,000 rows. I have a web-interface that does SELECT on that table, and does have options of sorting by some of the columns. When I introduce new indexes on some columns, it speeds things up, but on some columns is actually slows the SELECT... ORDER BY.... statement. Is that normal? I thought indexes take space, but never slow anything down (except for INSERT, DELETE, UPDATE).
Connect/ Join Query Multiple Dbs Multiple Servers
Struggling to connect simultaneously to remotehost@my_ip_address AND localhost@my_field_db FROM my_field_tablet_pc to affect a custom sycnchronization that SQLyog will not allow. Both remotehost and localhost are built on WAMP stacks.
Multiple Value Query
I was wondering how to pull multiple values from a database? So that... SELECT * FROM user WHERE user_id = 2, but also numbers, 3, 4, 5, 6, etc... Do I need to redefine user? SELECT * FROM user AS user1, user AS user2 WHERE user1.user_id = 1 AND user2.user_id = 2?
Multiple Commands In One Query
is it possible to do multiple commands in one query (from PHP)? that is, can the "idea" below be done all in one valid query: select * from table AND insert into table (stuff) values 'stuffings'
SQL Query Across Multiple Tables
I wish to make a 'Latest Comments' section, but I am unsure of the most efficient query to use. I have comments spread across 6 different tables, named as below; cms_artistcomments cms_featurecomments cms_freshtalentcomments cms_newscomments cms_reviewcomments cms_wallpapercomments Each set up as follows; comment_id artist_id (changes for each table) comment_author comment_post comment_date What I would like is to extract 'so many' number of comments and in reverse date order, to give the latest 'so many' comments.
Multiple Actions With One Query
Is it possible to SELECT data out of a MySQL database and also UPDATE a value all within the same query? I have a php reminder script that should be sent out WHERE a specific condition is met. I'll need their email address before I can send the email which I would SELECT. Then, I need to make a note in the MySQL database that I did send the reminder out by doing an UPDATE. It would be nice if I could achieve these tasks with one query. If not, no big deal. I'm just looking for a better way and maybe I'll reduce my MySQL ignorance today.
Query On Multiple Tables
I'm new to mySQL and databases and was hoping someone would be kind enough to help me out. I have two tables, events and images. I want to get the data from column image_path based upon two variables, $event_date and $event_id. The date is only in the events table but event_id is a foriegn key in the images table. I'm really struggling with the syntax and have got really good at generating errors!
Query Multiple Tables
I have a database called 'xxx', i need to query 3 tables inside this database called a, b, and c. Each table has a common field called artist: i need to select artist from all 3 tables (a, b and c) and group the results by artist. It has a catch of i needs them all to comply with a LIKE statement like below. So far i have: SELECT a.artist, b.artist, c.artist FROM a, b, c WHERE a.artist, b.artist, c.artist LIKE '$_GET[url]%' GROUP BY a.artist, b.artist, c.artist ORDER BY a.artist, b.artist, c.artist ASC I know this doesnt work but im guessing its along those lines on how it does work?
Query Across Multiple Tables?
I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called 'Date' in the other tables, how do I go about pulling back all the info for that entry across all the tables? I have tried doing this: WHERE main.Date = table2.Date AND table3.Date AND table4.date however it returns loads of data when it should only pull back the one entry.
One Query For Multiple Tables
I am using MySQL 4.0 I have 4 tables w/ the same columns. Table1, Table2, Table3, Table4, under database=survey. I want to count how many surveys have been completed under each Table. Using one query, I want the total of each Count(survey_id) from each table. Basically, Select Count(Survey_id) from Table1 + Select Count(Survey_id) from Table2 + Select Count(Survey_id) from Table3 + Select Count(Survey_id) from Table4. HOw can i do this in one query?
Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used). What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?
PHP Loops Vs. Multiple ORs In A Query.
I have a script that returns an array of product ids from search terms entered by a user on a web site. Is it better to execute an individual query in a PHP loop for each product (e.g. select * from dbname where id = 'n'), over and over again, or would it be better to build a large query string containing all the id's (e.g. select * from dbname where id = 'n1' or id = 'n2' or id = 'n3'...). I'm mostly concerned about server load here. The database is about 350 products currently, so the potential for a pretty long query string is high.
Query Multiple Tables
I have multiple tables I'd like to retrieve data from using the same query statment. All tables are setup as follows: ID Reference Value Timestamp I would like to select all the data from "table1" and "table2" where there timestamp is between Jan 01 2005 9:00:00 am till Jan 02 2005 16:00:00 Is this possible?
Query From Multiple Tables
I want to construct one single query that gets values from three different tables Table "A" has a column "id" Table "B" has columns "topicid", "topic" and "userid" Table "C" has coluns "userid" and "address" A.id links to B.topic_id. B.userid links to C.userid I want to get A.id, B.subject, C.address from all three tables in one query. Due to some other constraint, I must use left join with the first two tables: select A.id, B.topic from A left join B on (A.id = B.topicid); How can modify the above query to get C.address in the same query?
Multiple Keyword Query
Ive currently got a table with (shopId, Keyword) used for searching on a product. So a shop could have multiple keywords and a keyword could be associated with multiple products so if a user wants to do a such as "stationary AND office NOT warehouse" how would i get from the database all the shopIds that have the keywords stationary and office, but do not contain the keyword warehouse This would seem like a common problem, what is the best way to perform this type of query?
Multiple Statements In One Query
Anyone have an example of how to configure a Tomcat JNDI datasource so that you can execute multiple statements in one query string? I'm hoping there is a <parameter> of some kind that I can set for the resource in my context.xml file where I define the datasource. Also, how to you configure the Query Browser to support multiple statements in one query string?
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.
Multiple Update In 1 Query
I am trying to do the following using PHP and Mysql: $sql=" UPDATE records set appear_order='2' WHERE id='19'; UPDATE records set appear_order='3' WHERE id='17'; UPDATE records set appear_order='4' WHERE id='18'; UPDATE records set appear_order='5' WHERE id='20'; UPDATE records set appear_order='6' WHERE id='16'; "; I am getting the following error message: " Cannot update records: 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 records set appear_order='2' WHERE id='19'; UPDATE recor" I don't know why this isn't working. when I paste this query to phpMyAdmin (I hope you guys know phpMyAdmin) - it works. somehow using a PHP code it does'nt - I get that error message. did anyone every come across this error and knows how to overcome it?
Query On Multiple Tables
Tables: patient ------- patientID | name scans ------ scanID | scantype | scandate | patientID What I want to do is create a table that has a list of patient names and their latest scandate and scantype I can't quite figure out the syntax
Query WHERE Multiple Columns
I was wondering which is faster: creating a table with 1 column and 2 million rows OR 5 columns and about 400,000 rows the only difference is I would have to have WHERE col=1 OR col2=1 OR col3=1 OR col4=1
Multiple Indexes In One Query
I do a query like this on a couple million rows and it's too slow. SELECT `title`,`link`,`description` FROM `files` WHERE `spam` =0 AND `porn` = 0 AND `lastvisit` > 0 AND MATCH (`host`,`path`,`title`,`description`) AGAINST ('some query') I know it's using my FULLTEXT index on host, path, etc. I have another INDEX index on spam,porn, and lastvisit as well. Is it using this index already? If not is there a way to get it to use both indexes together?
Query With Multiple Criteria
Please tell me why this query will not work. It makes sense to me. INSERT INTO DAILY ( FULLNAME, FNAME, LNAME, GENDER, TITLE, COMPANY, ADDR1, CITY, ST, ZIP, G1, G2, [UPPER], UPPERFULL, FLEN, LLEN, F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, L16, L17, L18, FNAMEPOSS, ACCEPTED, [SESSION] ) SELECT RON.FULLNAME, RON.FNAME, RON.LNAME, RON.GENDER, RON.TITLE, RON.COMPANY, RON.ADDR1, RON.CITY, RON.ST, RON.ZIP, RON.G1, RON.G2, RON.UPPER, RON.UPPERFULL, RON.FLEN, RON.LLEN, RON.F1, RON.F2, RON.F3, RON.F4, RON.F5, RON.F6, RON.F7, RON.F8, RON.F9, RON.F10, RON.F11, RON.F12, RON.F13, RON.F14, RON.F15, RON.L16, RON.L17, RON.L18, RON.FNAMEPOSS, RON.ACCEPTED, INVTABLE.SESSION FROM RON, INVTABLE WHERE (((RON.FULLNAME)=[INVTABLE].[NAME]) AND ((RON.ACCEPTED) Is Null)); If I remove RON.ACCEPTED Is Null it works. WHY?
Multiple Table Query
I'm building an entertainment/food/festival website for my community using PHP and MySQL and have hit my first "speedbump" in the process. I have a simple text form that I need to query 3 seperate tables in my database (venue_profiles, artist_profiles and events) so if someone searches for a term, it will return a numbered list of everything regardless if it is a venue/artist profile or event. Everything was working fine when I only had to query 1 table but now I've been thrown a curveball and need it to search 3. Here are the tables:fields in question. 1) venue_profiles: id, name, city, category, description 2) artist_profiles: id, name, description 3) events: id, title, city, description What will the query look like for what I need? and how do I go about creating one simple list that includes all the results from all 3 tables?
Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers. OK, here is the table table test ------------------------ | test_id | test_order | ------------------------ | 1 | 1 | ------------------------ | 2 | 2 | ------------------------ | 3 | 3 | ------------------------ I'm trying to change the orders in one query, but not sure how to do that. phpMyAdmin shows me the code like this Quote: $sql = 'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;' 'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;' 'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;' . ' ' I'v tried that but got a syntax error. MySQL version is 4.0.26, can anyone help please?
Joining Multiple Tables In One Query
I was wondering if anyone could see the best way to lay this functionality out. I have the following tables and fields (comma seperated part). Assume the vendors userid = 5. I want to grab a list of all users that are within that vendors territory. [table] users userid, zipcode * multiple users per single zip code [table] vendors userid, vendorid [table] assoc_zipter zipcode, territory * multiple zip code per single territory [table] assoc_venter vendorid, territory * multiple territories per single vendorid Here is the SQL written out. SELECT * FROM users WHERE * vendors.vendorid=5 * vendors.vendorid = assoc_venter.vendorid * assoc_venter.territory = assoc_zipter.territory * assoc_zipter.zipcode = users.zipcode I am trying to get this all in one sql statement. Here is a sql statement that does not work: SELECT * FROM users JOIN vendors ON (vendors.userid=5) JOIN assoc_venter ON (vendors.vendorid=assoc_venter.vendor) JOIN assoc_zipter ON (assoc_venter.territory=assoc_zipter.territory AND users.zipcode=assoc_zipter.zipcode) GROUP BY users.userid
How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work) $sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";
Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images.... SQL SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"
Fulltext Query On Multiple Tables?
I've built a database for an organisation, in it are 3 tables i need to include in a search, news, events, and locations. Each have a title and a description. What I've been trying to achieve (and failed at, hence me running out of time) is to have the user enter one search query, and the results page, paginated, returning matching results from all 3 tables, which will then be listed in the search as "News: ..." etc, and the plan was to order them by the fulltext score. I considered running three consecutive fulltext queries and combining them in PHP, but then I won't be able to limit the result sets for paginating them.
Query With Multiple Data Filters
I need help with syntax but I dunno if what I want is possible. I need to retrieve the # of items sold and then broken down into; year to date, period to date , and total items sold. Can this be done with 1 query? .....
Multiple Table Query Problem
I have two tables users and cart. SELECT users.id, cart.id FROM user, cart WHERE users.id = cart.id when i run above query it pull the result correct. but SELECT users.id, cart.id FROM user, cart WHERE users.id != cart.id when i change the query not equal its pull multiple results.
Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".
Problem With Multiple Table Query Using Group By
I am running MySQL version 5.0.27-standard-log SELECT COUNT( rant_rants.* ) FROM rant_rants, rant_users GROUP BY rant_rants.userid LIMIT 0 , 15 Why won't this work? Foreach each rant_rants.userid I want the total number of rows in rant_rants with that userid. I have the second table in there because I am going to add more to this query once I figure this out. Any ideas?
Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here table categories +--------------------------------------- + | cat_id | cat_name | cat_total_articles | +----------------------------------------+ | 1 | PHP | 23 | +----------------------------------------+ | 2 | MySQL | 17 | +----------------------------------------+ table articles +---------------------------- + | article_id | article_cat_id | +-----------------------------+ | 1 | 1 | +-----------------------------+ | 2 | 2 | +-----------------------------+ Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table. Is it possible to combine the following queries into one statement? PHP mysql_query("UPDATE categories SET cat_total_articles = cat_total_articles + 1 WHERE cat_id = 2"); PHP mysql_query("UPDATE categories SET cat_total_articles = cat_total_articles - 1 WHERE cat_id = 1");
Can You Have Multiple LIKE Statements In ColdFusion / MySQL Query?
Hi, I'm running ColdFusion with a MySQL database. I'm trying to run a multiple keyword search on designated columns in a single table. So, you can enter the requested terms on one page, and then the next page runs the query and spits out any matches. I want to be able to search for LIKE matches, instead of exact matches. Anytime I run the query below, it works ok if you only enter a term in a single text box, but throws an error if more than 1 box is filled in....
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";
Seemingly Simple FULLTEXT Query Over Multiple Tables
Can't seem to get my head around this query. Here's what I have: I have 2 tables: TABLE a, TABLE b: Table a Columns: UID, FName, LName, Bio Table b Columns: UID, Specialty I want to search these FULLTEXT Indexed table columns (bio, Specialty) from the different tables for a string, and output the resulting UID and Username, one row only per UID (UID matches between tables)... To put it another way, if the search string is found in either of the 2 columns in the 2 tables, I would like to output the UID and FName from TABLE A. SELECT DISTINCT E.UID, E.FName, E.LName FROM contact AS E, brg_uid_specialties AS C WHERE E.UID=C.UID AND MATCH (E.Bio) AGAINST ('bubble noodle Oranges') OR MATCH (Specialties) AGAINST ('bubble noodle Oranges')" The resulting output from my sample tables is an enormous dataset (when it should only get 1 hit), and I can't figure out why... .
Running Multiple Update Querries In Query Browser
I am trying to run multiple update statements together from the MySQL Query Browser. For example: update table1 set field1 = "123", field2 = '456' where rowid = 'abc' update table1 set field1 = "321", field2 = '654' where rowid = 'efg' I can run them one at a time, but I cannot run multiple updates at the same time. I have 1100 of them to run, so I don't want run them individually. In M$ SQL I can just put a ; or GO at the end of each line to seperate it from the next. How can I do this in MySQL?
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?
Select Statement With Multiple Counts/multiple Joins Trouble
SQL SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count FROM duo_groups as g LEFT JOIN duo_group_members as m on m.group_id=g.group_id LEFT JOIN duo_users as u on u.user_id=m.user_id LEFT JOIN duo_videos as v ON g.group_id=v.group_id LEFT JOIN duo_video_comments as c on v.video_id=c.video_id GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username Will return My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
Multiple Languages, Multiple Products
I posted before and was helped immensely. I am designing a multilingual-capable site where each product may (or may not!) have a description in the user's chosen language. In this case, I want to get the default language description. Say my product descriptions table has fields prod_id, language, and description. When I have a single product and I want its description, I can retrieve it as follows:
|
TRACKER
