I have a particularly slow query due to the vast amount of information being joined together. However I needed to add a where clause in the shape of id in (select id from table). I want to know if there is any gain from the following, and more pressing, will it even give the desired results. select a.* from a where a.id in (select id...
In T-SQL what's faster? DELETE * FROM ... WHERE A IN (x,y,z) Or DELETE * FROM ... WHERE A = x OR A = y OR A = z In my case x, y and z are input parameters for the stored procedure. And I'm trying to get the performance of my DELETE and INSERT statements to the best of my abilities. ...
I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort? EXPLAIN SELECT SQL_NO_CACHE `groups`.* FROM `groups` INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id WHERE ((`memberships`.user_id = 1) AND (`memberships`.`status_code` = 1 A...
I've got an application that generates data for reports that look like: age < 30 | age >=30 | asian | hispanic ----------------------------------------------------------------- clients in prog A | | | ----------------------------------------------------------------- clients in p...
Setup: mysql> create table t(a integer unsigned,b integer unsigned); mysql> insert into t(a,b) values (1,2),(1,3),(2,4); mysql> create index i_t_a on t(a); mysql> create index i_t_b on t(b); mysql> explain select * from t where a=1 or b=4; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |...
select .. from ( Select ... from ... order by weight desc limit N ) order by rand() limit 1 The above needs to create a temporary table each time,which is not efficient,so it doesn't qualify. How to do it properly? ...
mysql> EXPLAIN SELECT * FROM urls ORDER BY RAND() LIMIT 1; +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------...
Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks. And for the sake of not being too vague, let's say we are using a mai...
This question is theoretical as well as practical. Any results indicating useful resources on optimizing queries will be appreciated. There is a large SQL database which stores a large amount of data stored in SQLXML fields. Querying the XML directly is not fast enough. I have looked at some MSDN articles on optimizing SQLXML (i.e....
I am creating some reports for an application to be used by various states. The database has the potential to be very large. I would like to know which way is the best way to get column totals. Currently I have SQL similar to the following: SELECT count(case when prg.prefix_id = 1 then iss.id end) + count(case when prg.prefix_...
I work on speed optimization of my application, and what I have found is that LINQ (or EF) is creating some strange SQL for me that works slow. Here is some code : SomeList.AddRange(_databaseView .Select(l=> new SomeViewModel { Date = l.Date, ...
hi all, i have this query in a table with about 100k records, it runs quite slow (3-4s), when I take out the group it's much faster (less than 0.5s). I'm quite at loss what to do to fix this : select msg.id, msg.thread_id, msg.senderid,msg.recipientid, from_user.username as from_name, to_user.username as to_name from msgtable as msg le...
How to optimize this? SELECT e.attr_id, e.sku, a.value FROM product_attr AS e, product_attr_text AS a WHERE e.attr_id = a.attr_id AND value IN ( SELECT value FROM product_attr_text WHERE attribute_id = ( SELECT attribute_id FROM eav_attr WHERE attribute_code = 'similar_prod_id' ) AND val...