Hot answers tagged explain
4
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. ... ...
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 ...
2
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 ...
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 ...
1
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 ...
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
