Order By Super Slow
I have a text field that's indexed. When I try to sort this column (alphabetical), the first page is pretty fast. However, getting to the end, it slows down a lot, taking about 7 seconds just to show the last page of results.
Is there any way to speed up the sorting?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Slow Answer When Using ORDER BY
I have two tables and want to return artists that begins with A,B,C and D If I use ORDER BY it takaes about 10 times longer to execute compare to not using ORDER BY. SELECT medialib.guid, medialib.artist, medialib.title FROM songstation INNER JOIN medialib ON ( songstation.songguid = medialib.guid ) WHERE ( songstation.stationguid = 'ffeeaab6-9558-4c85-9fc8-27e962d47ec7' ) AND ( medialib.artist LIKE 'A%' OR medialib.artist LIKE 'B%' OR medialib.artist LIKE 'C%' OR medialib.artist LIKE 'D%' ) ORDER BY medialib.artist LIMIT 0 , 30 I have tested BETWEEN, but the result is the same, slower when using ORDER BY.
View Replies !
Speed Up Query, Order By Column Is Too Slow!
I have a query that takes ages: SELECT tbl1.a, tbl2.b MATCH (tbl1.a) AGAINST ( 'someValue'IN BOOLEAN MODE ) AS score FROM `a` , `b` WHERE tbl2.b = 'someRestriction' ORDER BY score DESC, b.tbl1 DESC LIMIT 20 but because there are thousands of rows it takes ages to do both of the orders, however just : ORDER BY score LIMIT 20 -- is really quick and : ORDER BY b.tbl1 LIMIT 20 -- is really slow is it possible rearrange a constantly updated database so that its naturally ordered by b.tbl1 (so there will be no need to do the ORDER BY b.tbl1 query each time), or are there any other methods to speed this up (putting it all in one table instead of 2, is that significant?)
View Replies !
Restore Database With Stored Procedures Requires SUPER Privileges ?
I am working with an ISP and trying to restore a mysql database. I recently added stored procedures to this database, and when i tried to restore it i got this message. #1227 - Access denied; you need the SUPER privilege for this operation The DEFINER is set to the current user account (the one i logged in with). When i talked to the ISP tech support they said they turned off stored procs in MySql because they require SUPER privileges to run. Here is the text of our online chat.. Brian [9:25:29 PM]: I am trying to restore a mysql database that has stored procedures in it. But the restore is failing on the create for the stored procs saying that i must have 'super' privileges to do that. How can i restore this database? Keene [9:25:48 PM]: We do not support stored procedures on our servers. Sorry :( Brian [9:26:02 PM]: you are kidding me [9:26:30 PM]: why not? Keene [9:28:11 PM]: Because they require super-user privledges. We've been trying to re-write MySQL so it doesn't, but because of the way it's coded and designed, it's simply not possible in a shared hosting environment, at least not yet. Brian [9:30:39 PM]: it doesn't require super-user to execute them though, right? Keene [9:31:29 PM]: It actually does. That's the kicker. There's no way to implement it without root access. Brian [9:32:03 PM]: this is from the mysql manual for 5.03... [9:32:04 PM]: If you specify the DEFINER clause, these rules determine the legal DEFINER user values: * If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. [9:32:59 PM]: super is required to specify another definer according to this, but not if the definer is the current user acct Keene [9:35:20 PM]: Yes, the documentation does say that, but the code doesn't actually execute like that. It still requires root access to run the stored procedure, even if the definer is the current user. Although it doesn't act like it's using the root privileges, it does use them in the background, which opens up the possibility to create a stored procedure that grants root access to anyone. [9:35:36 PM]: So, we've disabled them. [9:35:58 PM]: At least until MySQL can create a patch that processes them differently. +++++++++++++++++++++++++++++++ So my question is - is this true? Is there a bug that requires SUPER privileges to create/execute a stored proc in mysql?
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 !
Slow
What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.
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 !
Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong? I'm also including the dump of the table definitions. This is a cd cataloging database. Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:
View Replies !
MySQLdb Slow
I have a Python program that parses a file and inserts records into a database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my parser runs *really* slow. Writing out to CSV files is fine, but when I try to insert the same records in a MySQL5 database, it slows to a crawl. Using MySQL 4.1 seems fine. The data seems to be inserted correctly, it's just really slow.
View Replies !
Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB. Using the mysql administrator gui to create a backup, everything goes fine, and restores quickly. Using the command line: mysqldump %dbname% --single-transaction > %dbname%.sql creates a file about 15% smaller than the gui produces, and is EXTREMELY slow to restore. I have tried adding locks, skip opt, everything. What does the gui use for a command to create this dump?
View Replies !
HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns... ID | Title title is indexed. When i run a query like this ------------------ SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds... ------------------ Even a single like statement just on `index` (heap) takes 3-6 seconds. Here's the table stats... ---------------------------- Data 397,442 KB Index 24,639 KB Total 422,081 KB ---------------------------- Why??
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that. MySQL said: Documentation #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
View Replies !
Slow Db Access
I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!
View Replies !
Slow Insertions
I have a MyISAM table with fixed width rows (ie no vars). I also have keys disabled. I am running a huge data load process. When it first started it was inserting about 38 records a second. Now, after about 200,000 records, it speed is about 22 recoerds per second. Whats going on here? and how can I improve it? I am loading about 15 million records, so the difference between 22 per sec and 38 per sec is SIGNIFICANT (ie 2 days).
View Replies !
Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table. Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.
View Replies !
Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form. My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot? Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?
View Replies !
Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished) select date from temps where date in (select distinct date from observations where camera like "a") The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?
View Replies !
Slow Connection
I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow. I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??
View Replies !
Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?
View Replies !
Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....
View Replies !
Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor) This is the query : SELECT NPL, PP FROM P_A INNER JOIN ACTIONS ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in (SELECT NA FROM P_A WHERE NP ='ABC') P_A has 5 columns and 12000 rows Actions has 5 columns and 770 rows Any suggestions ?
View Replies !
Slow Request
I want to run the following request: mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263); where fingerprints is the following table: ....
View Replies !
Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?
View Replies !
Why Does MySQL So Slow
I just changed to use MySQL few days ago but it was a bad idea. My server now is running very slowly with the database. I'm using Perl5 and DBD::Mysql in my script. The system is Linux9, Apache2. I looked at these mysql pid and saw a lot of activities (about 400) while there are more 100 users online at this moment and lots of running under a the same pid number.
View Replies !
Slow Max() Selection
I have a large table with some fields. Two of them are: year char(1) && year's rightmost character OrderNo integer Both are keys (BTREE). I need to select the last order in the year. select max(OrderNo) from ORDENES where year='8' While the year is growing the selection speed id decrement (12 seg).
View Replies !
Slow Connect
Does anybody know why it sometimes takes more than 10 seconds to connect to a database and sometimes it just takes half a millisecond?
View Replies !
Slow Connections
I am using MyODBC-3.51.11-2-win on Win 2003 OS. I am not able to see all of the connections in the list under the System DSN tab. The connections that show allow the ASP pages to run at an expected rate. However, the ones not showing in the list are running extremely slow. If I attpemt to recreate the connection I am told that the connection already exists and asks if I want to replace the existing connection. Wheter I click yes or no the connections do not show and the pages run slowly. How do I get them to show or resolve the issue. The ASP code is the exact same SQL statements and connection strings as the in previous applications.
View Replies !
MySQL Slow
I had downloaded a few years back mySQL v3.51 installed but never used it. Now I wanted to convert some B-TREE databases to mySQL and did some testing via ODBC to insert 70,000 records: My results: MS ACCESS: ~60,000 msecs MYSQL v3.51 ~18,000 msecs Impressed with the speed, I went ahead and got the latest MySQL v5.1. Uninstalled the older version, I had nothing there to preserve, so I did a simple new install with MySQL v5.1. I noticed the size of the files and BINEXE increasted by 1,000,000%. Ok, Bulky. Not a problem. I reran the same ODBC test, and now I got: MYSQL v5.1: ~450,000 msecs or 7.5 freaking MINUTES! What the hell happen? Nothing was done. I'm knew to MYSQL. I just installed it with all the defaults. I did choose "developer's machine" for the "optimizer wizard" I can't redistribute MYSQL v3.51 and force it down people's throats! I have to use what they are using already, if already installed. Not even my current system takes 1 minute to add 70,000 records. Why 7.5 minutes? All it is simple inserts/free statements.
View Replies !
What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?
View Replies !
MySQL Slow Log
I have the long query time set to 15 yet MySQL is still showing results with a query time of 0 in the slow query log. It says enter time in "seconds" in the MySQL Administrator but did it mean in milliseconds??
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 !
Slow Authentication
MySQL V 5.0.18 on SUSE 10.1 I'm not a complete *nix noob, but I sure as hell ain't a *nix or MySQL pro. This is a new installation. Everything screaming fast. Unless it deals w/authentication. Try to get in w/SQLyog from W2K locally ... intitial connection takes ~20 seconds. Then everything screaming fast. Web Server (W03) attempts to connect via MyODBC ... same result ... initial connection takes ~20 seconds. Subsequent queries screaming fast. VNC into the box at any time ... everything fast. (would seem to eliminate network/connection issues)
View Replies !
Slow Query
i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename, Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title, Buyer_2_Prename, Buyer_2_Surname, Email_Add, Price_Max, MAX(activity_t.Date) AS lastcomm FROM cl_t INNER JOIN cl_want_t ON cl_t.Client_ID = cl_want_t.Client_ID AND Agency_Code ='$agencyloggedincode' AND Deleted = 'N' LEFT JOIN activity_t ON Buy_Sell = 'B' AND Ref_No = cl_t.Client_ID WHERE cl_t.Sales_Agent_ID = $agentid GROUP BY cl_t.Client_ID ORDER BY $order The problem is the call to MAX(activity_t.Date) AS lastcomm activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds. I have indexing on activity_t.Date & activity_t.Ref_No Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.
View Replies !
Why Is This Query Too Slow?
I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so? MySQL SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id I traced the cause to this line count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id) But when i execute something like this MySQL SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john' it is quite fast (around 0.03 seconds) So why does it slow down when i'm joining the above query with 3 other tables ? Should i use INTEGER for user_id instead of string like 'john'?
View Replies !
How To Log-slow-queries
I'm running MySQL 5.0. Now I would like to log-slow-queries. With MySQL 4 I used mysqld --log-slow-queries --log-long-format start but now I get following note: mysqld: Too many arguments (first extra is 'start'). Use --help to get a list of available options It looks like starting and stopping now only works with etc/init.d/mysql start
View Replies !
Service Pack 4 Slow Down
I'm running Windows 2000 Server and MySQL 4.1 and all has been great until yesterday when I installed Windows Service Pack 4. Now when I query a database it displays very slowly. Before the service pack it was almost instant. Everything works properly, it's just very slow. Anyone heard of anything like this? If I use the MySQL command line and query the database it's very fast. The slow querys are from php pages.
View Replies !
Slow Joining Of Two Tables
I am having trouble combing data from two tables. The tables have exactly the same layout, but have different : mysql> describe MONITORINGUNIT1_DATA; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | dt | datetime | | | 0000-00-00 00:00:00 | | | wg | float(20,3) | YES | | NULL | | | dflag_wg | tinyint(4) | YES | | NULL | | +-------------+-------------+------+-----+---------------------+-------+ mysql> select count(dt) from MONITORINGUNIT1_DATA; +-----------+ | count(dt) | +-----------+ | 24144 | +-----------+ 1 row in set (0.00 sec) mysql> select count(dt) from MONITORINGUNIT2_DATA; +-----------+ | count(dt) | +-----------+ | 1464 | +-----------+ 1 row in set (0.00 sec) Very briefly, [dt] contains an hourly date/time stamp representing when the reading [wg] was taken. [dflag_wg] contains a integer that describes the data (over threshold, under threshold, etc). The DB is populated automatically by a Python script that executes once per hour. If I want to get the overlapping data (with the same date/time stamp) I use this query: Code:
View Replies !
MySQL Slow In Windows NT4
I am running the Apache web server, MySQL v4, and PHP on an NT4 server. Apache runs great, but the auction software I am using (Web2035 Auction software written in PHP) is very, very slow. Sometimes it takes 20-30 seconds to bring up an auction page from the items table which has less than 200 records in it. Can anyone can give me some pointers on where to start looking? (I don't know if the bottleneck is with MySQL or PHP or what I might need to look at to enhance the performance of either package.)
View Replies !
Slow Database Retrieval
iwhat would be the cause of our problem? i don't thnk it has someting to do with the way i run my queries from my applications since i do not encounter such problems on our old machine..Also i tried to execute queries from my sqlyog : ex: select * from db_town result: 8902 rows() in 2687 ms where on the old server it should take only about 421 ms select * from diagnostics_detail result: 42499 rows() in 27609 ms where on the old server it should take only about 4375 ms.
View Replies !
Slow Query Log Not Staying On
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt command line option to enable the slow query log, started MySQL, and the option showed up as turned on. Then later I restarted the server, and the slow query log option went back to being turned off. Is this a Windows problem in not remembering the service parameter? Has anyone else seen this?
View Replies !
Slow When Using Desc Clause
I'm scrolling by Order_# where I find and display on screen the < (lesser then) value of Order_#. Here's the syntax: "Select * From Orarchd Where `Order_#` < " & lngOrderNumber & " Order By `Order_#` Desc".... Order_# is the primary index. lngOrderNumber is the current value of Order_#. Here's the problem ...it takes forever to find the next record (at least 10 seconds)
View Replies !
|
TRACKER
