NYCPHP Meetup

[Glenn]

hello,

i'm new to the list.

i was reading some of the posts about database performance,
and have an idea to help speed up certain queries.

given a one to many relationship between
parent_table and child_table_a...

parent_table
-------------
parent_id (unique key) indexed
child_table_a_count
more_cols
...
-------------

child_table_a
-------------
parent_id
child_id
more_cols
...
--------------

the idea is to keep a record of how many children a parent record
has, then use limit, so the query doesn't have to search the entire
child table looking for rows that match parent_id. it will quit
when it's found the correct number of rows.

both tables are innodb

in the application, (or with a trigger?)
set up a transaction where;

whenever rows are added to or deleted from
child_table_a, child_table_a_count in parent_table
is updated to the number of child rows.

to look up child rows in child_table_a for parent_id,

1. query parent_table for child_table_a_count for parent_id.

2. construct the next query, using the value of
    child_table_a_count as the value for limit.

maybe this is way too much work, or maybe limit doesn't
actually stop searching the table when it reaches the
limit_num.

could this be useful?

thanks,
glenn