Limit Records To Improve Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows.
The programme basically read rows by sample of 1000 rows.
select * from <tbl_name> LIMIT i, i+1000
and print result in a text file.
This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows?
I wonder 2 things:
1- is this normal for mySQL to take this time?
2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance?
2- how can I improve this performance.
If you think of anything that can improve the performance on a select * from , please let me know.
View Complete Forum Thread with Replies
Related Forum Messages:
Best Way To Improve Performance On Order By?
I hope someone can help me with the following problem. (Note: I will simplify my table structure to the essential) I have two tables, one containing objects and one containing the objects a user has, so basically I have * table_userObjects with PRIMARY KEY idUserObject INDEX ON idObject (not unique) * table_objects with PRIMARY KEY idObject INDEX ON name What I want to optimize is the query which gets the objects for one user and sorts them by name. For example: SELECT * FROM table_userObjects,table_objects WHERE table_userObjects.idObject = table_objects.idObject AND table_userObjects.idUser = 3 ORDER BY table_objects.name LIMIT x,30 The db has around 40000 different objects and the top users have 200000 different items. In this cases it takes around 6 seconds to run the query. If I leave out the orderby no sorting has to be done and the query runs fast as expected. Is there anyway to create an index on table_userObjects, based on the name of the objects from table_objects? Or some other way to speed up this query?
View Replies !
Indexes To Improve Performance
I am looking to add indexes to my database to try and improve performance. Now I heard someone say once that you should add an index to any column that you are planning to filter using in the where part of your statements. So my question is this, in below is an example i have a table that I use to store reports that are generated by the system (i haven't chosen this especially but it has the core element that i user everywhere else). These core elements and columns that i regally in a where clause have '*' next to them. As such if i was following the advice of were to add indexes, i would probably add one to each of these columns. Then there are the other three that have '+', which occasionally i conduct a '%%' (wildcard) search on to help me find a cretin report. given this, plus the index on the primary key almost every table would have an index on it. To me this seems a bit much. So how many is too many?
View Replies !
How To Set An Index On Table To Improve The Performance
i've got a table with about 500 mio records. there are 3 col. decimal,decimal,int. my aim is to find data which fits best to the two decimal col. in which way should i set the index for the best performance? at the moment my query needs sth about 5min and it shouldn't need longer than 10 sec. is it possible to improve the performance only by using the correct index.
View Replies !
Performance Vs Many Records
I currently have 300 records in table "metrics". These metrics are assigned to users. So each user has access to the 300 metrics. by default, my SQL will load all 300 on a page for the user. I am now wanting the users to be able to say which they want to appear. So, out of the 300 they can tick the ones they want to see. What's the best way of doing this? I have thought about having a table called metrics_to_user which would store the user ID and a whole list of the metrics they choose. By default, every user would then need 300 records in this table? then they untick ones they dont want to see. Or another way is just to store just the ones they DONT want to see. So when I am loading the page, I do some SQL to get all 300, but somehow forget the ones they dont want.
View Replies !
Help With Performance Of Table With 200k Records
My client is experiencing severe performance issues going through a sales process. the order/cart table has around 200,000 records and is modified as you move through the process. Performance for multiple item orders takes a minutes to refresh. Their server version is 4.0 and the table has a primary index. (I am moving it to at least version 4.1) It is running PHP as the scripting language and I am going through logs to see what's up there as well but the performance issues have been ongoing and getting worse so it leads me to believe it's a db issue.
View Replies !
Performance Issue 2217728 Records :: Selects Taking Half An Hour
I am trying to execute following statement, which is not getting completed even after half an hour. SELECT FIND_IN_SET(t.seq, (SELECT GROUP_CONCAT(xx.seq) FROM indiv xx WHERE xx.unique_id = t.unique_id)) FROM indiv t indiv table has around 2217728 records. I have already index on unique_id. What should i do to speedup this statement?
View Replies !
Count And Limit Records
I am having trouble getting the right amount of distinct results. As a simplified example I want Code: select number,id,name,count(*) as total from tablename group by number,id order by number desc, total desc limit 10 I want to retrieve 10 unique rows, but for example when there is 1 count with value '2' then it returns only 9 rows. How can i make this query so that is returns 10 rows?
View Replies !
LIMIT (total Records)
If I have a query with LIMIT what will the best way to find out the total number of records returned by the same query but without any LIMIT? Will it be different solutions for different versions of MySQL? Is that possible to get this number in the same one query with LIMIT?
View Replies !
LIMIT :: Total Number Of Records?
If I have a sql statement like this: Code: SELECT id FROM `asset` WHERE field = 'value' LIMIT 20 , 40 Is it possible to know how many results there would've been if I hadn't used the LIMIT? Or will I need to do the above query and just count the results first, then apply the LIMIT and run the query again to get my limited set of results?
View Replies !
Limit :: Limited Number Of Records
I'm running a query to get a list of students and the class they belong to. Is there any way I can run a single query to return only 4 students from every class if there are more than 4 in some of them? The students have student ids, so I would want the first 4 student ids.
View Replies !
Limit Number Of Records On Left Join
I've heard this may not be possible, but never hurts to ask. I've got three tables, Case, Violator, Alias Case is a court police case Violator is the person Alias is different names (aliases) that person has. Here's my Query:
View Replies !
Limit ORDER BY RAND() To A Range Of Records?
trying to get a range of records randomly ordered. For example if I want the records from 0 to 10 from MyRecords I would get 1,3,4,2,5,6,8,10,9,7 and another time I would get 7,10,6,1,4,3,8,2,9,5. I have tried "SELECT * FROM MyRecords ORDER BY RAND() LIMIT 0,10" but I keep getting records from the whole record set, not from records 0 to 10.
View Replies !
Limit On Select Produces Records Out Of Order
I have a Java applet that displays descriptions (from an sql table) in a text area. The latest description is shown on load-up with max(time) ok, and there is a button to step through them in reverse order from latest to earliest. I have a problem with the following query: SELECT idescription FROM descriptions where unit = 'A' and iname = 'plate' order by 'time' limit "+nextrecord+",1 This query is executed on a java button press. The variable 'nextrecord' has already been set to 1 (to get the second latest, i.e. 'very useful item') and then 1 is added to it each time the button is pressed to get the next previous one. But the resulting order is squiffy, e.g. when applyed to the following table Code:
View Replies !
Counting The Number Of Records That's Greater Than The Limit
I have a table that has thousands of schools in it. I'm returning all the schools in a certain state. I'm limiting it to 20 per page. However, I want to display text that says how many there actually are...exampe: 'showing 1-20 of 43'. Is this possible? My query is below: SELECT id, city, CONCAT(school_fname, school_lname) AS thename FROM ccn_schools WHERE state='KS' ORDER BY CONCAT(school_fname, school_lname) LIMIT 20 So how do I have to run this query without a limit to get that number?
View Replies !
Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition date, account and invoice number are indexed Database size 18 million rows I am querying (selecting) columns of a date and an account our tester program that opens a socket to the Mysql database and does a select for the above n times each time the date and the account is randomized to minimize hits on records closeby. This program will perform over 1000 queries per second. At the end, the Mysql socket will be closed Even when the socket is closed each time, I still get 400 queries per second. When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results. Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.
View Replies !
Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers. SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;
View Replies !
Improve Query
I have this query that I use for stepping through records in a table, it selects for me the lowest, previous, next and highest record id refs for the table in question given the current record number. However it returns its query on two rows with repeated (and obsolete) data due to the case statement, I pick out the data I require using mysql_result command in PHP, but it would be much nicer if there was a way to only return a one row result with only the data I require. user_id is the autoinc field in table_t $id is the current record number being viewed PHP SELECTmin(user_id) AS First,max(user_id) AS Last, CASE when sign(user_id - '$id') > 0 then min(user_id) else max(user_id) end AS PrevNextFROM table_tWHERE user_id <> '$id'GROUP BY sign(user_id - '$id')ORDER BY sign(user_id - '$id') for completeness my PHP code for extracting the values i need is PHP $first = mysql_result($result,0,0);$prev = mysql_result($result,0,2);$next = mysql_result($result,1,2);$last = mysql_result($result,1,1);
View Replies !
Improve SELECT Command
I perform a SELECT on my database, but it takes over a minute for every run. I have to run it over 10000 times (with different values in the WHERE), so it takes way too long. A was therefore wondering if I could improve the query speed. Below you find the query. It is based on the ratio between a pixel (pix) vs. the average of its neighbourhood (from geo) in the same table (vgt) based on additional requirements (mgba,mgsc,eco). Code:
View Replies !
Improve The Execution Time
i am new to db2 want to ask questions about the performance of my sql commands for a view based another 3 views the sql commnads are as following: create view b_central_subgroup as select communicator as central_member, project_id as project_id from b_normalized_communicator intersect select initiator as central_member, project_id as project_id from b_normalized_initiator intersect select monospeaker as central_member, project_id as project_id from b_normalized_monospeaker it takes 4.4 seconds to execute this sql. it likes that the time is the summ of the other three views. Can the execution time be reduced through some other methods? If it is possible , then how ?
View Replies !
How To Improve On A Nested Select
I'm working on a simple data import tool, and I need to insert email addresses from table two into table one, if they don't already exist in table one. I figured this was a pretty easy nested select statement, but what I'm doing is getting my site taken off line for exceeding the CPU limit. Here's the SQL I'm using to get the new email addresses: SELECT distinct value, id FROM table_two WHERE name = 'email_address' AND value NOT IN (SELECT DISTINCT subscriber_email FROM table_one) There are about 4600 rows in table one, and 145,000 rows in table two. Does this seem like it would be a burdensome query? I'm not a SQL expert my any means, so is there a better way to go about this? It seemed like a simple one to me. Maybe my web host is just stingy with the CPU time.
View Replies !
How Do You Improve The Order By In Queries?
I read somewhere about mysql having to scan the table twice or something with "order by something" this was on mysql.com (as far as I remember) it said something about having it only have to scan it once but it didn't explain how to do it I really want to know, because a 500k row table of mine with a few indexes that cut it down to about 90k per topic is lagging more than I want (okay, so 1.2 seconds average page load on a 8mb connection isn't too bad, but I want to fix this order by stuff and maybe make it 0.9 or 1.0 :P)
View Replies !
Index To Improve Queries With AVG()
Is there any way to create some sort of index that will improve the performance of queries which use the AVG() function. An EXPLAIN statement on the query indicates that no index is being used. There are indexes on the ratings table for ID and ratings but they don't seem to be getting used EXPLAIN SELECT u.username AS username, ur.users_ID AS user_ID, IFNULL((AVG(r.rating))*(COUNT(ur.ratings_ID)),0) AS overall_score FROM ratings AS r, user_ratings AS ur, users AS u WHERE r.ID=ur.ratings_ID AND ur.active='yes' AND u.ID=ur.users_ID AND u.active='yes' GROUP BY ur.users_ID ORDER BY overall_score DESC LIMIT 0,40 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE r ALL PRIMARY 5 Using temporary; Using filesort 1 SIMPLE ur ref rating,users_ID_2,active rating 4 v2.r.ID 239 Using where 1 SIMPLE u eq_ref PRIMARY,active PRIMARY 4 v2.ur.users_ID 1 Using where
View Replies !
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
View Replies !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
View Replies !
Count Related Records, But Show Records With NO Related Records Also
cl_items ======== it_id (pk) it_ownerid it_name cl_offers ======== off_id (pk) off_itid (fk) -> to it_id off_whoid My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers) I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also. Is there any way to do this with mysql only?
View Replies !
IO Performance
I'm running a 5.0.51a mysql server on pentium M. My application is made of 30 myisam tables written every 2 seconds and a very few reads (1 evry hour) from the whole table. Each table is a kind of circular buffer (fixed number of line, current line). Each line of a table contains a medium blob whith fixed size (about 70k). I know the average output (Kb/sec) from the client to th server, says 200 Kb/sec. When trying to measure this flow with 2 invocations of "show global status like '%Bytes%'; followed by division by the elapsed time, I find between 1 and 2 times more (400 Kb/s). When I measure the output to the disk with the iostat command (iostat -d -k 10) I find 4 times more (800 Kb/s) and a lot of read Kb/s (100 kB/s). Is this normal or is there some tunning to do ? During this time, the iowait rate is very high (30%). Is this normal or is there some tunning to do ?
View Replies !
Best For Performance
It's a web application, accessed via internet, with around 100.000 clients and each client have around 10 users. This web app have a database of around 100 tables. Ok, the question is: Is it better to have each client with it's own catalog on the database (each catalog with around 100 tables) or is it better to use one catalog for all the clients (the tables would have a lot more records this way, but only one catalog)? I'm considering performance.
View Replies !
Which Is Better In Performance
country (id) country (1) America (2) Germany (3) Canada (4) China (5) Egypt (6) Russia (7) Korea (8) France (9) Brazil city (id) city (1) Berlin (2) New York (3) Cairo (4) Paris (5) Seoul (6) Peking (7) Chicago (8) Moscow (9) Denver I have two tables like the above. And I have one of these tables below. message1 (id) (city) message (1) (2) I love New York. (2) (5) Kumkang mountains are beautiful. (3) (4) I was born in France. (4) (6) Great Wall is great. (5) (7) Chicago is a big city. message2 (id) (city) (country) message (1) (2) (1) I love New York. (2) (5) (7) Kumkang mountains are beautiful. (3) (4) (8) I was born in France. (4) (6) (4) Great Wall is great. (5) (7) (1) Chicago is a big city. code1 select city, country from message1 left join city on city.id=message1.city left join country on country.id=city.country code2 select city, country from message2 left join city on city.id=message2.city left join ccountry on country.id=message2.country I think The codes above have a same result.(sorry, I didn't test it.)
View Replies !
Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?
View Replies !
Innodb Performance (CPU, RAM)?
I have a table with approx. 150,000,000 ++ rows. It should be approx 80 byte for each row. Now I'm planning to use it with mysql/innodb. Can you guys suggest anything about this. I have many questions... Can Innodb work well with such tables? Will queries (select/update) be slow? What hardware do you suggest, cpu/ram?
View Replies !
Balance Performance
My computer is AMD athlon 64 3400+ with 1.5G DDR. when i execute the following sql in mysql 5.XX. select c.filename, c.topic from articles c where c.topic in (select a.topic from articles a group by a.topic having count(a.topic)>1) order by topic; It eat up all the cpu power , and the mysql server stop responsing until end of this query. Even my apache stop responing too. How to fix this? How can i set the CPU limit of each query connection?
View Replies !
Performance MySQL With ADO VB
I'm developing an VB application which uses the SAX parser and ADO VB to insert.update data in in MySQL. The perormance is very bad. If I use the same data and insert/update to a MS Access database it takes 14 minutes. But using MySQL it takes about 5 hours! Here's the piece of code which takes the most time. Does anyone have an explanation for this? Code:
View Replies !
|
TRACKER
