Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re: Does DBI handle table alias?

by runrig (Abbot)
on Oct 07, 2004 at 19:47 UTC ( #397417=note: print w/replies, xml ) Need Help??

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
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.
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
    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.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://397417]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2022-06-29 09:15 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (96 votes). Check out past polls.