Archive for March, 2011
From Stack Overflow:
I have a query where I have a custom developed UDF that is used to calculate whether or not certain points are within a polygon (first query in
UNION) or circular (second query in
UNION) shape.SELECT a.geo_boundary_id, … FROM geo_boundary_vertex a, … … GROUP BY a.geo_boundary_id UNION SELECT b.geo_boundary_id, … FROM geo_boundary b, … … GROUP BY b.geo_boundary_id
When I run an explain for the query I get
filesortfor both queries within the
Now, I can split the queries up and use the
ORDER BY NULLtrick to get rid of the
filesorthowever when I attempt to add that to the end of a
UNIONit doesn't work.
How do I get rid of the
GROUP BY also implies
ORDER BY on the same set of expressions in the same order. That's why it adds an additional
filesort operation to sort the resultset if it does not come out naturally sorted (say, from an index).
This is not always a desired behavior, and MySQL manual suggests adding
ORDER BY NULL to the queries where sorting is not required. This can improve performance of the queries.
Let's create a sample table and see:
Answering questions asked on the site.
I have a table which I will call
saleto protect the innocent:
Sale id product price amount date
I need to retrieve ultimate values of
datefor each product:SELECT product, MIN(price), MAX(price), MIN(amount), MAX(amount), MIN(date), MAX(date) FROM sale GROUP BY product
The query only returns about 100 records.
I have all these fields indexed (together with
product), but this still produces a full table scan over 3,000,000 records.
How do I speed up this query?
To retrieve the ultimate values of the fields, MySQL would just need to make a loose index scan over each index and take the max and min value of the field for each
However, the optimizer won't do when multiple indexes are involved. Instead, it will revert to a full scan.
There is a workaround for it. Let's create a sample table and see them: