Bug #13187 query optimiser problem

• Submitted: 14 Sep 2005 15:58
• Modified: 14 Oct 2005 17:42
• Reporter: Pete Trevellick
• Status: No Feedback
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.12
• OS: Linux (Linux Gentoo AMD32)
• CPU Architecture: Any

[14 Sep 2005 15:58] Pete Trevellick

Description:
For a simple table, eg. create table xyz(int x, int y, int z) the time taken for the query

select * from xyz where (x in a,b,c...) and (y in a,b,c..)

seems to scale as the square of the a,b,c list length even is x y and z are all keyed. For example, with a list length of 1,000 the query already takes over 10 seconds on an AMD Sempron 3100. For lists of 5,000 and above the mysqld often crashes with out of memory errors.

My current inelegant work-around is just to use

select * from xyz where (x in a,b,c...)

and to filter the results for y myself

How to repeat:
create the table as indicated, populate it with a few thousand random x,y,z triples (making sure that some of the values used in x are also in y) then try the query

select * from xyz where (x in a,b,c...) and (y in a,b,c..)

with increasingly long a,b,c list lengths

[14 Sep 2005 17:42] Hartmut Holzgraefe

can you please add real create statements and queries and add EXPLAIN output for these?

[14 Oct 2005 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".