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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 Replies !
Multi-column Index, Is It Slow ?
If I use a multi-column index, is it ineffecient ? how much of a performance hit ? I'm creating an order table, that will have a city code as one of the columns. index will be ( orderID, city_code ) my other option is to use separate tables, and store the table name for that city, in a PHP session variable.
View Replies !
Speed Up This Query
I have a table with just userid,indexid,and imgid and I use this: SELECT count(userid), indexid, count( imgid ) FROM `photoindex` GROUP BY `indexid`; the table has 2 million rows and it says it take 2sec to finish but really, it takes 15 sec after sending the query I think the lag time is due to mysql reading the entire table into a temporary table in order to do the GROUP BY.can anyone help me reduce this query time? is there a SQL setting to increase the memory to speed up this type of query?
View Replies !
Speed This Query Up?
I have a simple query I am running, but it is taking 6 seconds to run. Im not sure what I can do to speed this up. Am I simply SOL or am I not doing something I could be?
View Replies !
Speed Of Query
I was on the PHP forum and they sent me here. I am quite new to working with large databases (approx 1 million records per table) and am having problems with the speed of viewing the data. I have a page that requests all users from a certain department and displays their names etc (this uses a simple 'SELECT first_name etc FROM staff WHERE department = 3' style query). There are around 800,000 staff in the table and so this query can take a long time to run (sometimes only to return 1 record). Is there any way of speeding up this query? Someone mentioned that creating an index on the department field may speed things up but they didn't seem sure, and I don't know how to do this so would only spend time learning if it might make a large difference! Large websites must deal with tables far larger than this and I was wondering how.
View Replies !
Can You See Any Way To Speed This Query Up A Bit?
SELECT SQL_CALC_FOUND_ROWS title, content, URL, id, (MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 1.5) + (MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.8) + (MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.5) AS score FROM $searchType WHERE MATCH(title, URL, content) AGAINST ('$searchQuery' IN BOOLEAN MODE) AND content != '' ORDER BY score DESC LIMIT $limit OFFSET $offset
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 !
Slow / Server Crash When Adding A Column
I have a table with about 100,000 records whose description is: +-----------------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+----------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | | PRI | NULL | auto_increment | | url | varchar(255) | YES | MUL | NULL | | | how_found | varchar(255) | YES | | NULL | | | use_for_parser | smallint(5) unsigned | YES | | 1 | | | checked_by_parser | smallint(5) unsigned | YES | MUL | NULL | | | monitored | smallint(5) unsigned | YES | MUL | NULL | | | date_found | datetime | YES | | NULL | | +-----------------------+----------------------+------+-----+---------+----------------+ I tried adding a column with the command: alter table url add column use_for_aol_mail smallint unsigned default 1 after checked_by_parser; This caused the command prompt to hang and apparently crashed the Web server which then had to be rebooted. Is there anything special to keep in mind about adding columns to such a large table?
View Replies !
How To Speed Up Select Query
I have a very big table >2GB. My query is as follows: Select JDay, cda_lake.Cell_Num,NH4, NOx, TP, DO, pH from cda_lake, site_cells where Site_Num =1 and site_cells.Cell_Num = cda_lake.Cell_Num and truncate(JDay,0)=152 order by JDay,Cell_Num; It took more 1 min to get 20504 records. Can you tell me what's wrong?
View Replies !
On What Depends Speed Of The Query?
I didnt have too much in common with large databases and i wonder what kind of query is suposed to be complicated. i mean in terms of performance. are there the golden rules like: when you have more then 5 where conditions use union or something? do you think this query could be written in a "faster" way?
View Replies !
Query Vs Logic Speed
I have a website which uses some fairly large tables ( > 300,000 rows ). As you can imagine accessing these tables can often be very slow. I am attempting speed up my sql and was wondering if, using php, it is faster to perform an sql query or approximately 10 lines of logic? What I am trying to do is, instead of querying the table thousands of times and performing my calculation on each small chunk, I would like to try to get the whole table at once, sort it by the ID's and just iterate over it and perform the calculations I need. Would this speed things up? how much if any? and is it worth the effort?
View Replies !
Indexes To Speed Up A Duplicates Query.
OK, say you're trying to find all the non-exact duplicates in a table, and especially do it with a single query, so that the check can be part of a user interface. Then naturally you're going to have a slow query, because it has to compare every row with every other row. You ought to be able to cut out exactly half of the records in the second table, by just saying: SELECT * FROM TABLE LEFT JOIN users dups ON users.created<dups.created But I can't get MySQL to use the index I have on 'created' (same results for integer-primary key, & a string field). My best guess why not is that (according to the manual) "MySQL will not use an index when ... the use of the index would require MySQL to access more than 30% of the rows in the table" - is this the reason? Another option: say that I thought that my duplicates will always have the same letter in their surname, then I could make an index on the first letter of the surname, and make the join just on *that*. This should make the number of rows needed up to 26 times less, right? But exactly how is this done? MySQL won't use my index on the following query: SELECT * FROM TABLE LEFT JOIN users dups ON SUBSTRING(users.name,0,1)=SUBSTRING(dups.name,0,1)
View Replies !
How To Speed Up Query On Table With 10k+ Entries
a table with 10k entries in the moment. The table will increase by approx. 30k entries each year. I have seen, that the large number of entries has slowed down my queries (from <1 sec to over 4 sec). I tried to add an extra field called 'archived' and set it to '1' if I only need the entry for statistical purposes. But with the where-clausel speed doesn't improve. What can I do to increase speed without having to move 'archived' entries to another table?
View Replies !
Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys. The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again. for example: select * from table where id=2 order by index_field desc; 4/5 seconds. select * from table where id=2 order by index_field desc; 0.02 seconds. select * from table where id=3 order by index_field desc; 4/5 seconds. select * from table where id=3 order by index_field desc; 0.02 seconds. etc, etc...
View Replies !
How To Speed Up Mysql Query/php Code
I have a table in a mysql database. with 5 fields and the field ID as the primary key. There are about 220 records in the database and the field 'Mediumimage' contains the file paths for the images to be dispalyed on the page. the PHP code for the query is shown below. Im just wondering why it takes quite a while to process the pictures which are of a small size. $query_handsets = "SELECT Handsets.Manufacturer, Handsets.Model, Handsets.Mediumimage, Handsets.`Handset Url` FROM Handsets WHERE Handsets.Manufacturer LIKE'". mysql_real_escape_string($_GET['manu'])."'"; $hansets = mysql_query($query_hansets, $Mobilecomparsion) or die(mysql_error()); $totalRows_hansets = mysql_num_rows($hansets); <?php $i = 0; while ($row_hansets = mysql_fetch_array($hansets)) { if ($row_hansets['Manufacturer'] != $lastManufacturer) { if ($i == 0) { echo '<img src="images/handsets/logos/'. $row_hansets['Manufacturer'].'.gif" /><br/>' echo "<table cellspacing=ཆ'><tr>"; $lastManufacturer = $row_hansets['Manufacturer']; } else { echo "</tr></table>"; echo '<img src="images/handsets/logos/'. $row_hansets['Manufacturer'].'.gif" /><br/>' echo "<table cellspacing=ཆ'><tr>"; $lastManufacturer = $row_hansets['Manufacturer']; $i = 0; } } elseif ($i % 13 == 0) { echo "</tr><tr>"; } echo "<td align='center'><font face='Ariel' size=Ƈ'>"; echo "<img src='images/4x4.gif' width=ླྀ' height=Ƈ'><br/>"; echo '<img src="images/handsets/'.$row_hansets['Mediumimage'].'" /><br/>' echo $row_hansets['Model']; echo '</td>' $i++; } echo '</tr></table>' ?>
View Replies !
Does Query Speed Depend On The Number Of Coloms?
Does the query speed also depend on the colums that you don't use in the query (so not in the where and not in the select part). I have a lange unused backup field in every row. I only put the backup information into that field once. But never use it on my live database. Does it speed up my queries if I put the backup information into an other table?
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 !
MYSQL Query Speed + Explanations Needed
Sometime I need to delete results set of the some query. Like DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) But when I have a table with 100k+ records it become a big problem. Script just hangs up forever As I understand, mysql get each id from table `table` and reexamine SELECT? I tried to create temprary table and filled it with select like DELETE FROM table WHERE id IN (SELECT id FROM `some_temp_table`) But its hangs up too... EXPLAIN do not works for the DELETE ... statement, manual do not helped. So, how does this query work "on the low level"? DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) Any suggestion how can I improve speed for the large tables?
View Replies !
Order By On Column And Reoder By Another Column
im using php and mysql, but i would like from the sql statement to get the results of a query order by a column and then reorder by another column that is: i want to select the last four entries accoding to the date. SELECT * FROM table ORDER BY Date DESC LIMIT 4 but I also want to order those 4 entries i got, according to their values at the order column like this SELECT * FROM table ORDER BY ordercol ASC LIMIT 4 how can i put this two together? i thougt of SELECT * FROM table ORDER BY Date DESC, ordercol ASC LIMIT 4 but it gets the last four entries and then if two entries have the same date it orders them according to the ordercol, how can it do it?
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 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 !
Slow Query->Efficient Query
I have a query that is running really slow !!!! I have joined on Key fields and indexed the tables fully but it is still solw. -------------------------------------------- select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual from details d join poten a on a.id = d.id left join recieved p on d.id = p.id left join users u on a.signed = u.userid where d.paid > '01-Dec-2005' and d.authorrceived is not null and d.authorrefused is null and ((d.payment starting 'E' or d.payment starting 'e') or (d.payment starting 'Q' or d.payment starting 'q' and p.target = '500')) order by d.paid, a.signed, d.id
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 !
View Slow Query Log
I searched here, google and MySQL docs but did not find an answer. I'm using MySQL-Front from a Windows platform to administer a remote database. The remote server doesn't have phpMyAdmin or anything like that. MySQL-Front reports 133 Slow Queries and an average of 15 queries per second. But I dont' know how get more information than that.
View Replies !
Slow Query Log Files
I have edited my.ini file to create a file called slow queries. My problem is that when the server starts up i do not get a full list of all slow queries. Is there anything else i need to change? How else can i come about in getting all queries that took a long time to execute? im using mySQL 5.0.9.
View Replies !
Query Execution Too Slow!
I have migrated from MySQL 4.1.22 to MySQL 5.0.45. Many of the queries that were working on my old server is working too slow in my new server(MySQL version 5.0.45). I am not aware why is this happening. Actually there is no error message or query failing. The query is executing, but the time taking to execute the query is too long. This problem is not occurring for all queirs, but only for some complex queires, that is queries with Left Join or Right Join, Group By etc. I have also checked the MySQL details through SSH(that is by mysql> SHOW VARIABLES;) Now one thing I found is there is lot of difference in key_buffer_size, table_cache, join_buffer_size, read_buffer_size, read_rnd_buffer_size , table_cache etc between my new server MySQL and old server MySQL. Will this be the reason for query execution taking too much time. Can any one guide me why this is happening. The striking thing is that these quires was working very much fine and faster in my old server.
View Replies !
Recycling The Slow-query.log
Am using v5.0.22. log-slow-queries=/var/lib/mysql/log/slow-query.log, but this has grown to over 4GB. Can this slow-query.log file by recycled without starting the server? Meaning can I do the following: mv slow-query.log slow-query.OLD.log, followed by a touch slow-query.log WHILE the server is running?
View Replies !
Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows. My table is getting kinda slow here is the query i am using UPDATE nametable SET sectionname = replace(sectionname,'".$oldsec."','".$sec."'), categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ' where sectionname='".$oldsec."' and categoryname='".$oldcat."' ; I am wondering if the same thing is possible with an insert... on duplicate key statement? I cant seem to get the insert statement to work, but not even sure whether it is appropriate. With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)
View Replies !
Slow Query Execution
Right now I am fazing one issue which is getting hell out of me. I am explaining the issues step by step. 1.I have one site running from last 3 years with large database and there is one main table which has maximum load. 2.Now, I have redesigned the site with lots of changes within this table too. 3.The problem is that the server is same, queries are same but output in the new database is taking 50 times more execution time. 4.I also just copy paste the table, and fire the same query... Strange even that is not working... Is it something where newly added table or database is having issues in the server? I have tried all, compared the structures of the data and table but still the query result is too slow on live testing sever. While same query in main site of same server is running perfectly.
View Replies !
Slow Query Issue
I'm getting issues with slow queries being generated by a table in my database for my app, this query is consistently generating an entry in the slow query log: SQL Code: # Query_time: 2 Lock_time: 0 Rows_sent: 27 Rows_examined: 958SELECT `p_id` FROM `products` LEFT JOIN `products_images` ON (`p_id` = `pi_pid`) WHERE `pi_image_mime` IS NULL AND `p_archived` = '0' GROUP BY `p_id`; an explain query generates this: 1 SIMPLE ls_products ref p_archived p_archived 1 const 866 Using where; Using mysqlorary; Using filesort 1 SIMPLE ls_products_images ref pi_pid pi_pid 4 shop.ls_products.p_id 12 Using where; Not exists and a desc generates this: SQL Code: pi_id  int(11)   NO   PRI   NULL  AUTO_INCREMENT     pi_pid   int(11)   NO  MUL  0        pi_label  varchar(255)  NO               pi_primary_image  tinyint(1)   NO      0         pi_sort_order  int(11)  NO      0       pi_micro_blob  mediumblob   NO                pi_thumb_blob  mediumblob  NO             pi_large_blob  mediumblob  NO             pi_zoom_blob  mediumblob   NO                pi_image_mime  varchar(25)  NO               pi_date_added  datetime   NO     0000-00-00 00:00:00        pi_filename   varchar(50)   NO                pi_image_label   varchar(255)   NO
View Replies !
Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out. The query I'm running is too slow...can anyone tell me what I can do to speed it up..if I can at all? I was wondering if because ZipListMatrix has 3.3 million rows that 8 seconds is all the faster it's going to be. Any help is greatly appreciated! I have already "optimized" the tables.
View Replies !
Server Slow When Exec Following Query
i try to tweak mysql server but the process still very slow to exec o following query exec time 2min for 11k row in two table update uploaded,uploaded_job set uploaded.export = 'yes' WHERE uploaded.work_order =uploaded_job.work_order and uploaded_job.claim_status IS NULL i had try indexing and optimise my database result still the same.
View Replies !
Slow Query Wile Using Indexes
i am having a problem with a aparently normal query, i am using indexes and it is still very slow, as the table grows biger and biger i am afraid the system cant hadle it... Here is the table data: ....
View Replies !
Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team. The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem. The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit. Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?
View Replies !
Slow Inefficent Query (FIXED)
I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously) My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results ....
View Replies !
Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options. Here is what I did. But I dont see it working yet. 1. cd /var/db/mysql 2. chown mysql slowquery.log 3. touch /usr/local/etc/my.cnf 4. chown mysql /usr/local/etc/my.cnf vi my.cnf [mysqld_safe] -u mysql --log-slow-queries=/var/db/mysql/slowquery.log so now when I type: mysqladmin shutdown and than mysqld_safe & my sql restarts but the log files are not being used. also - how do I know if my my.cnf is being used at all?
View Replies !
Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself... Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary. I'm running Redhat Enterprise.
View Replies !
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
View Replies !
Help Rewriting A Slow Phpbb Query
I have a "glance" or "Recent Topics" list on my forums that have become fairly complex. I modified an already feature rich glance mod to allow users to select individual forums to exclude from showing topics in the list. As well when users are members of certain forum groups, they see topics from the group forum in the list, and they are highlighted a different colour. The main SQL query to create the list often is showing up in the MySQL Slow_query log and I'm pretty sure is the main cause for the page loading slow. I am no mysql Guru, so I thought I would seek the advice of some to improve or totaly rewrite this slow query. PHP $sql = "SELECT f.forum_id, f.forum_name, t.topic_title, t.topic_id, t.topic_last_post_id, t.topic_poster, t.topic_views, t.topic_replies, t.topic_type, p2.post_time, p2.poster_id, u.username as last_username, u2.username as author_username FROM " . FORUMS_TABLE . " f, " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u, " . USERS_TABLE . " u2 WHERE f.forum_id NOT IN (" . $forumsignore . $glance_recent_ignore . ") AND t.forum_id = f.forum_id AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND t.topic_moved_id = 0 AND p2.poster_id = u.user_id AND t.topic_poster = u2.user_id ORDER BY t.topic_last_post_id DESC"; $sql .= ($glance_recent_offset) ? " LIMIT " . $glance_recent_offset . ", " . $glance_num_recent : " LIMIT " . $glance_num_recent; The "NOT IN" list varies per user, but here is an example: NOT IN (77,75,76,25,26,37,63,64,66,67,67,1,25,26,37,70,28,75,76,78)
View Replies !
Can't Turn On Slow Query Logging
long_query_time = 1 log-slow-queries = /var/lib/mysql/slow_queries.log Is the above syntax not correct for enabling slow query logging? All examples I've seen have the dashes in the second variable and underscores in the first. When I restart MySQL with those lines in my.cnf, it fails to start, but writes nothing to its error log. /var/lib/mysql/slow_queries.log exists, is owned by mysql, and has read/write permission.
View Replies !
815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance. My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,
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 !
Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends. Will like to know: Assuming I have a table with 8 columns. When I am querying these records with specific WHERE conditions, will SELECT * be faster, or SELECT (3-5 x columns) be faster?
View Replies !
|
TRACKER
