query-optimization

How to perform this task in 1 single, efficient mysql query?

I have a list of TV shows stored in 1 table. Another table stores show genres (action, romance, comedy). Most shows usually have more than 1 genre, so having a single tv_genre column and putting the genre ID in there isn't an option. I could create a look up table which would store tv show id + genre id, and I could insert 1 row for e...

How can I further optimize a derived table query which performs better than the JOINed equivalent?

UPDATE: I found a solution. See my Answer below. My Question How can I optimize this query to minimize my downtime? I need to update over 50 schemas with the number of tickets ranging from 100,000 to 2 million. Is it advisable to attempt to set all fields in tickets_extra at the same time? I feel that there is a solution here that I'm ...

Is there an optimal method/algorithm to unecessary extra parens in BOOL or arithmetic operations

I have this, I built it programatically: (( cat1:bobo AND ( ( cat2:jojo ) OR ( cat2:coco ) ) )) For the sake of debugging, I am looking for a good method that would basically reduce it to least amount of parens needed: cat1:bobo AND ( cat2:jojo OR cat2:coco ) I'm on C#, but if you have a good technique you've seen, I will port it...

How can I speed up this SELECT CONCAT/GROUP BY query?

I'm working on selecting locations (city, state) out of a database. The problem is that the query is running a tad slow and I'm not sure how to speed it up. For example: SELECT CONCAT_WS(', ', city, state) as location, AVG(latitude), AVG(longitude) FROM places WHERE city='New York' AND state='NY' GROUP BY location There's going to ...

Why does this speed up my SQL query?

I learned a trick a while back from a DBA friend to speed up certain SQL queries. I remember him mentioning that it had something to do with how SQL Server compiles the query, and that the query path is forced to use the indexed value. Here is my original query (takes 20 seconds): select Part.Id as PartId, Location.Id as LocationId F...

MySQL - Find rows matching all rows from joined table AND string from other tables

Hi, this is a follow up from http://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table Thanks to this site the query runs perfectly. But now i had to extend the query for a search for artist and track. This has lead me to the following query: SELECT DISTINCT`t`.`id` FROM `trackwords` AS `tw` IN...

SQL Server index usage question

Assuming the following: /* drop index ix_vouchers_nacsz on dbo.vouchers; drop index ix_vouchers_nacsz2 on dbo.vouchers; create index ix_vouchers_nacsz on dbo.Vouchers( FirstName, LastName, Address, Address2, City, State, Zip, Email ); create index ix_vouchers_nacsz2 on dbo.Vouchers( Email, FirstName, LastName, Address, Address2,...

MySQL JOIN Query taking far too long to complete

I have a table called users with roughly 250,000 records in it. I have another table called staging with around 75,000 records in it. Staging only has one column, msisdn. I want to check to see how many rows in staging are not present in users. I have the following query, which I have tested on a small data subset, and it seems to work ...

how to optimize mysql query if i have too many OR operators in my query?

using PHP and MySQL i have grabbed an array of facebook user ids from facebook. Now i want to find the corresponding username in my application for this array. Clearly in my application the user table contains unique username and unique fb_uid values. my rudimentary understanding oof programming led me to 2 ways: 1) use a loop and r...

String matching in Oracle 10g where either side can have wildcards

Test case schema and data follow as: create table tmp ( vals varchar(8), mask varchar(8) ); insert into tmp values ('12345678',' '); insert into tmp values ('12_45678',' _ '); insert into tmp values ('12345678',' _ '); insert into tmp values ('92345678',' '); insert into tmp values ('92345678',' _ ')...

Can MYSQL queries dramaticly slow my website ?

Hey, I'm doing a website that requires me to use about 5 mysql "SELECT * FROM" queries on each site and I'd like to know if it can slow the download speed in any way. ...

Tree depth in SQL

Assuming that there's a table called tree_node, that has the primary key called id and has a nullable column called parent_id and the table embeds a tree structure (or a forest), how can one compute the depth of a node in the tree/forest in an efficient way in SQL? ...

can this query be improved??

Hi, Please have a look at the below SQL code. DECLARE @RET TABLE(OID BIGINT NOT NULL,rowid bigint identity); DECLARE @ResultTbl TABLE(OID BIGINT,sOID BIGINT,partkey bigint); DECLARE @PATOID as VARCHAR(4000) SET @PATIENTOID= '95,96,192,253,110,201,201,83,87,88,208,208,208,208' INSERT INTO @RET SELECT OID FROM dbo.FGETBIGINTLIST(@PATOI...

MySQL SELECT runs forever when join is changed

I have the following SQL code: select val.PersonNo, val.event_time, clg.number_dialed from vicidial_agent_log val join call_log clg on date_add('1970-01-01 02:00:00', interval clg.uniqueid second) = val.event_time order by val.event_time desc limit 100; which executes and returns rows in les...

Caching calculated values (Sums/Totals) in the Database

Consider the Following object model (->> indicates collection): Customer->Orders Orders->>OrderLineItems->Product{Price} The app is focused on processing orders, so most of the time tables showing all the orders that match certain criteria are used in the UI. 99% of the time i am only interested in displaying the Sum of LineTotals, no...

Php - optimizing multiple counter increments

On a very heavy traffic LAMP server I'm using a memory table to keep track of several data items as counters. It is implemented like this: $query = "INSERT INTO daily_info_mem SET di_num=1 ,di_type=9, di_date = current_date(), di_sid= $sid_int ,di_name='user_counter' ON DUPLICATE KEY UPDATE di_num=di_num+1"; The index sets unique di_t...

Preserving data integrity in Drupal:

I've happened to develop a module in Drupal and due to some seeming View limitations had to use custom SQL. This ran me into some problems with node revisions and I came to conclusion that in Drupal it's best to use its native methods for working with any data. Otherwise, data integrity problems may arise. And even with desire to optim...

I need a slow query on AdventureWorks (SQL 2005)

Hi everyone, As an exercise (read:interview question) in index optimisation, I need a query which is slow on the standard AdventureWorks database in SQL2005. All the queries I've tried take about 1 second and I would prefer to have a query which takes multiple seconds so that it can be optimised effectively. Can anyone here create such...

(Firebird) SQL: 100x constraints in WHERE clause makes query extremely slow

Hi, I'm using Firebird and created a table, called EVENTS. The columns are: id (INT) | name (VARCHAR) | category (INT) | website (VARCHAR) | lat (DOUBLE) | lon (DOUBLE) Now a user wants to search for events in a certain radius around him, but entered only two or three letters of his home city. So we've got - lets say - 200 possible c...

Slow SQL update could use some help.

An ex-coworker of mine wrote the following UPDATE as part of a data import script and it takes nearly 15 minutes to complete on a table of 92k rows. UPDATE table SET name = ( SELECT TOP 1 old_name FROM ( SELECT SUM(r) rev, number, name, intermediate_number, interm...