Ok, so i have one really monstrous MySQL table (900k records, 180 MB total), and i want to extract from subgroups records with higher date_updated and calculate weighted average in each group. The calculation runs for ~15 hours, and i have a strong feeling i'm doing it wrong. First, monstrous table layout: category element_id date_upd...
I'd like to select all rows from one table which match "one or more" rows in another table, in the most efficient way. SELECT identity.id FROM identity INNER JOIN task ON task.identityid=identity.id AND task.groupid IN (78, 122, 345, 12, 234, 778, 233, 123, 33) Currently if there are multiple matching tasks this returns the same i...
I have a table with two indexes, one of which is the faster covering index for a certain query. However, mySQL (5.1) is not choosing the correct index. I have looked at the explain for this query and done some speed tests and it makes a big difference if you force the key. Is there any way of examining how it picks the index and what cr...
Database Table1 Id Table2Id ... Table2 Id StartTime Duration //in hours Query select * from Table1 join Table2 on Table2Id = Table2.Id where starttime < :starttime and starttime + Duration/24 > :endtime This query is currently taking about 2 seconds to run which is too long. There is an index on the id columns and a fu...
I have a website that has user ranking as a central part, but the user count has grown to over 50,000 and it is putting a strain on the server to loop through all of those to update the rank every 5 minutes. Is there a better method that can be used to easily update the ranks at least every 5 minutes? It doesn't have to be with php, it...
I have a bit of a strange one happening. The first query I got from running a profiler on a C# ADO.NET application. What it is doing is not as interesting as the way the parameters are being passed - This query is taking 250+ seconds to complete, but when I modify it (Query 1) by changing the way the parameters are passed (see Query 2), ...
I got a query: SELECT a.nick,grp,count(*) FROM help_mails h JOIN accounts a ON h.helper=a.id WHERE closed=1 GROUP BY helper, grp, a.nick What is wrong with this join? When I made 2 queries: SELECT helper,grp,count(*) FROM help_mails h WHERE closed=1 GROUP BY helper, grp; SELECT nick FROM accounts WHERE id IN (...) It is 100 times fast...
I have a mysql (5.0.22) myisam table with roughly 300k records in it and I want to do a lat/lon distance search within a five mile radius. I have an index that covers the lat/lon fields and is fast (milisecond response) when I just select for lat/lon. But when I select for additional fields in the table is slows down horribly to 5-8 ...
I am facing a problem that running a stored procedure is taking too much resources which sometimes causes a time out on the server (especially when the CPU usage is more than 90%). Can anyone suggest what the best and quickest way is to spot the block which takes much resources, and also suggest a good way to solve it, please? I am us...
I know that SQLite does not enforce foreign keys natively, but that's not my primary concern. The question is: If I declare CREATE TABLE invoice ( invoiceID INTEGER PRIMARY KEY, clientID INTEGER REFERENCES client(clientID), ... ) will sqlite at least use the information that clientID is a foreign key to optimize queries and au...
I was wondering if it's faster to process data in MySQL or a server language like PHP or Python. I'm sure native functions like ORDER will be faster in MySQL due to indexing, caching, etc, but actually calculating the rank (including ties returning multiple entries as having the same rank): Sample SQL SELECT TORCH_ID, distance AS t...
I am using 'explain' to view why my query is hitting every single row in my database, and I do not understand why it is doing so. Could someone take a look and give me a hint what I am missing? My database is MyISAM, and I am using Mysql 5.1, PHP5 Here is my table: -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXIST...
Hi everyone! I need to know if there's a best way to optimize this kind of DB model : Here's my tables : [category] idCategory name [postCategory] (a post can be in more than 1 category) idCategory idPost [post] idPost post [comment] idComment idPost inputDate comment I'm going to have to display all the posts, from a specific ca...
does anyone know of a postgres view / function / tool that could report on the slowest and most often used slower queries? i think this would be so useful for all sysadmins. thanks! ...
I am writing a stored procedure to perform a dynamic search that spans 10+ database tables. With millions of records in each table and a dynamic set of search parameters*, I am having some trouble optimizing the procedure. Is there a "best practice" for building these kinds of queries? E.g. Use strings to build a dynamic query, use a h...
Hey, so let's say i'm building this contact management system. There is a USER table and a CONTACT_INFO table. For every USER, I can have zero or more CONTACT_INFO records. The way I've defined it, I've setup a foreign key in my CONTACT_INFO table to point to the relevant USER record. I would like do a search for all the USER reco...
I'm trying to offer a feature where I can show pages most viewed by friends. My friends table has 5.7M rows and the views table has 5.3M rows. At the moment I just want to run a query on these two tables and find the 20 most viewed page id's by a person's friend. Here's the query as I have it now: SELECT page_id FROM `views` INNER J...
Lets setup the question first. What I have is >4 tables: Customer, Address, Order, OrderItems. Each are mapped using Hibernate Annotations and accessed through a Spring DAO/Services layer. What I am trying to do is merge duplicate customers together. So all that really needs to happen is all orders and addresses associated with custom...
It appears my SQL isn't limiting results based on price. In my previous post, http://stackoverflow.com/questions/1039771/sql-help-me-optimize-my-sql, people indicated that I should use a LEFT OUTER JOIN. SELECT homes.home_id, address, city, state, zip, price, photo_id, photo_url_dir FROM homes LEFT OU...
This is the query. Im mostly interested if there is a better way to grab the stuff I use GROUP_CONCAT for, or if thats a fairy good way of grabbing this data. I then explode it, and put the ids/names into an array, and then use a for loop to echo them out. SELECT mov_id, mov_title, GROUP_CONCAT(DISTINCT categories.cat_nam...