query-optimization

Optimal query to fetch a cumulative sum in MySQL

What is 'correct' query to fetch a cumulative sum in MySQL? I've a table where I keep information about files, one column list contains the size of the files in bytes. (the actual files are kept on disk somewhere) I would like to get the cumulative file size like this: +------------+---------+--------+----------------+ | fileInfoId | ...

MYSQL - Combining Two Results in One Query

I have a query I need to perform to show search results for a project. What needs to happen, I need to sort the results by the "horsesActiveDate" and this applies to all of them except for any ad with the adtypesID=7. Those results are sorted by date but they must always result after all other ads. So I will have all my ads in the res...

Optimising Lamda Linq to SQL query with OrderBy

I have the following lamda expression: IEnumerable<Order> query = _ordersRepository.GetAllByFilter( o => o.OrderStatus.OrderByDescending(os => os.Status.Date).First() .Status.StatusType.DisplayName != "Completed" || o.OrderStatus.OrderByDescending...

How to improve this mysql query in speed .

here it is SELECT tbl_rls . * , ( SELECT count( * ) FROM `comments` WHERE `post_id` = `tbl_rls`.`id` ) AS `t_comments` FROM `tbl_rls` WHERE 1 =1 AND `status` <> 'denied' AND ( `id` IN ( SELECT `rls_id` FROM `tbl_visitors_logs` WHERE `date` LIKE '2010-07-02%' AND `page_type` = 'post' GROUP BY `rls_id` ORDER BY count( * ) DESC ) ) AND ...

Why does this sql query do a key lookup?

I have a table User with a bunch of indexes. One of them is a unique index on the AccountIdentifier column. Since this is a unique index, why is a key lookup required in addition to the index seek? The index seek tooltip reports that only one record is returned. I've also tried converting the index to a "unique key" type. ...

Designing good PHP quiz script.

hi! I'm developing a quiz module for a school with php and mysql. The requirements are simple: 1) There are 10 categories(subjects). 2) Select 8 random questions from each category and display them one by one. 3) Get the students input. 4) Calculate the result and display it. Now, the problem that i face is how to make the script as...

Faster MySQL Queries?

At my work I have several tables with over 200,000 rows of data. I have to set-up some queries that look over 15,000+ at a time so sometimes I get this error: PHP Fatal error: Maximum execution time of 180 seconds exceeded So, how do I speed up faster queries? The query is like this: SELECT toemail, toname FROM email_sent W...

Getting rid of full index scan

Hi, The following query performs badly because of a full non-clustered index scan of 6.5 million records in P4FileReleases followed by a hash join. I'm looking for possible reasons the optimizer picks a scan over a seek. SELECT p4f.FileReleaseID FROM P4FileReleases p4f INNER JOIN AnalyzedFileView af ON p4f.FileRelease = ...

Slow query for update/delete sql statements

In mysql there are lot of slow queries, only related to update and delete statements. The tables have 2 index columns and not heavily indexed tables. Each table having 30K records on average. Please give your suggestions on how to overcome the slow queries related to update and delete queries. These kind of queries: DELETE FROM <table...

How to Optimize Queries in a Database - The Basics

It seems that all questions regarding this topic are very specific, and while I value specific examples, I'm interested in the basics of SQL optimization. I am very comfortable working in SQL, and have a background in hardware/low level software. What I want is the tools both tangible software, and a method to look at the mysql databas...

MYSQL select statement conditions

query: SELECT u.deviceID, u.userName, u.contactNo, u.rating FROM User u INNER JOIN TaxiQuery t ON u.deviceID = t.seat1 OR u.deviceID = t.seat2 OR u.deviceID = t.seat3 OR u.deviceID = t.seat4 WHERE t.queryID = 3; +--------------------------------------+---------...

Force a mysql SELECT query to not use cache

I am executing a mysql SELECT statement which takes 30 seconds to run the first time, but then just takes .2 all times after that. I thought clearing the query cache would resolve the problem (RESET QUERY CACHE), but it still takes .2 seconds after that. Only restarting the server reverts the query back to taking 30 seconds, but then it...

Optimizing queries for a database using SQL/XML. Suggested resources?

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....

how can i optimize this query taking 30 seconds for 1746 rows

query: SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID) FROM USER_ROLE A, USER B, ROLE C WHERE A.ROLE_ID = C.ROLE_ID AND C.GROUP_ID = 3 AND A.USER_ID = B.USER_ID AND B.TEMPLATE_IND = 'N' AND B.ONAP_PARTCODE IS NULL ...

Slow ORDER BY in large table.

Hi SQL experts, I have problem with ORDER BY clause. When I remove ORDER BY in following query, query is finished in 0.004 seconds. If I keep it, query is running very slow = 56 seconds. It is because MySQL is first grabbing all 500 000 records, then sorting and finally returing only first 18 records. How can I solve this big table ord...

Optimizing SQL query with checking special rules set

(First of all sorry for my English. I am not native speaker) I have a problem with selecting data. My problem is that I don't know how to optimize SELECT from table "component". As I understand – I cannot create any indexes which help Postgres make select based on set of rules from other table. I have following schema: Component can h...

mysql query slow after upgrade

I don't know what our Systems team did to mysql. But one of my jsp pages takes about 15 seconds to load. It took only 1 second before upgrade. There are only about 200 entries in related tables. And the page connects about 60 times to the database. It is weird that such small page has this issue. Other JSP pages that query mysql have ...

Optomizing a sql Count Query.

I am still fairly new to SQL so I wanted to know if I am doing this the most optimized way. SELECT DISTINCT ACCOUNTID, ACCOUNT_NAME (SELECT COUNT(*) FROM TICKET WHERE (ACCOUNTID = OPPORTUNITY.ACCOUNTID)) AS [Number Of Tickets], (SELECT COUNT(*) FROM TICKET WHERE (ACCOUNTID =...

Mysql query with Left Join is too very slow.

Query: select `r`.`id` as `id` from `tbl_rls` as `r` left join `tblc_comment_manager` as `cm` on `cm`.`rlsc_id` != `r`.`id` Both tables has 8k records but why it is too very slow taking 2-3 minutes and more sometime. OMG , this query makes mysql server down. Will get back to you peoples in a second :( All peoples those sug...

SQL Query Costing, aggregating a view is faster?

I have a table, Sheet1$ that contains 616 records. I have another table, Rates$ that contains 47880 records. Rates contains a response rate for a given record in the sheet for 90 days from a mailing date. Within all 90 days of a records Rates relation the total response is ALWAYS 1 (100%) Example: Sheet1$: Record 1, 1000 QTY, 5% R...