Tag Info

Hot answers tagged

11

Solution for PostgreSQL 9.1 CREATE INDEX idx_time_limits_inversed ON time_limits (id_phi, start_date_time, end_date_time DESC); In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. I wrote more in this closely related answer on ...


9

Actually, the problem here is that this looks like a prefix index. I don't see the table definition in the question, but sub_part = 700? You haven't indexed the whole column, so the index can't be used for sorting and is not useful as a covering index, either. It could only be used to find the rows that "might" match a WHERE and the server layer (above the ...


6

PROBLEM #1 Look at the query select last_name from employees order by last_name; I don't see a meaningful WHERE clause, and neither does the MySQL Query Optimizer. There is no incentive to use an index. PROBLEM #2 Look at the query select last_name from employees force index(idx_last_name) order by last_name; You gave it an index, but the Query ...


6

You are getting the message Impossible WHERE noticed after reading const tables This is documented in the page you already linked. MySQL has read all const (and system) tables and notice that the WHERE clause is always false const tables are defined as The table has at most one matching row, which is read at the start of the query. ... ...


5

The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index. A single index over both columns in the WHERE clause: ALTER TABLE clients ADD KEY(source,added) -- adding this ALTER TABLE clients ADD KEY(added,source) -- or this ...will usually help you more ...


4

It made my head hurt, but just a bit... It appears each event ID is a specific date/time, such as movies (which it appears) one event is the movie at 2:30 on Day X, another event is the movie @ 4:45 on Day X. The same movie on Day Y @ 2:30 would be a different ID... That said, you are trying to breakdown counts that are box-office specific vs web-based ...


4

type: index means it's an index scan. That is, it's scanning through an entire index of that table. An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index. There are as many ...


4

MySQL currently doesn't support this (unlike nearly all other DBMS). I think this will be in 5.6 but I'm not entirely sure. I don't think there is any workaround for that (except upgrading to a DBMS that does support this)


3

In order to answer this question, you must understand what the rows column on explain means, and the difference between calculations based on statistics and post-execution statistics. When you run explain, the rows column will tell you, for each table access, how many rows will be examined by using the intended filter. There are two ways to calculate that: ...


2

Limit does have an impact on how many rows are selected/affected. 543 is just the number of rows which match the joins and the where clause.


2

You may find this surprising but that's the behavior of InnoDB. The InnoDB storage engine does cardinality approximation by walking a few levels down the nonleaf BTREE nodes and takes an educated guess. I wrote about this a long time ago based on mysqlperformanceblog.com Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics? Aug ...


2

There's a lot of good material in the comments, but I overlooked something obvious early on that actually makes the answer to the question fairly straightforward. What I missed was the repeated use of OR in the queries, which I originally mis-read as AND... and this makes a great deal of difference. You're asking for this: WHERE relDst='24794' OR ...


2

Erwin's answer is already comprehensive, however: Range types for timestamps are available in PostgreSQL 9.1 with the Temporal extension from Jeff Davis: https://github.com/jeff-davis/PostgreSQL-Temporal Note: has limited features (uses Timestamptz, and you can only have the '[)' style overlap afaik). Also, there's lots of other great reasons to upgrade to ...


2

ref evidently means a keyed lookup because the definition of ref in EXPLAIN is The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table. As for ALL, my guess is that refers to a full table scan because the same URL says A full table scan is done for each combination of rows from ...


2

The best index for this query is (u1_id, t) My initial guess was right, that you have indexes on (t) alone and on (u1_id) alone. You haven't told us the exact EXPLAIN output (which index is used), so the most probable explanation is that mysql is choosing to use one of these existing indexes or none at all (doing a full scan of the table), which yields the ...


2

The B, C and E columns will all have constant values as they are included in the WHERE clause, so there is no need to be in the GROUP BY and ORDER BY clauses. Therefore, the ORDER BY can be written as ORDER BY G,H,I Since you can always rearrange columns in the GROUP BY clause without altering the results, that can be written as GROUP BY G,H,I,F to be ...


2

