query-optimization

MySQL - creating a query optimizer add-on

Hi guys! My question quite a general one. I have to create a prototype of a query optimizer for mySQL, that works instead of standard one, or complements it. It doesn't have to be very complicated, at the beginning. But is there a way of turning off the standard optimizer, so that I can check the way how mine works? And what language is...

MySQL query optimisation help

Hi guys, hoping you can help me on the right track to start optimising my queries. I've never thought too much about optimisation before, but I have a few queries similar to the one below and want to start concentrating on improving their efficiency. An example of a query which I badly need to optimise is as follows: SELECT COUNT(*) AS ...

MySQL all unique values from 2 or more columns

I'm trying to select UNIQUE values from a non-uniformized table and show only the unique ones. SELECT DISTINCT "year" as sorter, year(data) as year, NULL as location FROM site WHERE tipo='projects' UNION SELECT DISTINCT "loc" as sorter, NULL as year, spare_1 as location FROM site WHERE tipo='projects' ORDER BY sorter ASC, year DESC...

MySQL: Optimizing SELECT from 6 huge identical tables with different data split up by timestamp

Hello, please I have the same problem as I found here http://stackoverflow.com/questions/409705/mysql-selecting-data-from-multiple-tables-all-with-same-structure-but-different , I have to select data from many MySQL tables with identical structure, but different data (split up into table_0, table_1, table_2 etc to table_5 to distribute...

Is there a better index to speed up this query?

The following query is using temporary and filesort. I'd like to avoid that if possible. SELECT lib_name, description, count(seq_id), floor(avg(size)) FROM libraries l JOIN sequence s ON (l.lib_id=s.lib_id) WHERE s.is_contig=0 and foreign_seqs=0 GROUP BY lib_name; The EXPLAIN says: id,select_type,table,type,possible_keys,key,key_len...

Optimize Oracle SQL with large 'IN' clause

Here I have a query like below: SELECT field FROM table WHERE value IN ('val1', 'val2', 'val3', ... 'valn') Let's say there are 2000 values inside the IN clause, the value doesn't exist in other table. Do you have any idea to speed up this operation? The question is open to accept any kind of methods.. Thanks! ...

MySQL - how to optimize query to count votes

Hi guys. Just after some opinions on the best way to achieve the following outcome: I would like to store in my MySQL database products which can be voted on by users (each vote is worth +1). I also want to be able to see how many times in total a user has voted. To my simple mind, the following table structure would be ideal: tab...

Increment counter or insert row in one statement, in SQLite

In SQLite, given this database schema CREATE TABLE observations ( src TEXT, dest TEXT, verb TEXT, occurrences INTEGER ); CREATE UNIQUE INDEX observations_index ON observations (src, dest, verb); whenever a new observation tuple (:src, :dest, :verb) comes in, I want to either increment the "occurrences" column for t...

sql tuning - with multiple joins

I recently read somewhere that one of ways of tuning sql query is - If it has too many joins then do one join with fewer tables and cache the results in a temporary table. Then do the rest of the query joining on that table. My question - How it will improve the performance, as you are joining same numbers of tables. (Only not together)...

Joins against views: performance issues

From a performance point of view is it the same doing this: select * from MYTABLEONE MT1 join MYVIEW MV on MT1.ID = MV.ID ( where the view is create view MYVIEW as select MT2.*, MT3.* from MYTABLETWO MT2 join MYTABLETHREE MT3 on MT2.OtherID = MT3.OtherID ) Or is it better to do this: select MT1.*, MT2.*, MT3.* from MYTABLEONE MT1...

speeding up mysql queries / mysql views in django

I use the following code to select popular news entries (by date) from the database: popular = Entry.objects.filter(type='A', is_public=True).extra(select = {'dpub': 'date(dt_published)'}).order_by('-dpub', '-views', '-dt_written', 'headline')[0:5] To compare the execution speeds of a normal query and this one I ran the following mysq...

SQL Server 2005: Delete Optimization

Hi, Is this the most efficient way to delete from DeletedProducts table where there are not references found in the ProductFileInfo table? Sample: DELETE FROM DeletedProducts WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo Or is NOT EXIST a better way to perform this. Note: ProductFileInfo has over 20 Million...

Indexing this mysql query

I am querying database with follwing query. This query takes 21 seconds to execute. I have check it by explain query. I have index on fields groupId , batchId separately. EXPLAIN SELECT message, sentOn, maskId, isDndCheck, contentType FROM sms_histories WHERE groupId = 1750 GROUP BY batchId ORDER BY batchId DESC LIMIT 0 , 1 I am gett...

Is there a faster way to perform this mysql query?

Is there a faster way to perform this most simple query in terms of server load? I don't think I've ever tried anything other than this method: $sql = 'SELECT thing FROM table WHERE id="' . $id . '" '; $res = mysql_query($sql); $row = mysql_fetch_array($res); $thing = $row[0]; Any way to improve this? ...

How can my SQL query be optimized?

Hi all, I have a problem with a quite slowish MYSQL query. I'm building an AJAX menu with PHP and performance is really an issue. The query takes about 0,5 sec to complete, and i don't know how to optimize it. The SQL query : SELECT M.nom_mat, M.id_mat, M.rang, SC.nom_sous_cat, SC.id_sous_cat, C.nom_cat,M.id_cat FROM besson_mat M LEF...

Select Query Optimization

I have select query that fetch record based on a condition Select * from Employee where EmpStatus=#EmpStatus# The EmpStatus in the DB for each employee would either 0 or 1. EmpID EmpName EmpStatus *********************** 1 Name1 0 2 Name2 0 3 Name4 1 4 Name5 1 When I pass EmpStatus as 1, I should get list co...

Slow MySQL UPDATE query

I have a mysql table containing 400,000 rows whenever I run a PHP script to update one row, it takes about 3-4 seconds to do it. How can I optimize the update query? UPDATE `weakvocab` SET `times` = times+1, `wrong` = wrong+1, `mtime` = 1284369979 WHERE `owner` = 'owner_name' AND `vocID` = 'ID_number' AND `type` = 'type_name'; This ...

SQL averaging multiple time periods of the same dataset

I have the following query: SELECT AVG(val) from floatTable WHERE tagindex IN(1,2,3,4) AND DateAndTime > '$first_of_year' It returns the average value for all the values measured for those four tags for the year to date. Since I'm already retrieving this data, how can I get the data since the first of the month, since the first of the...

Is it better to pull in an entire dataset if there's going to be a lot of ajax filtering done on the page?

I basically have this web page where you can narrow your search results by adding and removing certain filters on the left hand side. The content being pulled from the database is a list of properties, which can be filtered by: Type ( Apartment, Camp, Ranch, Fishing Camp, Hotel, Lodge ) Activities ( Camping, Kayaking, Hunting ) Locati...

Optimize JOIN SQL query with additional SELECT

Hello, I need a query which will select just one (GROUP BY phi.id_product) image for each product and this image have to be the one with the highest priority (inner SELECT with ORDER BY statement). The priority is stored in N:M relation table called product_has_image I've created a query, but it tooks about 3 seconds to execute and I ...