In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

learn more… | top users | synonyms

5
votes
3answers
2k views

MySQL Query Optimization : Indexing and Pagination

I'm adding pagination to my 'latest news' php script and running in to an issue. SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit I ...
5
votes
1answer
3k views

my.cnf values not applying - Optimizing MySQL in CentOS LAMP

I have checked for other occurences of my.cnf and I have found only one occurrence of /etc/my.cnf. Then, I went ahead to fix in my values, e.g. query_cache_size etc. After saving and literally ...
1
vote
3answers
197 views

Query optimization

In another question they told me that the speed of a DBMS largely depends on the optimizations of your queries. Does anybody know good documents about this? At school I only learned the basics. For ...
3
votes
2answers
427 views

How do I calculate working business minutes?

I'm working on business inteligence reports and one of my task is to create reports for time tracking. I have to report on business minutes (10 hours per day) within business week (monday to ...
6
votes
1answer
5k views

Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server

I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server. I checked this once and found that after this is run, ...
4
votes
1answer
661 views

Speeding SQL queries for a large(?) WordPress database?

I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here). The plugin is great ...
6
votes
2answers
261 views

How should I Optimize storage for this table?

I'm using MySql to store a basic table of this format: id int(11) //Auto-Incrementing ID data varchar(5120) //Random input data, compressed by a program, not mysql. ...
8
votes
4answers
2k 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, ...
4
votes
4answers
347 views

Do cross-joins get optimised by the client library?

Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ. I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
3
votes
2answers
401 views

What are the key points to avoid table locks?

MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software ...
4
votes
2answers
1k views

SQL Server Wildcardless LIKE Optimization

In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query. If I was to have all queries generated as like clauses, such as: ...
6
votes
3answers
6k views

Avoiding temporary tables while sorting by a column in a different table

So I have this MySQL InnoDB query here: SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt FROM stories s LEFT JOIN votes v ON s.vid = v.vid WHERE s.lv = 1 AND s.status = 1 GROUP BY s.vid ...
3
votes
2answers
2k views

DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle

In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table ...
0
votes
2answers
2k views

How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?

SQL Server DELETE only marks as DELETED though leaves as data in the data pages. Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
9
votes
5answers
4k 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
2answers
763 views

Fetch first 9 records and make one more record which will be as “Others” in MySQL

I am fetching records from one table with count of one field with other field as name. I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
5
votes
4answers
4k 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 ...
6
votes
2answers
124 views

Is there a good “rule of thumb” for translating EXPLAIN cost to (wall-clock) runtime?

From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have ...
4
votes
2answers
171 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.: ...
66
votes
6answers
35k 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 ...