ORDER BY Takes Forever
I'm having problems with ORDER BY.
When I run a select targeting just one of my tables, it runs quickly and nicely.
When doing the following:
select * from rubbet, kommun where ortnamn like '%Johannes%' and rubbet.harad=kommun.harad and rubbet.socken=kommun.socken order by rubbet.ortnamn;
it screws up bad. The query takes about 2 minutes to run before showing the result: "74 rows fetched in 0.0032s (112.5129s)"
obviously the query takes no longer than 0.0032s but the ORDER BY clause makes it take forever to bring back the result.
without the order by the whole thing runs very smoothly.
View Complete Forum Thread with Replies
Related Forum Messages:
Subquery Takes Forever
I have two problems where I'm trying to retrieve data using a query. Part 1: I am doing a simple sub query which is on a two data sets with no more than 3000 rows in total. The query is taking 28 seconds to execute? Why I don't have a clue? Quote: 'Select Title FROM products WHERE Product_ID IN (SELECT Product_ID FROM order_items WHERE Order_ID="'.$id.'")' Part 2: When I have solved the above I'm looking to do a join, but don't know how to approach it ? Basically I'm trying to get a complete data set for a product/order like the following: SELECT * (which includes product_id) from items AND SELECT Title from products where product_id =(the product_id is retrieved from items); // using a join
View Replies !
MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week. Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.
View Replies !
Delete All Records In A Table Takes Forever -- Anyone Know Why?
I have a table called table_a that has 1 record in it. I delete that record, which because of foreign keys (the tables are type InnoDB) will cause the records in 8 other tables to be deleted. The 8 other tables have a maximum of 200,000 records in them. 2 of them have that many while the remaining have < 5,000 records. Currently, deleting that 1 record has taken hours upon hours with no end in site. Does anyone know why in the world this would take so long? Is there any way to speed it up (maybe a config setting I am missing or something)?
View Replies !
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
SELECT Takes 20 Seconds
I am having trouble speeding up a SELECT statement from a table with 1,764 records. I tried myisamchk --sort-index --sort-records=1 but this did not help (is a Primary key enough?). Things were fine at around 1200 records but went downhill after 1700. I am using MySQL 3.23.42 on HP/UX 11.11 and PHP 4.3.4. Any tips on how to speed things up?
View Replies !
Update Takes Too Long, I Need Help
here is my update SQL string: update prices, legend set prices.legend_id=legend.id where ( (prices.id between 1 and 10006) ) and prices.a_legend=legend.description running time: 63.97960 as you see prices has 10006 records and legend table arround 349 records please advice how to speed up this?
View Replies !
Create Index Takes More Than 5 Hours
I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM. I've created a table and imported 27 million rows of data. I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made. The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table. Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB). I'm wondering if this is normal time and space consumption for 27 million rows. Finally, doing an un-indexed query on this table : SELECT * from historical where reference = 'abcd'; Takes more than 1/2 hour. Is this also a normal amount of time? I understand that people are using MYSQL for data warehousing and major transactional applications.
View Replies !
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
JDBC Result Closing Takes Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
Backup With Mysqldump Takes Hours, Database Is Not That Big
Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb). My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all. My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk. The database is around 350mb, with around 1.1 million rows. To backup I use mysqldump -uroot -p database > backup.sql As I said, the above takes hours. I guess it should take minutes?
View Replies !
JDBC Result Closing Takes Ists Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
View Replies !
Lost Connection To MySQL Server During Query After Script Takes 60 Secs
I get the error "Lost connection to MySQL server during query" at the mysql_select_db when reaching this part of the code after 60 seconds of script execuation has already passed. Works fine if the script execution time when reaching this is under 60. Tested with Sleep() and I'm 100% sure that the 60 second barrier is causing it. I just don't know what the barrier is.////
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
Re-order The Natural Order Of All Of All The Rows Of A Table
Basically I want to re-order the rows of my entire database. It has many thousands of rows, and people are constantly running the same query against it. The problem is its quite slow (using a shared host). I know you can use an sql query to order by column, however I need to re-order twice and it really seems to be slow due to this. Since the query is always the same, if the rows were already ordered in the database , then the ordering query wouldn’t need to be done. Is there an easy way to do this, without deleting each and every row, then inserting them again? basically in the database I have: The natural order when you do a select * from this table (without using an order by query): ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |dataX |dataY |dataZ | |datal |datam |datan | (obviously a lot bigger than this) and I want reorder the entire table , eg The natural order when you do a select * from this table: ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |datal |datam |datan | |dataX |dataY |dataZ | So is there an easy way to do this, am I just being dumb? or do I need to delete the entire table and insert the rows one at a time in order?
View Replies !
Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie. *** SEE ATTACHED IMAGE PLEASE I have tried all sorts of SQL statements, e.g: SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC Nothing seems to work. I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ? I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.
View Replies !
How To ORDER BY The Order Requested In The Query?
Here's my query: SELECT * FROM myTable WHERE id=14 OR id=3 OR id=8 Simple stuff, I know. The result of the query is three rows that are all sorted by their 'id' in ascending order. I don't want this. What I want returned are rows sorted by the order in which I requested them. I need the query to return row #14, #3 and then #8 in that order.
View Replies !
Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of precedence in using it. Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for....
View Replies !
ORDER BY :: Sequence Order
Is it possible to ORDER BY based on the sequence order, for instance I use this Query: SELECT * FROM Showroom WHERE Artnumber IN (52900, 52536, 52730) the result is this: Artnumber Price 52536 25,80 52730 1,90 52900 31,10
View Replies !
Order By Unconventional Order
How can I have letter "z" comes before letter "a" when I select my items from a table. I need my list to show (z, a, b, c, d, ...). Also, how can I have numbers come after letters, say, if I want my list to be ( z, a, b, c, ... 1, 2, 3, ...)
View Replies !
ORDER BY Out Of Order - Fixed
I have a query that uses two tables that I want to produce output like the following: +-------------+-------------+-----------+ | LOCATION | GROSS SALES | NET SALES | +-------------+-------------+-----------+ | Location 1 | 11,860,735 | 2,907,552 | | Location 4 | 4,814,029 | 1,077,003 | | Location 3 | 2,711,795 | 710,804 | | Location 5 | 2,660,040 | 666,255 | | Location 2 | 2,049,470 | 563,830 | | Location 8 | 2,227,730 | 543,220 | | Location 7 | 1,766,880 | 425,483 | | Location 6 | 1,721,681 | 367,252 | | Location 10 | 13,424 | 2,253 | +-------------+-------------+-----------+ However, I cannot get the "order by" part of the statement to work. I have tried adding a zero to the order by alias. And when I attempt to specify the field in the order by with the calculation and not the alias I get an error. The following examples are queries without the zero and with a zero added to the alias in the order by line. I have also added the details for the table below for reference. Code:
View Replies !
Custom Order For ORDER BY
I'm grabbing six specific records, like so: $result = mysql_query("SELECT * FROM casestudies WHERE id=1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 ORDER BY ___________",$db); I'd like to post them in a custom order, specifically: 1,3,4,5,6,2. Can't figure out how to do this with ORDER BY, if that's even possible. Anything I'm missing?
View Replies !
Force Order By Order??
I have 6 rows of data... each has a name, eg: c1, c2, mrgs1, totalfte. they are all under the one column "field". Is there a way I can force their order?? at the mo, I use "ORDER BY field DESC" and its coming out lile mrgs1, totalfte, c1, c2 I need totalfte, mrgs1, c1, c2 is it possible to sort like this??
View Replies !
Order By Ascending Order
I have a mysql table with a column with a few zero values and an ordered list of numbers.. eg 0,0,0,1,2,3,4,5 is there a way to order the table in ascending order for the numbers 1-5 then display the ones with zero afterwards in one sql query.. so it would turn out like 1,2,3,4,5,0,0,0
View Replies !
Speed Up Order Table Table-name Order By Col-name For Large Table?
I have a table with 2 column: id and name. Column name is of type varchar(100). The table has about 0.1 billion rows, taking 11G disk space. Now what I want to do is alter the table by the name column. I ran the command: "order table table-name order by name" 1).Firstly, it create tmp table,which takes 19G disk space. 2).After that,the 19G space seems to be released(I see the free disk space increase by "df"). 3).Then I see that in the mysql directory, the size of the mysql file #sql-14f7-2.MYD" increases 2M every time, but very slow. I guess this mysql file is the new table after order. But how can it be so slow? It only increase 2M every 2 minutes or so, which would takes weeks to finish the command. So could any one tell me how can I speed up this command? Are there any variables I can adjust to make this faster?
View Replies !
ORDER BY A Set
I want to order a result set by a specific order, rather than just ascending or descending alphabetically/numerically. For example if I have a table called region idName 1London 2Scotland 3Wales 4Midlands .. .. .. but I want them ordered so that the result is Scotland, London, Wales, Midlands,... can I use some sort of set in the order by clause? Something like: select name from region order by id {2,1,3,4}; I haven't used mysql SETs before so not sure if they can be applied here.
View Replies !
Order By Str To Int
Hi jave a database and I want to oder my result by a colum containing string bit order them like they where int ex Select * form members order by level level is string containing data like '12' I want to oder like the level is Int 12 and not string '12'
View Replies !
Order By OR
Lets say I have a query similar to: SELECT * FROM company WHERE id='3' OR id='1' OR id='7' ; I want to be able to get the results to come out ordered by the order the ids were searched (the sequence in the WHERE OR; 3 first then 1, then 7.), so the results would be like: Row | id| name ------------------- 0| 3| Joe's Co 1| 1| Buymart 2| 7| The big evil As it is the results returns it like id 1, then 3, and 7.
View Replies !
Row Order
If I run a simple SELECT on a single table and it returns a small sized data set of say a hundred rows or so, and I don't set any ordering constrints (such as order by date or ID or something), if I run this same query over and over, will the row order be the same assuming no changes in the table?
View Replies !
Specify Order
Is it possible to specify order before grouping? I want to group a list of user actions by user id with the most recent at the top. For example, the query would be: SELECT * FROM UserActions ORDER BY Time DESC GROUP BY UserID; That doesn't work because you have to specify GROUP BY before ORDER BY. However, the output of specifying them in the correct order is not what I want. It seems to pick the first record in the database as the GROUP instead of the most recent one.What I'm actually trying to do is get a list of all the Users in order of the most recent UserAction, so as to be able to view the latest acitivity on my web site.
View Replies !
Sql Order By
I have just started re-writing my forum, which is going to take a long time. While I am doing this I have a bug to solve on version 1.3, which is to do with the SQL order by. Each post has a number, 1, 2, ect. Every time a post is added the post number increases. When I get to 10, the ORDER BY section of my script ( I assume ) uses the first number before any others. So 10 is considered less than 2. Ruining the whole order by system. Is there a way that this can be solved???
View Replies !
Help With ORDER BY
I have this query: PHP SELECT p.*, u.points, c.Title AS category, c.CategoryID AS cid, CONCAT(u.FirstName,' ',u.LastName) AS user, u.UserID AS uid, COUNT(b.UserID) AS referrals FROM products AS p, categories AS c, ltd_user AS u LEFT JOIN ltd_user b on p.UserID=b.referrer WHERE p.section=c.CategoryID AND u.UserID=p.UserID AND p.pppicture != '' AND b.ConfirmS = 1 AND b.UniqueIP = 1 and p.power=1 AND u.points>=10 AND p.section IN (1,2,3,4,5,8,9,10,11,12,13) GROUP BY p.productid ORDER BY p.bid DESC, referrals DESC LIMIT 0,10 problem is that query order only by p.bid while I need to sort by both
View Replies !
Order By?
The following mysql statement produces a buch of unordered results: SELECT Approve, ID, Name, Image1 FROM PROVIDER WHERE Approve = Ƈ' LIMIT $from, $max_results; 3 1 5 7 4 6 2 Id like to place an ORDER BY thing in there (ORDER BY ID(asc)) so it appears like this; 1 2 3 4 5 6 7 How do I do that?
View Replies !
Order By A Sum?
Ok this is really hard to explain. Table1 has userid's that correspond to another table along with 2 other fields num1 and num2. I need to get the 15 userids from Table1 with the highest SUM(num1) / SUM(num2). I can do this with PHP but would like to do it solely using SQL.
View Replies !
'Order By'
I'm pulling a list of links out of a DB and ordering by their 'order id'. instead of getting (for example) '1,4,7,12,22', I'm getting '1,12,22,4,7'.
View Replies !
Order Within Order
I have this query: Code: $sql = "SELECT p.id, p.name, c.name FROM projects p, category c WHERE c.id=p.category_id ORDER BY c.name" that sorts my projects per category. Now within one category, i need to sort the projects per name
View Replies !
Order By And Count
I have been trying to do a similar thing for a number of different tables but have given up as i could not find a way to do it but it seems an obvious thing many pople would want to do so i thought i would ask once more to see if it is possible. I have a mysql table like this Id01 Maidstone KENT Id02 Ashford KENT Id03 Bristol AVON Id04 Westminster LONDON Id05 Bath AVON Id06 Chatham KENT Is there a way to order the table by the county and get total for the number of rows fro meach county (for output to a coldfusion page) e.g. KENT (3) Chatham Maidstone Ashford AVON (2) Bristol Bath LONDON (1) Westminster Though formating does not matter
View Replies !
Order By With Group By
If I have a GROUP BY statement, which groups my multiple fields, e.g.: SELECT ......GROUP BY date, store can I have multiple ORDER BY statements with that? e.g.: SELECT ......GROUP BY date, store ORDER BY date asc, store, desc Is this an acceptable syntax?
View Replies !
Order By With Variable
I have listings with coordinates, i would like to do ORDER BY and display listings with coords first. If they don't have coords the value is blank and the coords go both positive and negative values. So i tried an "ORDER by l_xcoord HAVING l_xcoord<>0" but this is acting like a WHERE statement removing all results with no coords. I tried GROUP BY as well but same results.So the question is how can i display all results with the ones that have coords first? It seems like it should be simple but I'm just not seeing it.
View Replies !
Order By Primary Key
I'm building an Ajax table which fetches its contents from mySQL. The idea is that the table displays x results on each page, and when you click 'next page' it downloads the next x results and displays them. The table uses an arbitrary SQL string to fetch results, and I'm stuck on how to access just a small result set. Since the SQL is arbitrary I can't use WHERE myfield = somevalue, because I don't know what 'myfield' is. I'd like to substitue 'myfield' for the primary key of whatever table I'm accessing. So "select * from table where myfield < x and myfield y" becomes "select * from table where table_primary_key < x and table_primary_key
View Replies !
ORDER BY Regex?
I have a list like this: Reckless Engineer St Bonaventures The Blue Mountain The Croft The Cube Club The Folk house I'd like to reorder that ignoring the leading "The".
View Replies !
|
TRACKER
