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,
2011/08/26 13:58 -0700, javad bakhshi
Thanks guys for the help. but my problem seems to stand unsolved.
Right, no arrays. Nothing is left but table. I used a temporary table, but note
that MySQL also does not let table be returned, or passed in. The table-name
will be *sigh* global.
--
The innocuous change was to add an index for is_robot which is true
for 6 out of 20,000 records and null for the rest.
My complaint/question/observation is not how to optimize the query
that went awry, but to be alarmed that a venerable and perfectly
serviceable query, written years ago and
Hi,
On 27-8-2011 22:52, Dave Dyer wrote:
The innocuous change was to add an index for is_robot which is true
for 6 out of 20,000 records and null for the rest.
Not useful to add an index for that. I also wonder why the value is null
(meaning: unknown, not certain) for almost all records.
Another approach to this is to create a concatenated string from the PKs of
the result set and then parse that within a stored procedure elsewhere in
your Front End (FE). For more information on this, visit
www.artfulsoftware.com and check out the Queries page.
Arthur
I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column? I try to design my tables such that every column is NOT
NULL. In practice this is not realistic, but I try to adhere to this
principle
It is a general rule that indexes for columns with low cardinality are not
worth it, often making queries more expensive than they would be without
said index. binary columns all suffer from this.
- michael dykman
On Sat, Aug 27, 2011 at 4:52 PM, Dave Dyer ddyer-my...@real-me.net wrote:
On Sat, Aug 27, 2011 at 17:33, Arthur Fuller fuller.art...@gmail.com wrote:
I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column? I try to design my tables such that every column is NOT
