This link has been bookmarked by 26 people . It was first bookmarked on 14 Nov 2007, by someone privately.
-
25 Feb 14
-
Seven seconds is bad for a query this simple, as is examining 45,000 rows to return 2. Furthermore, this query was logged over 4,000 times in one day, meaning MySQL spent about 28,000 seconds a day on this query, or 7 hours.
-
the date column should not be type TEXT. What complicates the issue more is that the data in the date column is not normalized. That is, there are different representations of data (dates) in the column: Some dates are typical YYYY-MM-DD format and others are Unix timestamps. The first issue this creates is increased code complexity: Having to account for data that may be in different formats. Secondly, it's space inefficient. A date as TEXT will require 10 + 2 bytes or, times 45,000 rows, 540k.
-
As a DATE column type, 3 bytes or 135k.
-
How to index a date column as TEXT. Indexes on TEXT columns require a prefix length; that is, since TEXT columns are truly variable you have to tell MySQL how much of it you want to index. In this case, since the data is not normalized and everything counts down to the last second for dates and times, we would have to index 10 bytes, another 450k for the index (minus compression).
-
full table scan (type: ALL) plus a filesort (caused by ORDER BY).
-
no indexes MySQL can use (possible_keys: NULL).
-
No table scan and only one row examined. However, Extra doesn't say "Using index" even though our multi-column key includes both game_id and date.
-
MySQL won't retrieve the values from the index because "SELECT * " requires more values: It requires all 6 columns for every matching row but the index only has 2 columns.
-
In Case 1 only columns that were part of the key were included in the SELECT criteria, which is why things worked out better there than here.
-
The filesort is caused by date being TEXT.
-
And it goes to show that using efficient column types is important, unless you like filesorts.
-
but simply adding an index like we usually do isn't so easy anymore because there's multiple conditions.
-
We would like MySQL to use an index to find matching rows (avoid a table scan) but this won't be possible given the OR conditions.
-
For it to be possible, the conditions have to form a leftmost prefix of an index.
-
In other words, an index can't start with game_id OR other_id. Therefore, MySQL 4.x cannot use an index with this query. We're left with two options: Reduce the query even further or use MySQL 5.x.
-
-
23 Dec 11
-
27 Sep 10
-
28 Jul 10
-
09 Dec 09
-
02 Sep 09
-
26 Aug 09
-
29 Jun 09
-
02 Apr 09
-
16 Feb 09
-
12 Nov 08
-
13 Jun 08
-
12 Jun 08
-
16 Oct 07
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.