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...
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 ...
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...
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...
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...
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! ...
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...
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...
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)...
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...
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...
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...
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 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? ...
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...
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...
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 ...
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...
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...
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 ...