Tagged Questions
2
votes
1answer
71 views
mysql: need help to optimize my query/table
I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
3
votes
3answers
130 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
4
votes
1answer
194 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
4
votes
1answer
84 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
3
votes
1answer
149 views
Query optimization with extreme statistic
I have a table:
mysql> describe table_1;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default ...
2
votes
2answers
92 views
Optimizing index creation
One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.
An example case would be:
table1
...
1
vote
2answers
138 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
5
votes
1answer
135 views
Walking a BTREE index as far as possible in MySQL
Suppose one has a column of words on which one builds a BTREE index:
CREATE TABLE myTable (
words VARCHAR(25),
INDEX USING BTREE (words)
);
LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
2
votes
1answer
608 views
MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?
I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
1
vote
1answer
139 views
Is a query with UNIX_TIMESTAMP using indexes in mysql?
If I use
.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()
in a SELECT, does this query still make use on an index, set on some_timestamp ?
5
votes
3answers
162 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
1
vote
2answers
243 views
Slow insert with MySQL full-text index
I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right?
...
4
votes
2answers
893 views
Why is this query not using my nonclustered index, and how can I make it?
As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default.
This query runs in about 2.5 seconds:
SELECT TOP 1000 * FROM ...
2
votes
2answers
100 views
Why isn't the following query using the `always` index?
This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query:
SELECT * FROM movies, ...
1
vote
1answer
536 views
At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?
I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.
I guess that it ...
2
votes
2answers
164 views
In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Concretely, is it worth it to add an index to a query that scans the table of a mysql database,
even if that index would only be used once a month?
For example, having a users a table and then ...
1
vote
1answer
130 views
Optimising a sql correlated subquery statement
I am updating a column on tableA with data from tableB like this:
update tableA
set bKey = select bKey from tableB B where B.colB = tableA.colB
bKey has a unique index on tableB.
Which is the ...
8
votes
4answers
921 views
How can I optimize this MySQL query further?
I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
2
votes
2answers
131 views
How to create multiple entries on index based on the fields of one row?
I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL.
The problem arises when you have fields working as tags or similar concept. Ex.: ...
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
