in reply to Does DBI handle table alias?

Just an idea. Are there any duplicate rows in the table? If not, how about, e.g.:
select column_1
from theTable
where column_2 in (123, 124)
group by column_1
having count(*) = 2

[download]
I am not sure an index would even help in this case, but I'd at least try one on column_2.

Update: As indicated by tall_man below, the effectiveness of the index will depend on the distribution of the data in column_2.

Replies are listed 'Best First'.
Re^2: Does DBI handle table alias?
by zebedee (Pilgrim) on Oct 08, 2004 at 01:12 UTC
    I don't know enough about the sorts of requests that might be made against your database, but

    1. if using MySQL 4+, have you turned on the query caching? and

    2. can you pre-populate or generate any reports off-line? i.e. if you know the reports that most users ask for, can you pre-build them at 1-6 a.m. (or whatever), when it does not matter if each query takes three minutes.

    Even running the queries will build the query cache for number 1.

    Finding the top 20% of the reports used 80% of the time means you can pre-build those or prime the cache.

    It all depends on your data and how dynamic the queries are.
[reply]
Re^2: Does DBI handle table alias?
by tall_man (Parson) on Oct 08, 2004 at 15:02 UTC
    Another idea that might be worth trying is a subquery: 
    select count(*) from theTable
where column1 in
  (select column1 from theTable
   where column2 = 123)
and column2 = 124

    [download]
    But the other suggestion would probably scale better for bigger groups of attributes.

    Update: By the way, practically everyone who answered is giving you the wrong impression about indexes. Sometimes sequential scans are faster than using indexes, for example if the attribute being tested is a match for a large percentage of the records. That's what the query optimizer is supposed to do on a query-by-query basis: decide whether to use the indexes or not, and in what sequence. In the subquery I suggested above, the optimizer might not be smart enough to do it, so you might have to create the query such that the smallest subset will be selected by the inner query.

[reply]
[d/l]

In Section Seekers of Perl Wisdom