The process of determining which indexes are useful and which are not.
3
votes
0answers
38 views
Indexing strategy for dynamic predicate
Assume SQL Server 2012 Standard edition.
My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).
The ...
0
votes
1answer
13 views
MYSQL - Using simple inventory number convention as Primary Key
I have an inventory with three numbered groups like so:
Group 1: 0-999
Group 2: e0-e999
Group 3: v0-v999
Can I simply make a one-column table to store this information and make that column the ...
0
votes
1answer
61 views
Am I wrong in table design or wrong in selected index when made the table?
I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
2
votes
0answers
37 views
What is Transaction Path Analysis?
I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my ...
1
vote
0answers
46 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
0
votes
1answer
54 views
Two identical MySQL tables, one not using indexes
I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.
Schema of one table
CREATE TABLE `measuresHistory` (
...
1
vote
1answer
67 views
Varchar index - will hashing value make it faster?
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
1
vote
1answer
25 views
Is it better to add an index and search via the index or add a unique constraint and let an insert fail?
I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:
mysql> DESCRIBE job_views;
...
0
votes
0answers
46 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
1
vote
1answer
44 views
MySQL: logging queries which would execute without using indexes
I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, ...
3
votes
0answers
50 views
How to count selectivity rows in PostgreSQL 8.2
I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index:
db=# explain
select count(*), messagetype, alias
from event
where
templateinfoid = 10 and ...
0
votes
1answer
33 views
Can I rely on the order of colums on an index generated by Database tuning advisor
I got the following recommendation on a table:
CREATE NONCLUSTERED INDEX
...
3
votes
1answer
117 views
What are the disadvantages if indexes are added on all columns?
I know, that it is not a good design to set too many indexes, but just for understanding the theory:
What exactly are the disadvantages if you add an index on all columns in a table in MySQL?
Are ...
3
votes
1answer
78 views
How can I determine what is using a particular resource (table, view, or function, etc) on my SQL Server?
I've been looking for tables that could use better indexing. I've created a stored procedure that provides a list of tables that have no indexes other than a primary key:
CREATE PROCEDURE ...
5
votes
1answer
243 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
