Is it possible to have a foreign key without an index in MySQL 5.6.34? I want that because I created a nullable column in 20M rows with a foreign key to another table. As this is a new feature, only the new rows MAY have this column filled with an…
I've been dropped in a system that was poorly designed. Now I'm doing DBA on their DB, and I have a lot of situation like the following (Pseudo-code):
Table t1{
c1;
c2;
c3;
c4;
key(c1);
key(c2);
key(c1,c2);
key(c1,c2,c3);}
Are the single column…
Suppose a SQL database of "values of series". Each Value belongs to a Series, and has a date:
@Entity
class Series { … }
Value {
Series series;
Date date;
…
}
Each value is unique for each combination of series and date, which is…
Here is what I have :
table content : cat_id product_id data1 data2 etc.
the categories are not unique obviously.
the product ids are unique.
2 queries :
1 -- SELECT * WHERE cat_id = :cat - must be as quick as possible
2 -- SELECT * WHERE…
form wikipedia:http://en.wikipedia.org/wiki/Index_(database)
Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. For example, an index could be created on upper(last_name), which would only…
I was trying to see the kind of performance gains column-store indexes can provide on a table. The table has about a 3.7 million rows, 11 columns and is stored as a heap (i.e without a primary key). I create a column-store index on the table and run…
I have a class Email that looks like :
public class Email
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string Subject { get; set; }
public string Body { get; set; }
public string…
I have a database table that has more than 50 Million record and to improve searching i had to create a non clustered indexes, and once i create one it takes 5 ~ 10 minutes to be created so i guess in the background it sorts the data according to…
I'm using a varchar[] column (varchar array) in Postgres 9.2 to store some tags. While retrieving rows by tags, I want the query to be case insensitive. However, I want to preserve the case to display in the UI (therefore I can't just store…
I have a PostgreSQL database table with text[] (array) columns defined on it. I'm using these columns to search for a specific record in the database in this way:
select obj from business
where ((('street' = ANY (address_line_1)
and 'a_city' =…
I've been trying to use an index in my query but to no avail. It's defined, but not used.
Here are my indexes
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment …
Below is the EXPLAIN for the query in question..
mysql> EXPLAIN SELECT orders_0.id, orders_0.created_at, orders_0.total_amount, orders_0.delivery_date, orders_0.customer_id, orders_0.items_summary, orders_0.site_id, orders_0.depot_id,…
I have a statement that selects the substring of a charindex as follows:
SELECT SUBSTRING(StringField, 5, CHARINDEX('ABC', StringField) - 5)...
WHERE
CHARINDEX('ABC', StringField) > 5
When I run the above statement in a select query, the results…
