I have a query I turned into a view that works OK. But the site_phrase sp table seems to be not using a column and goes through all the records in the table. Why is that? Here is the query: EXPLAIN SELECT `p`.`id` AS `id`, `p`.`text` AS `phrase`, `p`.`ignored` AS `igno...
I have a large table with millions of records. Table `price` ------------ id product site value The table is brand new, and there are no indexes created. I then issued a request for new index creation with the following query: CREATE INDEX ix_price_site_product_value_id ON price (site, product, value, id); This took long long t...
So I started off with this query: SELECT * FROM TABLE1 WHERE hash IN (SELECT id FROM temptable); It took forever, so I ran an explain: mysql> explain SELECT * FROM TABLE1 WHERE hash IN (SELECT id FROM temptable); +----+--------------------+-----------------+------+---------------+------+---------+------+------------+-------------+ | ...
I am trying to build a new table such that the values in the existing table are NOT contained (but obviously the following checks for contained) in another table. Following is my table structure: mysql> explain t1; +-----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default...
I have three columns in my table. +-----------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------------+------+-----+---------+-------+ | hash | mediumint(8) unsigned | NO | PRI | 0 | | | nums | int(10) unsi...
I just created a couple of queries that bring the same data but in a different. the first one uses a sub query and the second one uses a self join strategy. checking the documentation, i found the ANALYZE and EXPLAIN commands, Now i'm trying to understand which query is better. this is the result of EXPLAIN ANALYZE for each query. Hope s...
I'm trying to speed up a query which I currently have as: SELECT * FROM `events` WHERE (field1 = 'some string' or field1 = 'some string') and is_current = true GROUP BY event_id ORDER BY pub_date this takes roughly 30seconds. field1 is a varchar(150) I'm currently indexing field1, is_current, event_id, pub_data charity, pu...
I have large MySQL tables with hundreds of thousands of rows. I need to write a query on a customers table which gets the count of when customers will be available to contact again. eg. SELECT 'This week', COUNT(*) FROM customers WHERE sales_person_id = 1 AND DATEDIFF(NOW(), available_date) < 7 UNION SELECT 'Next week', COUNT(*) ...
I'm getting an index scan on a join with a unique column; it claims to be examining a large number of rows even when it's looking up just one row. This is the query: select t.id, t.twitter_id, t.screen_name, t.text from tweets t inner join twitter_handle th on th.handle = t.screen_na...
Are MySQL operations multithreaded? Specifically, on running a select, does the select (or join) algorithm spawn multiple threads to run together? Would being multi-threaded prevent being able to support a lot of concurrent users? ...
I am currently using the following query to get some numbers: SELECT gid, count(gid), (SELECT cou FROM size WHERE gid = infor.gid) FROM infor WHERE id==4325 GROUP BY gid; The output I am getting at my current stage is the following: +----------+-----------------+---------------------------------------------------------------...
Hi All. I'm designing an application that will use Oracle, and we have this department hierarchy that we need to map in our database. Some thing that looks like this (I'm pretty sure you all know what I'm talking about but I'll include a piece of the ERD just in case): So it will have data stored that looks like this: [1 | 0] [2 | ...
Hello, I am trying to copied records from one table to another as fast as possible. Currently I have a simple cursor loop similiar to this: FOR rec IN source_cursor LOOP INSERT INTO destination (a, b) VALUES (rec.a, rec.b) END LOOP; I want to speed it up to be super fast so am trying some BULK operations (a BULK FETCH, then a FOR...
Hi all, new Stacker here! From a client I receive SQL queries that contain all sorts of redundant sub-SELECTs, generated by a builder in MS Access. I convert the SQL to Postgres, and it runs - but I know it's hopelessly inefficient. So, I set about optimising the query, and produce iterative improvements - and to determine whether each ...
To perform transformations in my database, I frequently use a chained set of views. Within the views will be common table expressions. For example I would have the following: CREATE VIEW TransformationStep1 AS WITH Transformation1A AS ( SELECT Field1, Field2, Field3, Bla(Field1) AS Calc FROM Table1 ), Transformation1...
Let's say I have the following association: Club has_many users User has_many guns Gun has_many bullets Club: Moe, Larry, Curly Moe: 2 guns gun 1 has 100 bullets gun 2 has 20 bullets Larry: 1 gun gun 1 has 40 bullets Curly: 2 guns gun 1 has 20 bullets gun 2 has 10 bullets Now, I want to find out how many bullets in the CLUB. It'...
I have a table which keeps parent-child-relations between items. Those can be changed over time, and it is necessary to keep a complete history so that I can query how the relations were at any time. The table is something like this (I removed some columns and the primary key etc. to reduce noise): CREATE TABLE [tblRelation]( [dtCr...
I have a table PAYMENTS in MySql database: CREATE TABLE `PAYMENTS` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `USER_ID` BIGINT(20) NOT NULL, `CATEGORY_ID` BIGINT(20) NOT NULL, `AMOUNT` DOUBLE NULL DEFAULT NULL, PRIMARY KEY (`ID`), INDEX `PAYMENT_INDEX1` (`USER_ID`), INDEX `PAYMENT_INDEX2` (`CATEGORY_ID`), ...
I have a stored procedure that uses a FAST_FORWARD cursor to chronologically loop over a set of ~300k records and assigns them to declaration sets based on the state of a lot of running variables and flags, partly implemented as table variables. I have given a lot of thought on how to do this set-based and I just can't do it. So I am stu...
I am creating some reports for an application to be used by various states. The database has the potential to be very large. I would like to know which way is the best way to get column totals. Currently I have SQL similar to the following: SELECT count(case when prg.prefix_id = 1 then iss.id end) + count(case when prg.prefix_...