I have a long-running SQL Server 2005 query that I have been hoping to optimize. When I look at the actual execution plan, it says a Clustered Index Seek has 66% of the cost. Execuation Plan Snippit: <RelOp AvgRowSize="31" EstimateCPU="0.0113754" EstimateIO="0.0609028" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10198.5" Log...
I have two tables: A and P. I want to get information out of all rows in A whose id is in a temporary table I created, tmp_ids. However, there is additional information about A in the P table, foo, and I want to get this info as well. I have the following query: SELECT A.H_id AS hid, A.id AS aid, P.foo, A.pos, A.size FROM ...
Hello, I have seen mysql queries with order by runs slow. Is there any specific way to optimize queries which use order by ? Queries without order by run very fast but with order by its always runs slow. if any one suggest any thing on this as general solutions. Thank You ...
I have a MySQL query that goes as follows SELECT count(`clicks`.`user_id`) as total, `users`.`fullname` FROM `users`, `clicks`, WHERE `users`.`id` = `clicks`.`user_id` GROUP BY `clicks`.`user_id` ORDER BY `total` desc LIMIT 0,20; I am running statistics on several button pressing type games. It has a us...
Working in MS SQL 2005 and I want to use a select statement within a wildcard where clause like so: SELECT text FROM table_1 WHERE ID LIKE '%SELECT ID FROM table_2%' I'm looking for product ids within a large body of text that is held in a DB. The SELECT statement in the wildcard clause will return 50+ rows. The statement above is o...
We are trying to run a query to get all unpaid invoices. When I run the query it hangs our whole system. I am just wondering if there is a way I can make this more efficent. SQL isn't my strong point. $query = "SELECT SQL_CALC_FOUND_ROWS i.order_id, o., acct., (SELECT SUM(items.item_qty) AS qty FROM items WHERE items.order_id = o.orde...
So, I've got a webapp that lets users submit code. The submissions are stored in the code table, and a couple of columns of that are Fulltext-Indexed. This is how I have been performing searches up until now. But, users can submit their submissions with as many tags as they like - and I'd like these to be included in the search too (but...
Hi, I've got a MySQL query that pulls lat longs from a database based on a criterion, tests whether these points are within a polygon, and returns the points that are within the polygon. Everything works fine. The problem is that the query takes approx. 20 seconds to return a result. Is there a way to optimize this query so that query ...
Hello ! I have a table with structure like that: table name: shop id_shop int(10) name varchar(200) latitude double longitude double And I'd like to calculate distance between given coordinates and coordinates saved in database. My current query: SELECT * FROM `shop` AS `s` WHERE ( ( 6371 ...
I have a MASTER object table and an ACTION_HISTORY table that chronicles when a user has interacted with the object. I need to know when the last action was for each object. I am stuck between two approaches. Here are some simplified test tables CREATE TABLE MasterTable (ID INT IDENTITY(1,1) NOT NULL, someData varchar(20) NOT NULL CO...
Background: entities tables currently has 14,111 records articles table currently has 5211 records I am trying to find all articles that are active (completed) and have the entity 'google' # Finding articles that have the entity google takes: # 4 ms SELECT `Article`.`id` FROM `articles_entities` AS `ArticlesEntity` LEFT JOIN `entities...
let's say I've got two queries: select top 20 idField, field1, field2 from table1 where idField in ( select idField from table1 where field3 like '...' union select idField from table2 where field4 like '...' ) order by sortfield1 select top 20 idField, field1, field2 from table1 where field3 like '...' or idfield ...
I want to write a query in a stored proc with many filters but I want to avoid dynamic SQL. Say my parameters are nullable (@filter1, @filter2, @filter3...). One way I might solve this is: SELECT col1, col2, col3 FROM table WHERE col1 = ISNULL(@filter1, col1) AND col2 = ISNULL(@filter2, col2) AND col3 = ISNULL(@filter3, col3) The re...
Problem In the following query, plr_stations is called twice: once to limit the WHERE clause; and once to count the number of results it returned. The code resembles: SELECT m.*, s.*, ( SELECT count(1) FROM climate.plr_stations('48.5146','-123.4447') ) AS count_stations FROM climate.station s, climate.measure...
I have two tables: one has a datetime column, the other has a date column. I want to join the two tables like this: SELECT t1.dt, t2.d FROM table1 t1, JOIN table2 t2 ON DATE(t1.dt) = t2.date But obviously, this kills the index on t1.dt. What's the best strategy to get around this? The simplest approach would be to add a ne...
I know of two ways to insert without duplication. The first is using a WHERE NOT EXISTS clause: INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s WHERE NOT EXISTS ( SELECT * FROM table_name AS T WHERE T.col1 = %s AND T.col2 = %s) the other is doing a LEFT JOIN: INSERT INTO table_name (col1, col2, col3) SELECT ...
When researching on how to do the classic "get POI in range" problem I've found that the most used algorithms are Haversine and if you need real accuracy then Vincenty's formula. I went the first one because high accuracy wasn't an issue. However, it got me thinking on something that hits me as odd, why is that I found no references to c...
I have several tables I need to query in order to get all the rows for a certain user. The tables basically look like this contact ======= id_contact PK firstName lastName ... contact_phone =============== id_contact_phone, PK id_contact, FK id_phone_type, FK phone ... phone_type ============ id_phone_type PK phone_type .... And the...
The following query works, but its very slow for 10 records (2 seconds). The profiling says its creating a tmp table, but I'm not sure why. Basically, I'm joining the current user, to the acl groups, to get all groups they are in, then joining the groups to the companies, to get all companies they are in, then joining the companies to t...
Is there anyway to prioritize or throttle a query at all in MySQL? I'm a DBA on a server that sees a lot of unoptimized queries come into the server and they just destroy the CPU. I'm looking at throttling certain users that hit on the database in poor fashion. Clarification: I realize that MySQL has facilities built in to limit numbe...