Improving LIMIT X,Y Query
I have only 1m records in my database running on a laptop of speed
1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk.
I use 'LIMIT x,10' for the query to utilise record paging.
When the value of x is nearer to 0, the query speed is fast.
Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes
about 0sec, 11secs and 4mins respectively.
1) Is there anything I should do, in terms of sql statement or database
design, to obtain the same speed for cases where x is in the middle or
nearer to the end?
2) How fast is a simple select statement (not select count(*)) on a
system like mine?
3) Suppose I left my original select .. limit query as is, what is the
minimum hardware that I need to improve the speed?
View Complete Forum Thread with Replies
Related Forum Messages:
Help Required Improving Query
the following query has been giving me problems. My users are reporting that credit is going down automatically and download counts seem to be overwritten with other downloads. I'm thinking using JOINS would be better, but I'm not sure which how best to structure it. MySQL UPDATE tb_members s, tb_userfield f,tb_downloads dl SET s.credit_available = credit_available - 1, s.credit_used = s.credit_used + 1, f.field24 = f.field24 - 1, dl.info_downloads_web = dl.info_downloads_web + 1, dl.info_downloads_today = dl.info_downloads_today + 1, dl.info_downloads_30days = dl.info_downloads_30days + 1, dl.info_last_download_time = NOW() WHERE s.userid = '".$ses_userid."' AND f.userid = '".$ses_userid."' AND dl.key='".$download_key."'"
View Replies !
Need Some Help In Improving Performance On A Query (was: Hi People)
I need some help in improving performance on a query which is taking too long to execute. I have 3 tables: table 'photo' stores information on photos and it has 2 important fields: - photo_id - title table 'item' stores information on generic items in the system (photos is an example). It has 3 important fields: - item_id - item_type (like 'photos') - submitter_id table 'user' stores information on users in the system. It has 2 important fields: - user_id - name I would like to get the number of photos that either have a title that contains 'a' or the submitter's name contains 'a' I tried the following: SELECT count(DISTINCT photo.photo_id) NumItems FROM (photo, item, user) WHERE (item.item_id = photo.photo_id AND item.item_type = 'photos' AND (photo.title LIKE '%a%' OR (user.name LIKE '%a%' AND user.user_id = item.submitter_id))); This query is taking way too long (sometimes up to 10 seconds Note that I have over 2,000,000 records in the 'user' table (I suspect the problem is there) I only have 2 recods in the 'photo' table I only have 2 records in the 'item' table
View Replies !
Any Tips For Improving This Query Time?
Is there anything I can do to improve execution time for this query? mysql> SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +---------------------------------------------------------+---------------------------------------------------------------+-------+ | webpageUrl | webpageName | count | +---------------------------------------------------------+---------------------------------------------------------------+-------+ | http://www.w3counter.com/ | W3Counter - Free Web Counter, Web Stats, Live Analytics | 2490 | | http://www.w3counter.com/stats/visitors/4071/day/1000/0 | Visitors Overview - www.pitbullmortgageschool.com/ | 1496 | | http://www.w3counter.com/stats/4071/v_daily | W3Counter - Visitors by Date - www.pitbullmortgageschool.com/ | 1130 | | http://www.w3counter.com/stats/ | Your Websites | 1124 | | http://www.w3counter.com/stats/websites | W3Counter - Websites | 800 | +---------------------------------------------------------+---------------------------------------------------------------+-------+ 5 rows in set (0.88 sec) mysql> explain SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | 1 | SIMPLE | _1_log | ALL | NULL | NULL | NULL | NULL | 29422 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ 1 row in set (0.00 sec) It averages 0.5-3 seconds throughout the day (it's run often enough that it shows up in my slow query log multiple times an hour for exceeding 1 second). Adding a partial index on webpageUrl doesn't affect anything, and URLs can be quite long. Is there anything I can do to speed up that query? The table is updated too often to maintain separate url/count tables -- can't spare the resources.
View Replies !
Improving The Performance
Serve Spec: AMD 2000 RAM 1.5 GB Fedora 10 How can I modify my.cnf for a better performance. Current file looks like [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186"
View Replies !
Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server. We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance? This is what i've done so far. 1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed. 2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker. Things i'm considering doing 1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes. 2. Moving the database off the webserver and onto its own dedicated mysql server. 3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution. 4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost? 5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance. 6. Buying a book on mysql optimisation Any suggestions?
View Replies !
Limit SUM() Query
I am trying design a report that will query the "order" table and give me a sum of the "grandtotal" column for the sales in a particular range of dates. I have a query that selects the data with the range of dates: my($query) = "SELECT * FROM $sc_mysql_ord_table WHERE time > $sdate AND time < $edate ORDER BY $in{'sort'}";
View Replies !
Using LIKE And LIMIT In A Query
Is it possible to use LIKE and LIMIT in a query? I am trying to get search results and limit them. Ive tried different ways but it doesn't seem to work. I have looked up syntax for both LIKE and LIMIT but cannot find one that includes both. $query = 'SELECT * FROM table WHERE rows LIKE "%$q%" AND LIMIT 0, 10'; $query = 'SELECT * FROM table WHERE rows LIKE "%$q%", LIMIT 0, 10'; $query = 'SELECT * FROM table WHERE rows LIKE "%$q%" LIMIT 0, 10';
View Replies !
Double Limit Query
I have 100 records in a table and I've used paging and showing 5 records per page. But I want to get only 40 or 50 total records from 100 from the table and then want to use paging and display 5 products each page. How should I do that. Because In a query i only can use 1 LIMIT that I used for paging but how to ristrict the query to get only 40 or 50 records? Please Help SELECT * FROM temp LIMIT 0,5; but how to get 40 or 50 total records?
View Replies !
Select Query Using Limit
i have the basic sql query as follows: $sql = "select * from photo where CategoryId='$idc' order by Id limit $start, 9"; this will select all rows from db photo based on CategoryId, then stop after it gets the 9 beginning at $start right? start is the value of the ID i want to start the select from. in this example, i have valid db values ranging from id: 11-20. the 1st page that runs this query has start=11, and displays for 11-19. when i goto the next page setting start=20, i get nothing. Limit returns 9 rows starting at $start right? or am i misunderstanding how all this works?
View Replies !
LIMIT In Delete Query
I want write delet query who don`t delete 10 lines in table and delete all rest but I don`t know how, I`ll use ORDER and LIMIT but I don`t know how I can limit that with LIMIT! I know this: LIMIT 10,[some number] But I want delete all and I don`t know that number (rest after 10 lines.
View Replies !
Eliminate LIMIT From Sub Query
I have a single table that tracks all revisions to all issues. The format is like so: id - auto_increment issue_id - foreign key to an issue changed_at - timestamp assignee - foreign key to a user change_made - varchar of change that was made at that time. I need to select the latest change to every issue for one specific user. I would normally do this with: SELECT * FROM historical_data AS hd WHERE hd.id IN (SELECT id FROM historical_data WHERE hd.id = id ORDER BY changed_at DESC LIMIT 1) AND assignee = 6; Of course this gives the "You can use LIMIT & IN at the same time" error. Is there something else I can do to get the same result ... besides moving to Postgres?
View Replies !
Query With Limit 3 But Minimum 1
How do I run a query with limit 3 but a minimum of 1? For instance, I'd like to grab news items from the last 3 days. If, however, there are no items from the last 3 days, it grabs the single newest.
View Replies !
Sub Query With ORDER BY Id DESC LIMIT 1
I am trying to make a query with some sub queries, and i am having some syntax errors. I have a table with the active users: which contains: id(auto_increment) | user_name | now_active the now_active field is inserted with value = '1', when the user logs in. And is updated to '0' when the user logs out. Here is the code: - At login: it inserts "INSERT INTO users_active (user_name, now_active) VALUES ('$user_name', $user_ip, '1');" - At logout: it updates the last UPDATE users_active SET now_active = '0' WHERE id =(SELECT id IN (SELECT id WHERE user_name = '$user_name') ORDER BY id DESC LIMIT 1); I need mysql to do what is below in one single query: 0. $user_name = John_example 1. $user_ids = "SELECT id FROM users_active WHERE user_name = $username"; 2. $user_max_id = "SELECT id WHERE id = $user_ids ORDER BY id DESC LIMIT 1" 3. $update = "UPDATE users_active SET now_active = '0' WHERE id = $user_max_id;
View Replies !
Simple Query Question: LIMIT
I'm looking to list all the rows in a database, but skipping the first 5. I know that I can use "LIMIT 0,5" to get the first 5 results, but what query do I use to list everything from 6th result onwards?
View Replies !
Optimizing A SELECT .. WHERE .. LIMIT Query
(using MySQL 5.0) First, the problem: I need to select different "pages" of IDs that match a small list of categories. Here's my table: CREATE TABLE `testTable` ( `id` int(10) unsigned NOT NULL auto_increment, `category` tinyint(3) unsigned NOT NULL, `username` char(15) NOT NULL, PRIMARY KEY (`id`), KEY `cat` (`category`) ) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1 And an EXPLAIN of my query: EXPLAIN SELECT * FROM myTestable WHERE category IN(5,8,11) LIMIT 5000,20; id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | myTestable | range | cat | cat | 1 | null | 49904 | Using where So from that we can see it's hitting 50,000 rows to get the 20 rows that I want (it's a table with 1,000,000 rows total). I've been using MySQL for a fair amount of time, but I haven't ever done much optimization beyond the standard "add an index on it".
View Replies !
Reverse Order Of Rows In A LIMIT Query
I have the following query: SELECT * FROM stocks WHERE ticker = 'CBU' AND `date` < '2009-01-31' ORDER BY `date` DESC LIMIT 3 This returns the 3 most recent dates out of a total of 20 rows. I would like to use the 3 most recent dates BUT IN REVERSE order. if I use the ASC command, it will not work as it will return the 3 oldest dates in the 20 rows, not the 3 most recent in reverse order. How can I reverse the order of the 3 most recent dates returned with the LIMIT command in the query above?
View Replies !
Limit Text Output In SELECT Query
I'm looking to output a list of news stories, but only outputting the first 100 or so characters of the text and only limited to the newest 4 entries in descending order. At the moment I'm using this nugget: But it doesn't work. $result=mysql_query('SELECT newsheading, (LEFT newsbody,100), newsdate FROM news ORDER BY newsid DESC LIMIT 4');
View Replies !
Query On Large Table When Implementing Paging With LIMIT?
I'm working on a paging implementation but I am stuck on a couple of things: 1) I tested with a simple query which yielded this: EXPLAIN SELECT create_date FROM threads LIMIT 0,25 id=1 select_type= SIMPLE table=threads type=index possible_keys=NULL key=create_date key_len=4 ref=NULL rows=57852 extra=Using index I thought that the LIMIT clause would limit the number of rows scanned, why is it still showing 57k rows? 2) Assuming there is a simple fix to the above, now if I want to order by create_date (SELECT create_date FROM threads ORDER BY create_date LIMIT 0,25) I understand that mysql must pull the entire table to sort the data set before applying the limit. So the explain would probably look a lot like the above. In this instance, is there a more scalable solution to implement a sorted paged result set?
View Replies !
How To Construct Query For Results With Limit (Grouped By Values In Another Field)
Field 1 : GroupName Field 2 : IndexNumber The sample data could be {(HR, 1), (HR, 3), (HR, 5), (MANAGER, 10), (MANAGER, 12)} I wanted to pick the first two values for each GroupName sorted by IndexNumber ascending. So if I wanted 2 results for each GroupName, the result would be {(HR, 1), (HR, 3), (MANAGER, 10), (MANAGER, 12)} This will exclude the third set of data (HR, 5).
View Replies !
How To Get The Total Number Of Rows With A Query "limit" ?
I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page. But for the first page, I need to run the same query with a count(*) to know how many pages I will get (number total of rows/ X). The problem is my query is very slow (maybe 5s) because there is much worch to do, and on the first page, I need to run this query twice (not exactly, but ...) so the page is very very slow to load. My question is : is there a function to get the total number of rows even on a query with "limit" ? Or what could I do else ?
View Replies !
Using LIMIT 0,1
Let's say I do a check for a duplicate email address in a database by using SELECT COUNT(*) FROM emails WHERE email='' .$email. '' Will it be faster (as in end sooner) if LIMIT 0,1 is stuck at the end of that query, or does it not matter?
View Replies !
Sum() With LIMIT
I'm trying to write a query that will add up the values in a column but then stop when the value reaches a given limit. roughly like this: select sum(filesize) as totalsize from table1 where column2 = 'xyz'; (and do the sum(filesize) until totalsize = 10000) I simply can't figure this one out.
View Replies !
Using LIMIT
If I'm querying a table where I know it's going to be a unique answer should I put LIMIT 0,1 . My thinking being that if the record I'm looking for is the 3rd of 250,000 then using LIMIT will stop the query after it's found the record instead of going through the other 249,997 records. Is this right? $sql = "SELECT * FROM db_sector WHERE sector_x = '" . $x . "' AND sector_y = '" . $y . "' LIMIT 1"; $result = mysql_query($sql); $sector_id = mysql_result($result, "0", "db_sector.sector_id");
View Replies !
Limit Sql 4.1.22
error from query: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' SELECT * FROM news WHERE news.id NOT IN (SELECT news.id FROM news ORDER BY date DESC LIMIT 15) If limit is not supported how would i archive this?
View Replies !
I Need A Help About LIMIT
i have 10 rows in a table i want to select the last 3 rows only i tried: "select * from table1 order by id desc limit 0,3" it selected rows 10, 9 & 8 but i want it ordered like 8, 9 & 10, what should i do?? i also tried: "select * from table1 order by id asc limit 0,3" but it only selects the first 3 rows it worked when i used subselect: select * from (select * from table1 order by id desc limit 0,3) as test order by test.id asc but my host is using mysql 3.23, which does not accept subselecting.. i also tried: "select * from table1 order by id asc limit count(*)-2,3" but it only gave me an error msg i even tried: "select * from table1 order by id asc limit 3,-1" but same: error
View Replies !
Limit?
i saw the following in a mysql query: $stmt = "SELECT id, first_name, last_name FROM persons LIMIT 0, 3"; in that code, what does "LIMIT" mean, and what does it do?
View Replies !
What Does LIMIT Do?
in MySQL, when I do LIMIT x, how does the database work? 1) Does it pull everything relevant, then only takes the x starting from 0 or. 2) Does it only pull the first x amount out and stops?
View Replies !
Sum With LIMIT
I have an issue. I have a mysql select like this: SELECT sum(points) as p_max, sum(achieved) as p_ach FROM SSL_A6supplier_ratings where customer_id='$customer_id' and supplier_id='$supplier_id' and theme_id=1 order by ID desc limit 8 its and archive table, so it must only sum the last 8, but for some reason the limit doesnt work and its adding everything up. I did some googling and it seems that the limit doesnt work for sum or count etc, and i have to use sub-selects, but i cnt seem to get it to work. I have tried this, but doesnt work: SELECT sum(select points FROM SSL_A6supplier_ratings where customer_id=1 and supplier_id=1 and theme_id=1 order by ID desc limit 8) as p_max, sum(select achieved FROM SSL_A6supplier_ratings where customer_id=1 and supplier_id=1 and theme_id=1 order by ID desc limit 8) as p_ach FROM SSL_A6supplier_ratings where customer_id=1 and supplier_id=1 and theme_id=1 order by ID desc limit 8
View Replies !
Set A Space Limit
I have any users who have hosting on my server and they want a MySQL User Account for their websites.How can I do to limit the space of their database following the plan of their hosting?If I can't set a limit they can put much more datas into database.
View Replies !
Sort, Limit
I am trying to do a query like: (SELECT * FROM table1 ORDER BY frequency DESC LIMIT 50) ORDER BY name; Where the top 50 frequency values are then sorted by name. When I try this, it displays each equal frequency value sub sorted by name (so all freq = 50 are shown a-z, then all freq=40 shpwn a-z etc), rather than showing the top 50 frequencies sorted by name which is what I want to achieve.
View Replies !
Select Limit
I have a list of numbers (i.e. 1 to 175) and I want to do a select statement to get the highest number. I have tried the limit thing but not getting the right output. Can someone shed some light on this?
View Replies !
How To Limit COUNT(*)
I guess there is no way to limit COUNT(*). Is that right? We cannot use the result of COUNT in WHERE condition or LIMIT doesn't help. In this case so far I have retrieved a field and used LIMIT. Is there a better way to control it?
View Replies !
Getting Other Data After Using LIMIT?
I have a table with a few hundreds records of products information. I need to retrieve the top 10 records based on the sum of the products quantity. My query is as below: Select *, sum(qty) as SUMQTY ,sum(amount) as SUMAMOUNT from tdetail group by barcode order by SUMQTY desc limit 10; This query gave me 10 records of products(barcode). My problem is that i need to have another 2 fields for the percentage calculation( eg.sum(qty) of product N/total qty , sum(amount)of product N/total amount.), but i dont know how to get the total qty and total amount. How do i do that in one query?
View Replies !
LIMIT Problems
When I execute the following query without the LIMIT clause, it executes under 1 second. When I add the LIMIT clause, it takes 10 seconds to complete. The client table contains 100,000 records. What is going on? SELECT * FROM clients WHERE rep_id = 1 AND last_name LIKE '%au%' ORDER BY first_name DESC LIMIT 0 , 10
View Replies !
DELETE With LIMIT
I want to creat a SQL statment that deletes all records but the first 7, i have tried DELETE FROM dbtable LIMIT 8,100 but it didn't work, what is the corret way?
View Replies !
Benefit Of LIMIT
despite have just 1 record any benefit of using LIMIT ? PHP Code: SELECT username FROM user WHERE id > 5 AND < 7 LIMIT 1; PHP Code: SELECT username FROM user WHERE id > 5 AND < 7 ;
View Replies !
Results W/ A LIMIT BY
I have a table where I need to pull out the 5 latest records only. There is an INT 10 field called category. I only want one record from each of the following items in category (1,3,4,5,7). I can't do a Distinct because the other fields are not distinct. If I do a LIMIT BY, I can't guarantee that I'll have one of each. How do I get out the data?Here is my not working query: SELECT username, title, category, price FROM mister WHERE category IN(1,3,4,5,7) ORDER BY laststamp DESC LIMIT 5
View Replies !
Explain Limit?
Could some explain the "LIMIT" function to me? For example, I have the following Code: $sql = "SELECT name, bio, headShotFile FROM artists WHERE id='$id' LIMIT 0,1"; I bought a web site from a friend and now I'm picking apart the code and learning. I've done some surfing, but couldn't find a clear explanation, only uses of it. Yet I want to make sure I fully understand it.
View Replies !
Limit Number
4671 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.60 mysqld 4672 mysql 8 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4673 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4674 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4675 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.07 mysqld 4676 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4677 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.01 mysqld 4678 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4679 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.00 mysqld 4680 mysql 9 0 11416 11m 1252 S 0.0 12.4 0:00.07 mysqld I really don't need all these, they're just eating all my ram. I think 2-3 would suffice for my low-traffic website. Where can I limit the number of processes spawned by mysql?
View Replies !
Limit Offset
I want to limit a query to the last ten rows. Is this possible without changing the order? I was hoping a negative offset would work, but it doesn't I specifically want the order descending so I don't have to reverse the order later using PHP. I'm actually query something by dates and I want the dates in chronological order and I only want the most recent results. I could use WHERE with some date calculations, but it would be easier to manage if I could count backwards.
View Replies !
|
TRACKER
