The process of determining which indexes are useful and which are not.

learn more… | top users | synonyms

1
vote
1answer
80 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
1
vote
1answer
233 views

MySQL query optimization with aggregate ORDER BY

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items." The schema is as follows: ...
1
vote
1answer
101 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
0
votes
1answer
29 views

Indexes involved in intervals collision detection

Let's assume an hotel booking application wants to display all customers being present between two dates. Starting with a venue table : CREATE TABLE `venue` ( `id` INT NOT NULL AUTO_INCREMENT ...
0
votes
1answer
39 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
0
votes
1answer
144 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
0answers
181 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 ...
2
votes
0answers
62 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
1
vote
0answers
32 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
1
vote
0answers
47 views

Index Maintenance

I have a query thats performing badly and has a RID lookup. There are already quite a few indexes on the table and in the query stats I can see they are used although some less than others and all ...
1
vote
0answers
87 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 ...
1
vote
0answers
483 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
0
votes
0answers
27 views

Slow search times with functions despite index?

I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further. Given a have a few billion records with ...
0
votes
0answers
33 views

MySql - multi-column index using only null values from one column

I have a Innodb table with a index that looks at 3 columns. ColA is integer and always has a value. ColB is varchar and always has a value. ColC is datetime and sometimes has a value (NULL). My ...
0
votes
0answers
27 views

What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query

I have the following table: CREATE TABLE `wp_prize_draws_log_copy` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `entry_date` date NOT NULL, `entry_time` datetime ...
0
votes
0answers
49 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
0
votes
0answers
200 views

Table Valued Function Very Slow

I have a TVF that when called takes 10 secs to run. However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables. In the Execution ...
0
votes
0answers
105 views

Howto improve performance for these queries, why is my indexed view making it worse for most of them and how can I change that?

for the record, this is a rewrite of the following question (you do not have to read that because I include all important data here) Howto improve performance for queries against table without primary ...
0
votes
0answers
59 views

MySQL randomly refuses to use index

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...