UPDATE Large Number Of Rows With CASE Is Too Slow
UPDATE table SET rating =
(CASE uniq
WHEN 'ACD' THEN 0.6857
WHEN 'GGYB' THEN 0.5831
WHEN 'QUCG' THEN 0.5831
WHEN 'CYP' THEN 0.5831
...
ELSE 0 ....
View Complete Forum Thread with Replies
Related Forum Messages:
UPDATE Large Number Of Rows With CASE Is Very Slow
UPDATE table SET rating = (CASE uniq WHEN 'ACD' THEN 0.6857 WHEN 'GGYB' THEN 0.5831 WHEN 'QUCG' THEN 0.5831 WHEN 'CYP' THEN 0.5831 ... ELSE 0 END), score = (SELECT @t := 1/(1+rating))*votes_up - (1-@t)*votes_down; The table has the column `uniq` as PRIMARY KEY of type VARCHAR(10), and about 500 rows. I need to update the entire table with this query. The problem is the query runs extremely slowly, taking 4.6529 seconds to run. I realize the index is not the most efficient, but is it the main culprit?
View Replies !
Slow Join On Large Tables
I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ", count(*) FROM D, DL WHERE D.DLID=DL.ID GROUP BY HLQ produces the following explain: tabletypepossible_keyskeykey_lenrefrowsExtra DALL500000Using where; Using temporary; Using filesort DLeq_refIDID4D.DLID1Using index The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00). key_buffer=512M table_cache=256 sort_buffer=16M read_buffer_size=16M It appeasrs that the 'Using filesort' on table D is due to the Group By clause and is the problem. I have an index on HLQ. Is there any way to get MySQL to use it?
View Replies !
Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else. I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger. This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen. Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go. I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.
View Replies !
Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do: There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns. The most common query is SELECT data FROM table WHERE n1 = ? The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column. Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset: SELECT COUNT(1) AS total FROM table WHERE n1 = 3; index = random number from 1 to total SELECT data FROM table WHERE n1 = 3 LIMIT index,1; How can I speed this up? I need it to take less than half a second if possible. Thank you.
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 Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user. Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details. Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?
View Replies !
Large Number JOINs And With Two FK To One Table
1) I have a first table with a PK (OwnerID) and a large number of FKs 2) I want to return the 'filename' column from the tables referenced by the FKs (and wonder if there is a better/faster/more concise way to do it than the JOIN syntax below? 3) In one instance there are 'bookcaseRight' and 'bookcaseLeft' fields in the first table which both reference the same 'bookcases.bookcaseID' -- and I cannot figure the way to do this (and/or/union all give improper results) Simplified code below and Thanks in advance: --------------------------------------------------- SELECT doors.filename AS door, bookcases.filename AS bookcaseL, bookcases.filename AS bookCaseR, FROM offices JOIN doors ON offices.doorID = doors.doorID JOIN bookcases ON offices.bookcaseR = bookcases.bookcaseID {?AND?OR?Union?} offices.bookcaseL = bookcases.bookcaseID WHERE offices.ownerID = 1 ;
View Replies !
Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems? as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?
View Replies !
Number Of User Accounts For Large Database
I have a large database (816 tables with 11 fields each, each field could hold up to 50 entires) and I'd like to know if each table should have its own user account (created in Cpanel and used in the config file to access the database). May sound like a simple question to some but I need to know in case we have a lot of traffic and 1 user account is not "enough" for all visitors to access the database info.
View Replies !
Update Too Slow
I need to update 25 * 5000 records, if I do one at the time it takes too long time, do any one have a good proposal ?
View Replies !
Slow Update
The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records. UPDATE items SET item_views = item_views + 1 WHERE id = 5897; is there any way I could speed this up? Some setting I could change to make MySQL faster for this? The field "id" is the primary table key.
View Replies !
Very Slow Update Statement
I am having an issue with an UPDATE statement that takes a very long time. I am using 1 table in a schema to update another table in another schema. Below are the create statements and the update statment I am using. Table and column names have been changed to protect the innocent :) Code:
View Replies !
Very Slow Table Update
I have two tables. One is really a subset of the other. However, they came in different data files and I would like to pull data from one and put it into the other. However, it is VERY slow! Once the tables are setup I will only read from them and perform operations. I will never update or insert. However, I can't get things setup to that point. Code:
View Replies !
UPDATE Function Very Slow??
I have a large table (77,321 Rows) and I'm trying to update it. For some reason, UPDATE takes a long time. Maybe it's my query? I'm doing it through php, maybe that's it? I'm running the program locally using the CLI, and it still takes ages. Should I not expect it to be as fast as SELECT functions? $update ="UPDATE `ch_products` SET `products_weight` = '".$IDS[$i][Weight]."' WHERE `xref1` = '".$IDS[$i][ID]."'"; How can I make this faster?
View Replies !
Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows. My table is getting kinda slow here is the query i am using UPDATE nametable SET sectionname = replace(sectionname,'".$oldsec."','".$sec."'), categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ' where sectionname='".$oldsec."' and categoryname='".$oldcat."' ; I am wondering if the same thing is possible with an insert... on duplicate key statement? I cant seem to get the insert statement to work, but not even sure whether it is appropriate. With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)
View Replies !
CASE Statement In Update Command
I'm trying to update one table (tbl_usage) based on information from another table (tbl_subscriptions) joined by the userID. The usage table tracks the number of times a user hits a page, and decrements the count each time. When the user has a zero count, they are redirected to a warning page. Each week the count is reset depending on what type of license they have (licenseType) and how many of those licenses they've bought (numLicenses). A group license (G) gives them 100 hits, and an individual license (I) gives them 5 hits. I'm trying to use a case statement to perform this count reset operation, but I can't seem to get it to work. The following case statement works just fine: Code:
View Replies !
Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out. The query I'm running is too slow...can anyone tell me what I can do to speed it up..if I can at all? I was wondering if because ZipListMatrix has 3.3 million rows that 8 seconds is all the faster it's going to be. Any help is greatly appreciated! I have already "optimized" the tables.
View Replies !
Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'. About half the rows are 'yes' and half are 'no'. Table is indexed on 'stamped'. We just need the count on 'yes'. SELECT count(*) from T where stamped='yes' or <>'no' or >'n'... is very slow. Is there a better approach / query to get a fast result?
View Replies !
Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57 # User@Host: counter[counter] @ localhost [] # Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647 SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7; mysql> select count(*) from _1_log; +----------+ | count(*) | +----------+ | 111824 | +----------+ 1 row in set (0.00 sec)
View Replies !
Update Performance On Large Table
I have a table that is approaching 4 gigs. I have optimized as much as I can with indexes so that select statements are ok, but updating entries seems to be taking a bit of time. I have 2 questions: 1) I know about the EXPLAIN command for select queries, but is there something like that for updates? I get an error when I try to do "EXPLAIN UPDATE mytable..." Do updates use indexes the same way as selects? 2) Would breaking the table up into seperate tables and then using a merge table increase the performace? I've heard that key reads are slower on merge tables - but since I could make 4 tables of a gig each, wouldn't it be faster opening up 1 smaller file to make an update instead of a huge one?
View Replies !
UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it. update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id = b.id and a.col2 = b.col2; The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back. I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states. How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved. Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)
View Replies !
Returning Several Random Rows From A Large Table
I have a table with userinfo like user name, age and so on... I want to be able to return 10 random rows from this table. I know that the MySQL RAND() is quite slow for large tables. So here's what I've come up with: How about generating a random number in PHP based on the tables MAXID and using SELECT * FROM the_table WHERE primary_key >= $randNr and then redo this 10 times to get example 10 results. this doesn't solve the problem with holes in the table though. but here's another one: SELECT primary_key FROM the_table use mysql_num_rows() in PHP to see how many rows are available. select 10 random numbers based on the above result. FOR loop through the 10 random numbers: SELECT * FROM the_table WHERE primary_key = $rand_number Are any of these a good idea or should I just smack my head and rethink my solutions. on a side note just how slow would it be to use SELECT * FROM the_table then creating random numbers based on mysql_num_rows() and then moving the data pointer to each individual point using mysql_data_seek() Just how slow will this be on large tables with say a million rows? last one would be horrible I imagine. I have one PRIMARY KEY column and I'm using INNODB tables. I'm afraid of using MYISAM because the table will be read intensive an hopefully contain > 0,5 mil users. Maybe I'm mistaken about this? Maybe there's a way to use COUNT() and MYISAM?
View Replies !
Selecting Random Rows In A VERY Large Table?
I would like to select random rows in a very large mysql table but I don't want to use something like that: "SELECT * FROM tablename ORDER BY RAND() LIMIT 1000"; Because it's horribly slow when you have a large table like mine (6 million rows+).
View Replies !
To Update A Large Amount Of Values In 2 Columsn In 1 Table
I have a table that I need to change all values for all rows in 2 columns (used for mapping products). Is there any SQL that will let me just say something like, update the 2 columns and insert these values in this order? Example: these are the values: 2, 4, 6, 3, 4, 1, 3, 4 And those values would be inserted as the values for the first 8 rows
View Replies !
Get Number Of Rows
I'm developing a hr system which will display Staff that are currently in the financial year(regardless of resigned or not resigned). Our financial year is from Oct - Sep (Oct 2006 - Sep 2007) For staff that have not resigned (@ the pt of query), the dateTerminated field is NULL. I have a database that stores resignation date and startdate (both columns are date type). I tried with the following query but was not successful. PLease help. Select * from (select name,dateTerminated,dept from user_info,hr_users where user_info.id=hr_users.sn or dateTerminated is null AND (year(dateTerminated)<=2007 AND year(dateTerminated)>=8) )as table2 dunno how to continue ??? order by table2.name ASC
View Replies !
Number Of Rows
Is there a quick query (without just asking to query the entire database) that will return the number of rows in a given table? I know I can do SELECT * FROM table but I don't really care about the data, I just want to know how many entries there are.
View Replies !
Get Number Of Rows Above A Row
I have one table with a some user info, and a column with the users points. I would like to fetch the user data and sonehow calculate in which place that user is. Basically, the number of users that has more points than the user. An exampel of the table and data: +----+---------+--------+ | id | name | points | +----+---------+--------+ | 1 | chris | 1000 | | 2 | john | 900 | | 3 | jeff | 800 | | 4 | jay | 700 | | 5 | michael | 600 | | 6 | eric | 500 | | 7 | stuart | 400 | +----+---------+--------+ And an example of the data set I would like: +----+---------+--------+----------+ | id | name | points | position | +----+---------+--------+----------+ | 1 | chris | 1000 | 1 | | 3 | jeff | 800 | 3 | | 6 | eric | 500 | 6 | +----+---------+--------+----------+ I'm not sure how to do this in one query, and I'm not even sure that it's possible, but if it is I sure would like to know how. I'm using MySQL 4.1.16.
View Replies !
Adding A Number In An UPDATE?
Is it possible to add a number in an 'UPDATE to a numer in a row? instead of "SELECT'ing the row, then adding 1 to the number then updating it. Like sql="UPDATE $tabename SET track='track + 1'
View Replies !
Counting Number Of Rows
Can anyone come up with a novel way of counting the number of rows in a table that contain no NULL values in the columns that each row contains? Each row contains 200+ columns, so doing a "count" on each column, and then selecting the smallest value is not very practical.
View Replies !
JDBC Number Rows
After you have done a .executeQuery() and returned that to a ResultSet how do you get the number of rows returned? I have tried using getFetchSize() but that does not help me.
View Replies !
Number Of Table Rows
I have tried to answer this question by using Google but my search didn't bear any fruits, so to speak. I am in the middle of designing my db and i have just thought that in some of my tables i think i will have 1000s of entries [rows] and if my web application with a db backend is successful it can turn into millions of rows [wishful] - so i wanted to know is there a limit in the number of rows for a table? I think not, but how will performance be effected?I am using a MySQL database, I am using a linux server not sure if this matters but microsft and i dont get along.
View Replies !
Retrieve Number Of Rows
I've got a large table with a lot of entries. only two fields, one's the key (which auto incs) and the other's a text field...if I want to just retrieve the number of rows in the table, what's the best way to do this?I know I could tell mysql to sort the table by the key row and then take the first one, but wouldn't this make it analyze the entire table (which would waste resources, especially considering that this table will have thousands and thousands of entries)? what's a better way to do it?
View Replies !
Maximum Number Of Rows
I am looking for information on the total number rows that can be used in an InnoDB,if there is even such a limit.I am not a DBA, so the information on mysql is a bit cryptic.
View Replies !
Restricting The Number Of Rows
I wold like to restrict the number of rows in my table. Currently I am using the mySQL ver 4.1.12a-nt I have alter the table using this statement, ALTER TABLE `database1`.`tabley` MAX_ROWS = 7; But whenever I insert the 8th row, it executed successfully. Pls explain why and how should I go about restricting the number of rows?
View Replies !
Limit Number Of Rows
I was wondering how can I limit the number of rows used in a table. For example if we wanna create a table named table1 having only 7 rows.
View Replies !
Adding Certain Number Of Rows
I have a table: Name | Amount --------------- Moe | 30 Larry | 9 Curly | 12 Larry | 10 Moe | 7 How do I find the total amounts for each person and then output the name of the person with the greatest total? (which in this case is Moe with 37)
View Replies !
Update Column With Random Number
I'd like to return records from a table in random order for a list on a webpage. I began by using `order by rand()`. However, the problem with this approach is that as you 'page' through a long list, the query re-randomizes for each page. To avoid this, I've added a column `random_sort` to the table. Once each day, I'd like to run a PHP script to update this column for every row in the table, to a random integer value. As this value will only be used to sort on, it doesn't matter if there is an occasional duplicate number. Values could be from 0 to, say, 1 million. Is there a way I could construct a MySQL query to do this, or would I need to use PHP and loop through every record?
View Replies !
Update Between Number Range Question?
I'm trying to update some numbers that are between a certain range. The query im using below keeps producing an error. Any advice? Quote: UPDATE ��' SET available = 's' WHERE number BETWEEN ?' AND ?'
View Replies !
|
TRACKER
