query-optimization

mysql: Optimizing query w/ mixed-ascendency ORDER BY

I have a large table (~1M rows now, soon ~10M) that has two ranked columns (in addition to the regular data): avg_visited, a float 0-1 representing a %age popularity; higher is better alexa_rank, an integer 1-N giving an a priori ranking The a priori ranking is from external sources so can't be changed. Many rows have no popularity y...

SQL optimization - execution plan changes based on constraint value - Why?

I've got a table ItemValue full of data on a SQL 2005 Server running in 2000 compatibility mode that looks something like (it's a User-Defined values table): ID ItemCode FieldID Value -- ---------- ------- ------ 1 abc123 1 D 2 abc123 2 287.23 4 xyz789 1 A 5 xyz78...

How to get star query optimization in SQL Server 2005

I have a star schema but SQL Server 2005 always uses the clustered indexes to access a table. What parameters do I have to set to enable this optimization. According to http://blogs.msdn.com/sqlqueryprocessing/archive/2007/04/09/how-to-check-whether-the-final-query-plan-is-optimized-for-star-join.aspx and the DWH datasheet of SQL Serve...

Collapsing overlapping rows from multiple joined tables into as few rows as possible

Table_Design: ID "Alpha" "Beta" Table_Size: Design_ID Size "Alpha" S "Alpha" M "Alpha" L "Beta" S "Beta" L Table_Color: Design_Id Color "Alpha" "Black" "Alpha" "Blue" "Alpha" "Red" "Alpha" "Green" "Beta" "Orange" select D.ID, S.Size, C.Color from Table_Design D Left Outer Join Table_...

performance issue in a select query from a single table

Hi , I have a table as below dbo.UserLogs ------------------------------------- Id | UserId |Date | Name| P1 | Dirty ------------------------------------- There can be several records per userId[even in millions] I have clustered index on Date column and query this table very frequently in time ranges. The column 'Dirty' is non-n...

SQL Server 05, which is optimal, LIKE %<term>% or CONTAINS() for searching large column

I've got a function written by another developer which I am trying to modify for a slightly different use. It is used by a SP to check if a certain phrase exists in a text document stored in the DB, and returns 1 if the value is found or 0 if its not. This is the query: SELECT @mres=1 from documents where id=@DocumentID and contains(...

Why is doing a top(1) on an indexed column in SQL Server slow?

I'm puzzled by the following. I have a DB with around 10 million rows, and (among other indices) on 1 column (campaignid_int) is an index. Now I have 700k rows where the campaignid is indeed 3835 For all these rows, the connectionid is the same. I just want to find out this connectionid. use messaging_db; SELECT TOP (1) connect...

Optimize MySQL query to avoid unnecessary calls to user-defined function

I have a query that makes several calls within a SELECT statement to a user-defined function. The function (vfget) returns the value back from key=value pairs contained within a string. Is it possible for the query to just call the function once and store this in a variable so that it can be reused within the same query? Currently my q...

Using * in SELECT Query

I am currently porting an application written in MySQL3 and PHP4 to MySQL5 and PHP5. On analysis I found several SQL queries which uses "select * from tablename" even if only one column(field) is processed in PHP. The table has almost 60 columns and it has a primary key. In most cases, the only column used is id which is the primary ke...

sql: Group by x,y,z; return grouped by x,y with lowest f(z)

This is for http://cssfingerprint.com I collect timing stats about how fast the different methods I use perform on different browsers, etc., so that I can optimize the scraping speed. Separately, I have a report about what each method returns for a handful of URLs with known-correct values, so that I can tell which methods are bogus on ...

will a mysql query run slower if one of the tables involved has no index defined?

There's this already populated database which came from another dev. I'm not sure what went on in that dev's mind when he created the tables, but on one of our scripts there is this query involving 4 tables and it runs super slow. SELECT a.col_1, a.col_2, a.col_3, a.col_4, a.col_5, a.col_6, a.col_7 FROM a, b, c, ...

Why is the index on s not used for sort here?

Setup: mysql> create table test(id integer unsigned,s varchar(30)); Query OK, 0 rows affected (0.05 sec) mysql> insert into test(id,s) value(1,'s'); Query OK, 1 row affected (0.00 sec) mysql> insert into test(id,s) value(1,'tsr'); Query OK, 1 row affected (0.00 sec) mysql> insert into test(id,s) value(1,'ts3r'); Query OK, 1 row affec...

which one is a faster/better sql practice?

Suppose I have a 2 column table (id, flag) and id is sequential. I expect this table to contain a lot of records. I want to periodically select the first row not flagged and update it. Some of the records on the way may have already been flagged, so I want to skip them. Does it make more sense if I store the last id I flagged and use ...

What's the difference between !col and col=false in MySQL?

The two statements have totally different performance: mysql> explain select * from jobs where createIndexed=false; +----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows |...

Cannot sort a row of size 8130, which is greater than the allowable maximum of 8094

Hello All, SELECT DISTINCT tblJobReq.JobReqId , tblJobReq.JobStatusId , tblJobClass.JobClassId , tblJobClass.Title , tblJobReq.JobClassSubTitle , tblJobAnnouncement.JobClassDesc , tblJobAnnouncement.EndDate , blJobAnnouncement.AgencyMktgVer...

Is SQL DATEDIFF(year, ..., ...) an Expensive Computation?

I'm trying to optimize up some horrendously complicated SQL queries because it takes too long to finish. In my queries, I have dynamically created SQL statements with lots of the same functions, so I created a temporary table where each function is only called once instead of many, many times - this cut my execution time by 3/4. So my ...

Is there a way to optimize this mysql query...?

Hi Everyone... Say, I got these two tables.... Table 1 : Hotels hotel_id hotel_name 1 abc 2 xyz 3 efg Table 2 : Payments payment_id payment_date hotel_id total_amt comission p1 23-03-2010 1 100 10 p2 23...

set difference in SQL query

I'm trying to select records with a statement SELECT * FROM A WHERE LEFT(B, 5) IN (SELECT * FROM (SELECT LEFT(A.B,5), COUNT(DISTINCT A.C) c_count FROM A GROUP BY LEFT(B,5) ) p1 WHERE p1.c_count = 1 ) AND C IN (SELECT * FROM (SELECT A.C , COUNT(DISTINCT L...

Require help in Writing Query

The following image have been uploaded to show what I am trying to do and what I wanted out of it Can any one help me write the Query to get the results what I want Please check the following SELECT * FROM KPT WHERE PROPERTY_ID IN (SELECT PROPERTY_ID FROM khata_header WHERE DIV_ID...

Rails/mysql SUM distinct records - optimization

Hey. How would you optimize this SQL SELECT SUM(tmp.cost) FROM ( SELECT DISTINCT clients.id as client, countries.credits_cost AS cost FROM countries INNER JOIN clients ON clients.country_id = countries.id INNER JOIN clients_groups ON clients_groups.client_id=clients.id WHERE clients_groups.group_id IN (1,2,3,4,5,6,7,8,9) ...