What is the fastest/shortest/one-liner (not possible :p) way to build a unique tree of elements from a tree where many of the elements are duplicated/missing in some nodes, given the tree has a defined set of nodes (which we'd use this algorithm to figure out so we don't have to manually do it). It could be XML/JSON(hash), or whatever. ...
I have a SQL that can be simplified to: SELECT * FROM table WHERE LOCATE( column, :keyword ) > 0 ORDER BY LOCATE( column, :keyword ) You can see there is a duplicate of "LOCATE( column, :keyword )". Is there a way to calculate it only once ? ...
In MySQL 5.0.75-0ubuntu10.2 I've got a fixed table layout like that: Table parent with an id Table parent2 with an id Table children1 with a parentId CREATE TABLE `Parent` ( `id` int(11) NOT NULL auto_increment, `name` varchar(200) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB CREATE TABLE `Parent2` ( `id` int(11) NOT NUL...
Query: select id, title from posts where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5 2,55) Explain plan: mysql> explain select id,title from posts where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5 2,55); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id...
Relational databases are frequently used to store graphs in all their many flavors (trees, directed graphs, undirected graphs, ...). Why then do none of the major DBMSs (Microsoft, MySql, Oracle, PostgreSQL, SqlLite, just to name a few in alphabetical order) include library support for treating relations as graphs? Some desirable featu...
Is there any way I can prohibit MySQL from performing a full table scan when the result was not found using indexes? For example this query: SELECT * FROM a WHERE (X BETWEEN a.B AND a.C) ORDER BY a.B DESC LIMIT 1; Is only efficient if X satisfies the condition and there is at least 1 row returned, but if the condition cannot be sat...
Nitpicker Question: I like to have a function returning a boolean to check if a table has an entry or not. And i need to call this a lot, so some optimizing is needed. Iues mysql for now, but should be fairly basic... So should i use select id from table where a=b limit 1; or select count(*) as cnt from table where a=b; or some...
I have a database with hundreds of awkwardly named tables in it (CG001T, GH066L, etc), and I have views on every one with its "friendly" name (the view "CUSTOMERS" is "SELECT * FROM GG120T", for example). I want to add "WITH SCHEMABINDING" to my views so that I can have some of the advantages associated with it, like being able to index ...
This is more of a generic SQL problem but I'm using Firebird 2.5 if anyone knows of a Firebird/Interbase specific optimization. First, the following is a simplified example schema to illustrate the issue I'm trying to solve: CREATE TABLE users ( id INTEGER PRIMARY KEY, name VARCHAR(16) ); CREATE TABLE data_set ( id INTEGER PR...
Hello, I have the following three tables: Venues, Events, Instance. Events have a Venue (Venues have many events), and Events have many Instances. For example, the Event "Terminator2" is a film that has a certain cinema as its Venue and will have many instances of that film (date times). I need to run a query, which gives me a lost ...
I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort? EXPLAIN SELECT SQL_NO_CACHE `groups`.* FROM `groups` INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id WHERE ((`memberships`.user_id = 1) AND (`memberships`.`status_code` = 1 A...
Hi All, I want to optimize this query as it is taking long to execute almost a second Here's the query: IF Exists( Select CustFirstName From Customers Where (CustFirstName = InputCustFirstName) OR (CustLastName= InputCustLastName) OR (Email = InputEmail) ); All these three columns have Unique index on it. and I have 765...
I have an extensive slow query log which was running for a few weeks. I would like to parse it, which would put the highest occurring queries at the top (with number of executions and average times of execution), and it goes from there in descending order. What tool/command can I use to accomplish that? ...
Good afternoon, I have two massive tables with about 100 million records each and I'm afraid I needed to perform an Inner Join between the two. Now, both tables are very simple; here's the description: BioEntity table: BioEntityId (int) Name (nvarchar 4000, although this is an overkill) TypeId (int) EGM table (an auxiliar table, in...
I have a database table of all zipcodes in the US that includes city,state,latitude & longitude for each zipcode. I also have a database table of points that each have a latitude & longitude associated with them. I'd like to be able to use 1 MySQL query to provide me with a list of all unique city/state combinations from the zipcodes tab...
Is there a way in MySQL to COUNT(*) from a table where if the number is greater than x, it will stop counting there? Basically, I only want to know if the number of records returned from a query is more or less than a particular number. If it's more than that number, I don't really care how many rows there are, if it's less, tell me the ...
What is the best way to detect Select n+1 problems if i am using linq to SQL, right now we are working on a project and it seem to be pretty slow to display some lists. What is the best method to detect this? ...
Here is a subset of my tables: orders: - order_id - customer_id order_products: - order_id - order_product_id (unique key) - canceled I want to select all orders (order_id) for a given customer(customer_id), where ALL of the products in the order are canceled, not just some of the products. Is there a more elegantly or efficient...
Hiya, Can anyone suggest tips or alterations to make this code cleaner and faster? This was the only way I could think of doing it on a Friday evening, but I'm sure there must be a more efficient way of doing it... I know regexs aren't efficient but I can't honestly see how else I can do this, especially if the Postcode data can be an...