I made a answer about because a comment will not support formating and RolandoMySQL DBA talked about gen_clust_index and innodb. And this is very important on a innodb based table. This goes further than normal DBA knowledge because you need to be able to analyse C code.. You should ALWAYS ALWAYS make a PRIMARY KEY or an UNIQUE KEY if you are using Innodb. ...


2

I suggest to use a trigram index provided by the additional module pg_trgm. Combine that with the length of the string to get a valid pre-selection. Drop the columns T4,T16 and T64 (faster in a single statement), and run VACUUM FULL or CLUSTER. Install pg_trgm. Details here: Full Text Search With PostgreSQL Create a GiST index on tl and length(tl) There ...


2

Yes, you can. This is called tracing the Oracle optimizer. Doing so creates a trace file in which the optimizer dumps all reasoning done when composing a plan. An example of generating a trace for a specific SQL can be found here how to trace optimizer for specific SQL system wide - 10053 trace event You might need a little time to read it, given the ...


1

Just make a simple join. Sub-queries does not provide the best result quite often EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating FROM levels AS l INNER JOIN users_favorites AS uf ON uf.level_id = l.id WHERE l.user_id = 2;


1

I resolved the problem by dropping and recreating IX1. (The reasoning behind that was to invalidate any cached plan still using it.) The query now returns 420 rows after 11 seconds. If anybody can give me a good theory on what happened I will accept it as a correct answer.


1

Join ordering is left to right. That is, in the above example a nested-loop join is performed by a full table scan of CUSTOMER followed by index look-ups into ORDERS and LINEITEM. (The meaning of ALL and ref are defined in the user manual for traditional EXPLAIN.) Note that Visual EXPLAIN is significantly updated in MySQL Workbench 6.1 including more ...


1

I created your scenario with COLLATE = "C", and both queries use a bitmap index scan index on other_names_lower_trgm_gin as expected. SQL Fiddle with a table of ~ 10k rows, Postgres 9.2.4, COLLATE = "C". There is probably something wrong in your setup that is not in your question. Run (takes some time for big tables and an exclusive lock!): VACUUM FULL ...


1

There are other parameters that can influence execution plan, like PGA size, number of CPUs, disk I/O speed, different sample sizes when you are gathering statistics, index modifications, different system load, different block sizes and so on and on. Just too many to check them all. You could try sqlTXplain or sql Health Check Oracle's tools to find the ...


1

No No Stop all other activity on the server, ensure that the cache is in a known state. Elaboration: The client/interface does not change the SQL that is sent to the server and processed. In the scheme of things network latency is going to be under 1 sec, and probably a lot lot less, so not a significant factor. The best way to understand what is ...


1

+1 to @junus for the explanations regarding EXPLAIN, the slow query log and rows examined. Regarding "How to optimize the query": Assuming there is an explicit foreign key relationship between the products and the members table, the join between them: ephpb2b_products off INNER JOIN ephpb2b_members mem ON off.uid = mem.id can be converted to ...


1

Impossible WHERE noticed after reading const tables in explain query? This error occurs due to invalid value being put on a column which are either primary key or unique key. Try with a correct value in the where clause .


1

You could try to create the multicolumn index in a different order: primary key(id_phi, start_date_time,end_date_time); I posted once a similar question also related to the ordering of indexes on a multicolumn index. The key is trying to use first the most restrictive conditions to reduce the search space. Edit: My mistake. Now I see that you already ...


1

The execution performance differs because: In the first case, when any inner table gets created, it is created without any index unless we specify a column name in the WHERE condition. So the tables temp_A and temp_B get created with the variable in the WHERE condition being taken as an index key. Though if we give index-id in the WHERE condition, it takes ...


1

There is a differences because: In first query doing the following: create temp table with data from A filtering out by CONDITION create temp table with data from B for each row in temp_A we traverse temp_B. In this case temp_A using FULL SCAN, temp_B access by index Second query For each row from A which fall under CONDITION we join with table B. ...



Only top voted, non community-wiki answers of a minimum length are eligible