Tag Info

New answers tagged

1

It looks like you're joining returns on the date and the type of item only, creating a semi-cartesian product. E.g. If you have 100 orders for item A, and 100 returns of item A, on the same day, you'll return 100 x 100 = 10,000 rows, with every return joined to every order. Is this what you want? In terms of performance, the difference between your two ...


0

The problem is that the query has to retrieve 13.7 billion rows from FIELDDATA in aggregate. This is caused by two significant issues with your query. One will be easy to solve, one will be harder. The easy problem is with the index. As one commented suggested, you need to add a nonclustered index on FIELDDATA(ID, FIELDID). The clustered index scan is ...


1

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes. You have to create an R-tree index for your coordinates.


0

Your multipart index is covering 4 columns, but the only column that supports a seek is the first column. The trailing columns (y, x, z) can be used to find the rows, but this may not be optimal. If the column best for a search is y then make that the first column. Indexes are all about statistics which directs how the index can be used. So, perhaps this ...


0

Having all languages in one table is an overhead. Each time you will have to load the data of all languages and use part of (for one language), or you will add a condition to your query to load one language's data, which is also an overhead. In my opinion, having each language is a separate table is lighter. However, if the load on your website is low, and ...


8

The decisive factor is whether there is a unique constraint/index on b (key_column). If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column). If on the opposite, there is a unique constraint, the query is equivalent to ...


3

When you do a SELECT ... FROM some_view, the first thing Oracle will do is substitute some_view with SELECT statement that defines the view. So in your case, SELECT some_column FROM dummy is, both in logical and optimizer terms, the same as SELECT some_column FROM ( SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column) ); ...


0

Percona themselves have written a tool for this very purpose - it's called pt-index-usage and is to be found here. From the DESCRIPTION: This tool connects to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. When it is finished, it prints out a report on indexes that the queries didn’t ...


3

Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry): ALTER TABLE entry_log ADD INDEX card_no__date__last_entry__ix (card_no, date, last_entry) ; and then use this query: SELECT CONCAT(date, ' ', last_entry) AS LAST_LOG FROM entry_log WHERE card_no = LPAD('2948', 32, '0') ORDER ...


3

Cleanse your data before storing it. Otherwise, INDEXes may be useless. I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless. This SELECT would run a lot faster because of the index: SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE card_no = '2948' OK, you don't like the ...



Top 50 recent answers are included