query-optimization

MySql.Data.MySqlClient.MySqlException: Timeout expired

In recent times, a particular page in my web app throws the Exception Details: MySql.Data.MySqlClient.MySqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Though I use Ibtais as persistence layer, this error occurs. I have restarted the MySql service instanc...

Optimize query with two "in" clauses

I've trying to optimize a query on MySQL that takes around 15-20 seconds to run. My Data table has about 10M rows, and the query is trying to return 68,000 records which match 144 "run" fields and 35 "name" fields. Because the query is using two in clauses, my indexes don't seem to be terribly helpful. Here's the query: select * from...

SQL ROW_NUMBER() over performance problem

I have this SQL that works fine. Want the my filter to return the LATEST unique SessionGuids with the highest UserSessionSequenceID. Problem is performance sucks - even though I have good indexes. How can I rewrite this - to omit the ROW_NUMBER line? SELECT TOP(@resultCount) * FROM ( SELECT [UserSessionSequenceID] ...

MySQL Query: Long time spent in 'init' state

I'm performing an update on a MySQL table (myisam engine) that, according to the profiler, is spending an inordinate amount of time in the 'init' state: mysql> show profile for query 2; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | starting | 0.000057 | | ch...

How to Improve UPDATE query result time in Postgresql?

I have the following query running for 3hours+: UPDATE eop_201007 set coord_x = gi.x_etrs89, coord_y = gi.x_etrs89,gr_type = 4 from eop_201007 as eop, geoindex201001 as gi where eop.cp7=gi.cp7 AND eop.gr_type=0; eop table has 300k+ records, and gi table 100k+. The cp7 field is indexed in both tables, and this is taking way too much t...

MySQL: For ORDER BY calls (different fields), do I create an index for each field?

A quick question. I have a simple table with this structure: USERS_TABLE = {id}, name, number_field_1, number_field_2, number_field_3, number_field_4, number_field_5 Sorting is a major feature, one field at a time at the moment. Table can have up to 50 million records. Most selects are done using the "id" field, which is the primary k...

How do I get ID of an exiting table entry or insert a new one in case it doesn't exist efficiently?

I'm using SQLite to maintain a database which contains, among other things, a table with paths. The CREATE statement for the table looks like this CREATE TABLE path (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, UNIQUE(name)); I'm looking for a short way to say 'insert this given path into the ...

How can I optimise this query in MYSQL? What needs to be done

Please scroll down to the "25/08/2010 Update". I have a query which I have turned into a view. It runs very slowly because (as far as I understand) there are several issues with indexes on the joined tables. explain select * from view_ed_abc_print As you can see, I have table "a" using where, temporary and filesort with 4659 ro...

Need help optimizing a MySQL query - JOINs not using the correct indexes

I have this query below that I've rewritten a dozen different ways, yet I am unable to get it optimized and loaded in under 8 seconds. If I can get it to 2s, that would be good. 1s or less would be optimal. This query retrieves a list of books that are currently available for sale or trade, and performs a bit of filtering. This query...

MySQL query running really slow for large database

I am trying to extract duplicate rows from my database. I have a listings table and a listings_meta table which holds additional information. The listings_meta table has over 16,000 rows. When I run the query below it runs REALLY slow. I don't understand why. Is there something wrong with my code? SELECT l.id FROM listings AS l LEF...

How can I handle the time consuming SQL ?

We have a table with 6 million records, and then we have a SQL which need around 7 minutes to query the result. I think the SQL cannot be optimized any more. The query time causes our weblogic to throw the max stuck thread exception. Is there any recommendation for me to handle this problem ? Following is the query, but it's hard for ...

How to handle massive data query and control the time within 1 sec ?

I am thinking through a problem, if I get a table, and the data in it keep growing, thousand, million, billion .... One day, I think even a simple query it will need several seconds to run. So is there any means which we can use to control the time within 1 second or any reasonable time ? ...

possible ways of profiling an asp.net website in a production server?

I have an asp.net website up and running in my production server. I want to get the possible ways of profiling an asp.net website in a production server because my application is really slow? As i say slow i don't mean the delivery of static content but the database operations and my c# code? So any suggestion? ...

Indexing on BigInt column in MySQL

i have a table which has big int column used for storing the time stamp.The time stamp value which we are getting from our application is 13 digit number like 1280505757693.And there are many rows in this table right now probably more than half a million entries.Should i use Index on timestamp column or not ???? Any suggestions ? ...

SQL Strategies for parent - child tables

I could use some help determining the best (most performant / easily maintainable) strategy for retrieving parent-child objects from a SQL db. I inherited this code, and I've got relatively short deadline on this and I want to do as little foundational changes as possible. I'll have plenty of time to implement nhibernate or other ORM wi...

Mysql is it faster to use numbers in where clause compared to strings?

Lets say you have 4 types of assessments, Test, Quiz, MiniQuiz and FinalExam and we store records in the database like so studentid ----- assesType 1 test 2 quiz 3 quiz 4 quiz 5 miniquiz 6 miniquiz 7 final 8 final ...

how to stop mysqldump from recording as slow queries

Hi, Im recording slow queries longer than 2 seconds. That is running fine except, the daily backups also get recorded in slow log because 1) mysqldump calls select * from xyz (big table); and 2) when inserting the backup into a new db (yesterday's backup, sundays backup, etc). How to prevent mysqldump from logging slow queries? Is there...

Creating indexes for 'OR' operator in queries

I have some MySQL queries with conditions like where field1=val1 or field2=val2 and some like where fieldx=valx and fieldy=valy and (field1=val1 or field2=val2) How can I optimize these queries by creating indexes? My intuition is to create separate indexes for field1 and field2 for first query as it is an OR, so a composite index ...

Fetch only N rows at a time (MySQL)

I'm looking for a way to fetch all data from a huge table in smaller chunks. Please advise. ...

Optimize SQL query when GROUP BY and ORDER BY expressions are different?

Hello all From the Order By Optimization in Mysql documentation, I quote... In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: You have different ORDER BY and GROUP BY expressions. Is the...