I have a website where visitors can leave comments. I want to add the ability to answer comments (i.e. nested comments). At first this query was fast but after I populated the table with the existing comments (about 30000) a simple query like: SELECT c.id, c2.id FROM (SELECT id FROM swb_comments WHERE pageId = 1411 ...
My members will have the ability to customise their profile page with X amount of widgets, each widget displays different data such as a list of music, list of people they are following etc. Several widgets include: - List of media they have uploaded - List of people they are following - List of people following them - Html/Text wid...
I am tracking clicks over three time periods: the past day, past week and past month. To do this, I have three tables: An hourly table, with columns link_id, two other attributes, and hour_1 to hour_24, together with a computed column giving the sum A weekday table, with columns click_id, two other attributes, and day_1 to day_7, toge...
This is my tables, the AUTO_INCREMENT shows the size of each: tbl_clientes: CREATE TABLE `tbl_clientes` ( `int_clientes_id_pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `str_clientes_documento` varchar(255) DEFAULT NULL, `str_clientes_nome_original` char(255) DEFAULT NULL, PRIMARY KEY (`int_clientes_id_pk`), UNIQUE KEY `st...
I run a server that is getting killed by the number of requests it is getting from one of our iPhone games for storing scores. By this I mean the server becomes unresponsive. I only really know enough MySQL/PHP to get by, so I am floundering in my attempts to fix this. I am sure its a problem that can be optimised because we have a dedi...
Hi, Im making a mysql table which will be holding my blog posts and some of their information. I want to be able to add comments the the blog posts and have them saved in mysql. My question is weather I should make a table called comments and have all the comments there have the blog post id, so I would only select the corresponding com...
I have an Access database that contains a table with information about parts we sort. This table has an autonumber ID field and a 110ID that links to another table with the part information. It also contains a sortDate, sortShift, sorted, scrapped, and repaired. I need to find how many parts have been sorted since the last defect (non...
I'm running a number of queries that merge constantly-changing data into a master table and one of the queries (below) seems to be running quite slowly. The set up is as follows: products table and products_temp table have identical structures. New data goes into the products_temp table, then I run queries similar to the one below to me...
I'm building a MySQL database which contains entries about special substrings of DNA in species of yeast. My table looks like this: +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | species | text | YES ...
Using RAND() in MySQL to get a single random row out of a huge table is very slow: SELECT quote FROM quotes ORDER BY RAND() LIMIT 1 Here is an article about this issue and why this is the case. Their solution is to use two queries: SELECT COUNT(*) AS cnt FROM quotes - Use result to generate a number between 0 and COUNT(*) SELECT q...
We have a simple SQL problem here. In a varchar column, we wanted to search for a string anywhere in the field. What is the best way to implement this for performance? Obviously an index is not going to help here, any other tricks? We are using MySQL and have about 3 million records. We need to execute many of these queries per second ...
I'm developing an application that tracks the user's current position and stores it into a SQLite database. Everything works fine, but now I have the problem when querying the database for a track with more than 1000 records it takes nearly 1.5 minutes. On my Desktop it just takes 1 second. I know it's a query with many subselects but I...
Hi guys, I've got some SQL that looks roughly like this: with InterestingObjects(ObjectID, OtherInformation, Whatever) as ( select X.ObjectID, Y.OtherInformation, Z.Whatever from X join Y join Z -- abbreviated for brevity ) -- ...long query follows, which uses InterestingObjects in several more CTEs, -- and then uses those CTE...
If I submit a query to SQL Server 2005 which contains a number of LEFT JOIN clauses where the table joined to is then never referenced, will the joins still happen or is SQL Server intelligent enough to discard them? Obviously it wouldn't be able to discard INNER JOINs [false assumption! see answers] as that would potentially change the...
I have a 400 line sql query which is throwing exception withing 30 seconds ORA-03113: end-of-file on communication channel Below are things to note: I have set the timeout as 10 mins There is one last condition when removed resolves this error. This error came only recently when I analyzed indexes. The troubling condition is ...
My situation is: Table member id firstname lastname company address data ( 5 fields ) contact data ( 2 fields ) etc Table member_profile member_id html ( something like <h2>firstname lastname</h2><h3>Company</h3><span>date_registration</span> ) date_activity chat_status Table news id member_id (fk to member_id in member_profile) title...
Can someone please explain why this works. Here is the scenerio. I have a stored proc and it starts to run slow. Then I pick a parameter and declare a variable to house its value and in the proc use the declared variable instead of the parameter. The proc will then speed up considerably. I think it has something to do with the cache...
I'm working on this game titled "Fortress", located at http://www.joemajewski.com/fortress. Anyways, it's one of those browser-based role-playing games where players build an army and upgrade their stats to get a high ranking on the leaderboard. Every 30 minutes a cron job is going to execute, which makes some updates. It loops through ...
I have a simple program which reads a bunch of things from the filesystem, filters the results , and prints them. This simple program implements a domain specific language to make selection easier. This DSL "compiles" down into an execution plan that looks like this (Input was C:\Windows\System32\* OR -md5"ABCDEFG" OR -tf): Index Succe...
Hello guys, i really hope that someone can help me with my problem. I would like to realize a match against query with multiple searchstrings. In the moment it looks like this: SELECT result.* FROM ( SELECT A.TITLE AS title MATCH(A.TITLE) AGAINST('$searchstring1' IN BOOLEAN MODE) AS REL, MATCH(A.TITLE) AGAI...