SEARCH YOUR SOLUTION HERE  

Speeding up my SQL Query on a large database

try without the ORDER BY

and don't forget, it has to look at all rows in the table, unless you have an index on "field" and/or "$field"

Posted On: Thursday 25th of October 2012 10:56:31 PM Total Views:  177
View Complete with Replies




Related Messages:

Help speeding up a SELECT   (111 Views)
I have worked on this SELECT as much as I can and I can't get it to work any faster, but I am positive someone here can see where the problem is: Code: SELECT t1.id, t4.tag_id, t4.name as tag, t4.slug as tag_slug, t4.description as tag_description, t1.header, t1.description as teaser, t1.slug FROM news.news AS t1, news.tag_relation as t2, news.tag_taxonomy as t3, news.tags AS t4 WHERE t2.post_id = t1.id AND t3.tag_id = t2.tag_id AND t4.tag_id = t3.tag_id GROUP BY t1.id ORDER BY t1.`date`DESC, t1.id DESC LIMIT 0,25 I believe I have indexed everything necessary and have simplified this thing as much as possible. The tag select is what is slowing it down since each article has more than one record in the tag_selection table, which is the reason for the DISTINCT and GROUP BY.
db backup of files   (152 Views)
instead of doing a mysqldump can I just backup the physical files in /var/lib/mysql if it crashes, I just copy and replace the folder
Output events grouped by date. Can I do this with a single query?   (153 Views)
My objective is to output a range of events grouped by date. Each event has a start date and an end date (in unixtimestamp format). This is what I am trying to do: date 1 --event --event --event date 2 --event date 3 --event --event ... I can do 2 queries one to get the dates and a second to iterate through the events. However I keep thinking that i should be able to do this with one query. Am i wrong or have I missed the obvious
Can't figure out how to create complex query with group by and count   (264 Views)
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: usertagid | usertagsiteid | usertagtext 1 | 1 | 'search' 2 | 1 | 'search engine' 3 | 2 | 'news' If i feed the query the usertagtext 'search' it should list: 'search engine', 1 If i feed the query the usertagtext 'search engine' it should list: 'search', 1 If i feed the query the usertagtext 'news' it should not return any results. Here is what I have so far but it doesn't accept the usertagtext input into it: Code: SELECT DISTINCT usertagtext, COUNT(usertagtext) AS usertagsum FROM usertags GROUP BY usertagtext ORDER BY usertagsum DESC LIMIT 20 Let me know if that still doesn't make sense, I'm having a hard time trying to describe it. Hopefully you can figure out what I am looking for based on my examples.
Cant export database without weird characters showing up == ?   (245 Views)
1) My local database has weird characters () in there which should be spaces. 2) So I do a find and replace on the entire database to remove them: Code: UPDATE report SET overview = REPLACE(overview, '', ''); 3) I check the data locally and all the 's are removed. 4) I export the database and try to import it on my testing server online, and the characters are still in there! I try to import the same SQL file locally and the characters come back as well. Something is happening when I export the database to an SQL file. I have tried a MyISAM/UTF8 and it didnt work. I tried a MyISAM/latin and it didnt work. what am i doing wrong
Trigger for Duplicate rows (Please help)   (202 Views)
Im creating an employee database for TAFE. I have an employee table with employee_id and place fields. The trigger is activated by the Before Insert event. When there's a duplication of employee_id and place the trigger generates a message. For example if a new inserted row has employee_id 20 place 6, and there is a row with employee_id 20 and place 6 already existed in the Employee table, the trigger generates a message: Duplicate Employee_id 20 Place 6. I don't want a unique constraint. The code is below: Code: CREATE TRIGGER employee_tg ON employee BEFORE INSERT ON employee FOR EACH ROW BEGIN IF NOT EXISTS (SELECT 1 FROM employee e INNER JOIN INSERTED i ON i.employee_id=e.employee_id AND i.place=e.place) INSERT INTO employee(employee_id,place) SELECT employee_id,place FROM INSERTED ELSE RAISEERROR ('Duplicate employee_id 20 Place 6) END IF; END DELIMITER; Im having syntax problem in MYSQL. What could be wrong
Distinct & Group -- kiss my butt   (53 Views)
table looks like this (old table schema, bad I know): company --------------- Honda Toyota Chevy Honda Honda SELECT DISTINCT company FROM `cars` Returns all results SELECT company FROM `cars` GROUP BY company Returns all results Why cant I get this to output the distinct results
Grouping a recordset's results   (103 Views)
I'm hoping this is fairly straightforward, but I'm just not sure of the syntax. Basically, I've set up a little page where my client can post customer feedback on products he sells. The page is here : link here The SQL / PHP currently just looks like : mysql_select_db($database_connPixelar, $connPixelar); $query_rsReviews = "SELECT * FROM feedback WHERE Approved = 'Yes' ORDER BY Product ASC"; $rsReviews = mysql_query($query_rsReviews, $connPixelar) or die(mysql_error()); $row_rsReviews = mysql_fetch_assoc($rsReviews); $totalRows_rsReviews = mysql_num_rows($rsReviews); I'd basically just like to group the reviews by product, so have the product name, eg PBCL10.4 appear just once, with the reviews for that product listed underneath. Hope that makes sense, and any pointers much appreciated. Cheers.
Distorted images from uploads folder   (136 Views)
Hi all, I'm trying to work locally & want access to images that were placed on my web host. When I copy those images to a local uploads folder they appear distorted. Has anyone experienced this before
avoiding duplicates   (187 Views)
I have two mysql tables that are linked: subscribers, and subscribers_data. subscribers holds personal information about a subscriber, subscriber_data holds the technical info. the pk and fk are the subscriber's id. I'm having an issue while inserting email addresses into the subscribers_data table. I build a query and insert new email addresses into the subscribers table. I then want to take those inserted id numbers and insert them into the subscribers_data table. If I try to insert duplicate email addresses, they are ignored by the subscriber table (as they should be), however, the subscribers_data table continues with INSERTs. For instance. If I add two email addresses, then the subscribers table will now have 2, and the subscribers_data will have 2. Now, If I attempt to add those exact same email addresses, the subscribers table will reject them, but the subscribers_data table will now have 4 rows. It should reject them too. I'm just not sure how to do that. I tried foreach() and while() loops, but kept getting the same results. Code MySQL: CREATE TABLE IF NOT EXISTS `subscribers` ( `id` int(10) unsigned NOT NULL auto_increment, `fname` varchar(30) collate utf8_unicode_ci NOT NULL, `lname` varchar(30) collate utf8_unicode_ci NOT NULL, `email` varchar(60) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Subscriber information' ; CREATE TABLE IF NOT EXISTS `subscribers_data` ( `sd_sub_id` int(10) unsigned NOT NULL, `sd_date_added` datetime NOT NULL default '0000-00-00 00:00:00', `sd_active` char(1) collate utf8_unicode_ci NOT NULL default '0', `sd_verified` char(1) collate utf8_unicode_ci NOT NULL default '0', PRIMARY KEY (`sd_sub_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='additional subscriber info'; PHP Code: //counteachPOSTedemailaddress $insert_count=0; $insert_id=get_max_sub_id()+1;//subscriberidneedstostartathighestid+1 $ok=array(); foreach($emailsas$email){ if(check_email_address($email)){ $ok[]="($insert_id,'$email')"; //checkforduplicateemailaddresses $email_check=email_check($email); if($email_check>0){ //emailaddressalreadyexists! $dup_error=1; $dup_email[]=$email; $dup_count=count($dup_email); } $insert_count++; $insert_id++; $new_email=$email; }else{ //errorstuff } } //createtemptableforinsertingnewemailaddresses $sql=mysql_query("CREATETEMPORARYTABLEsubscribers_temp(idINT(10),emailVARCHAR(60))TYPE=HEAP"; if(!empty($ok)){ $insert_query="INSERTINTOsubscribers_temp(id,email)VALUES".join(',',$ok); //insertnewemailaddressesintosubscribers_temptable $sql=mysql_query($insert_query)ordie(mysql_error()); } //getemailaddressesfromsubscribers_temptableandinsertintosubscriberstable $sql=mysql_query("INSERTIGNOREsubscribers(id,email)SELECTid,emailFROMsubscribers_temp"); //now,insertsubscriber'sidnumberandadditionaldataintosubscribersdatatable $query=mysql_query(" INSERTINTOsubscribers_data( sd_sub_id, sd_added_by, sd_date_added, sd_active, sd_verified )SELECTid,'a',now(),1,1FROMsubscribers_temp");
log-update in my.ini   (162 Views)
If i put log-update=update in my ini it works fine,but as soon as try to specify a different location i.e. log-update=/backups/update i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work. Any advice would be greatly appreciated.
MySql update multiple rows in single table   (181 Views)
Hi Everyone, I have a Bids table and I need to set prices for products and create bids. So, my problem is if the user selects a 100 or 200 products and sets prices for bidding, is there any efficient way to update all of them at once Right now its updating one record at a time.
Displaying ALL Dupes.   (52 Views)
I am runing these queries in my phpmyadmin to find dupe subjects in a table: QUERY 1: SELECT SUBJECT, COUNT(SUBJECT) AS NumOccurrences FROM TABLE GROUP BY SUBJECT HAVING ( COUNT(SUBJECT) > 1 ); QUERY 2: SELECT DISTINCT SUBJECT from TABLE group by SUBJECT HAVING count(SUBJECT) > 1; Both work perfectly fine for me, but the problem I am facing here is that it shows only the first record and not the other dupes with them. How Can I SELECT and Display all the dupes Not only the first one. TIA.
Efficient way to count groups of records   (131 Views)
I have a database that has items and rank and I want to get the count of items for all of the ranks. So the data is like... item1 rank 0 item2 rank 0 item3 rank 0 item4 rank 1 item5 rank 1 item6 rank 1 item7 rank 2 item8 rank 2 Where there are about 500 items and ranks 0-10. What would be the best SQL query to get the following output rank0 - 3 rank1 - 3 rank2 - 2 I know I could do a count when looking for rank0, but I prefer not to call it 11times.
Group by week   (83 Views)
hey I am new with php and mysql so bare with me i have a table called MESSAGES and it has ID, NAME, DATE, MESSAGE so i want a way when the users insert data in the table it would print links for example: week # 1 ( contains results of week # 1 ) week # 2 ( contains results of week # 2 ) week # 3 ( contains results of week # 3 )
email groups (best practices)   (226 Views)
, I will like to know the best practice for grouping email addresses in a table. We have a basic table that has email address for admin users. Which goes thus CLIENTS EMAIL emailID int username: varchar 20 Client_email varchar 150 The Above table is for our admin users. Enabling them to send out emails to clients. Based on their bills. We have a second table for mass mailing. Which has emails in groups. Each group is based on the above entry. And groups could range from 5 users up to 100,000 users. Whats the best way to implement this. Do I create a seperate column for each email entry, knowing that this table would be extremely big, with a lot of repeated values, ie username etc. Or group them TOGETHER as varchar(max) PLEASE ADVICE, best practices
I need to update a large amount of values in 2 columsn in 1 table   (220 Views)
I have a table that I need to change all values for all rows in 2 columns (used for mapping products). Is there any SQL that will let me just say something like, update the 2 columns and insert these values in this order Example: these are the values: 2, 4, 6, 3, 4, 1, 3, 4 And those values would be inserted as the values for the first 8 rows Sorry, noob question probably Thank you
Select query Duplicating results   (141 Views)
The below query works in so far that it returns the expected results except that it returns the same results twice, I cant see where I have went wrong with the query, can you
Finding Duplicates   (101 Views)
Is it possible to run a MySQL query that will just find any rows that have the same value as another Or even to find any where one particular field's value appears more than once in the table Not sure how clear I'm being, so I'll try to explain myself, just in case. Lets assume that the table has allowed duplicate job numbers in a table, and I want to find them all, rather than a particular job number, can I run a query that says something like "SELECT * FROM table WHERE num_rows(job) > 1"
insert...update in mysql?   (360 Views)
I am trying to insertupdate into multiple tables how do i do that I have 3 tables e.g. table x table y table z. table x (x_id) table y( y_id, x_id) //x_id-foreign key referencing table x table z (z_id, y_id) //y_id-foreign key referencing table y