Hi,
On 27-8-2011 1:28, Dave Dyer wrote:
Can you post the EXPLAIN EXTENDED output for your before and after queries?
also, have you recently run an ANALYZE TABLE on the tables?
What was the result of ANALYZE TABLE?
What is the engine of the tables involved?
// before
Used keys:
p2.NULL, g.player2, p1.uid
In your original post you wrote: "The according to "explain", the query
used gmtdate as an index, an excellent choice."
The explain output you posted later indicated that this is not the case
(anymore).
gmtdate isn't listed as possible index, so what has changed?
> It seems odd that the query optimizer would choose to scan a 3.5
> million entry table instead of a 20,000 entry table.
Let's see.
Before: 28653 * 41 * 1 rows to consider = 1.1 M rows
After: 15292 * 67 * 1 rows to consider = 1.0 M rows
Conclusion: the query optimizer didn't choose to scan an entire table.
In fact it found a way to have to look at 10% less rows.
For the final order by and limit it would be great to have a (partial)
index to work with.
It's true that planning indexes isn't always an exact science. Generally
speaking the goal is to construct both the query and the indexes in a
way that you rule out as many rows as possible early on in the process.
From your query it becomes evident that you want the latest fifty
matches between two players who both have the status is_robot "null".
Try to create indexes which cover as many of the columns which are
involved in the join, where and order parts, and look at the cardinality
of those indexes. This will determine how many records can be discarded
in each join and keeps the number of records MySQL has to scan as low as
possible.
Another way is a bit tricky, but can speed up queries a lot: you want
the 50 most recent records, so analyse the data and see if you can
predict how big your result set will be in a period of time. Let's
assume that there are always between 10 and 50 of such records per day.
If you want the top 50 it would be safe to limit the search for the last
10 to 20 days.
Of course this requires an index which includes gmtdate, but it can make
the result set before the limit a lot smaller.
--
Kind regards / met vriendelijke groet,
Jigal van Hemert.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
