MYSQL :: Approach A Heirarchy Tree

A Better Approach
Another future problem might be multiple attachments per post. (I'd end up with even more queries?)

This is what I'm using now:

1. Query the topics for the page.

SELECT * FROM ib_topics WHERE board_id = 123 ORDER BY topic_bumptime DESC, topic_id DESC LIMIT 0, 10

2. For each topic, obtain the first and last 3 posts. Join the attachment table, if the post has an attachment. (i.o.w. this query is used multiple times, each time with a different topic_id)

( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id ASC LIMIT 1)
UNION DISTINCT
( SELECT *, ib_posts.post_id AS post_id FROM ib_posts
LEFT JOIN ib_attachments ON post_attachment = "1" AND ib_posts.post_id = ib_attachments.post_id
WHERE topic_id = 1234 ORDER BY ib_posts.post_id DESC LIMIT 3)

Is there a better approach to this?

Approach In MySQL
What is the better approach in MySQL from a performance point of view?
I have approx 1000 stock market fond titles that I want to insert in
MySQL on daily basis and keep them in there for some years, I'll then
use the data for presentations, analysis,… the data I save is the
name, the date, the lowest, highest and last value and the volume.
The analysis will be run on all of them on daily/weekly basis.

implementation 1:
-----------------------
I create one (1) huge table with the fields date | name | lowest |
highest | last | volume

implementation 2:
-----------------------
I create one (1) huge table per year with the fields date | name |
lowest | highest | last | volume

implementation 3:
-----------------------
I create one table per name with the fields date lowest | highest |
last | volume

implementation 4:
-----------------------
I create tables with the fields date | name1 | name2 | … | name 249
i.e.:
table 1 for lowest values for titles 1 to 249
table 2 for lowest values for titles 250 to 499
table 3 for lowest values for titles 500 to 749
table 4 for lowest values for titles 750 to 1000
table 5 for highest values for titles 1 to 249
etc.

One Table Approach For All Publications
we are working on a web portal rewrite using a MVC platform (cakePHP)
so we are now having a discussion if having a one db table for all our publications (publications, news, articles, classified ads, events, magazines, etc) since all this publication holds the same data fields in 95% of the cases. For that 5% we will make an extra table publications_extras with some extra fields and this "prepend" table will be called only for that 5% of the cases.

We will cache as much queries *** possible so the db load will be very load for one day (lets say 500 select queries a day for the publications table and maybe 100-200 insert and update queries).

So Is this one-table-approach considered a bad practice or a good practice. Please tell me why and be as specific *** possible.

Oh, and one of the main reason to use this approach is also to have the M and C part of the MVC logic as unified *** possible. So in nearly all the cases for all type of the publications there will be only two for all of them (one model and one controller)

Best Approach To User Authentication?
So I'm setting up a php/mysql driven website on our company's intranet. I need to setup username's and passwords for different individuals because they all need access to different parts of the site.

Now I'm reading up on how to do user authentication. One way I am reading about, involves creating a username/password table in the database, and when the user attempts to login in some generic php/html form, the php logs into the database using a generic login/password, then checks to see if the user's login/password match up with what is in the table.

That's an interesting way to do it. But the thing that is odd to me is that you actually log into the database using a generic login/password (hidden in the php script) and you would simply have to control what the user has access to in your frontend code, based off their login.

MySQL has it's own user authentication system in place, where you create logins and passwords for the database and control what databases and tables they have access to and control what they can do with them. So why wouldn't you do it this way?

Sorry if this sounds confusing, but hopefully users experienced in this understand where I am coming from.

Question About Search Module Approach
I am doing a search module and right now, I am using a FULLTEXT to do it. So, if I search for 'car', it'll only return me words starting with car...

I read that FULLTEXT is the good choice when it is about searching, but, if I look at sitepoint search or flickr search, it seem that they do not use FULLTEXT but more query like this

SEARCH table WHERE field LIKE '%search%' with maybe some regex!

JOIN Or OBJECT ? Which Is Good Approach
I read this on some forums that I dont remember, there was quite a debate on this, an anyone please telle me which is a good approach to get information fron different tables for example I write code like this..

suppose I have 2 tables, user, and images

$r = mysql_query("SELECT * FROM users us, images im WHERE blah blah ");

now this works fine, I have created objects of thse tables to fetch information from 2 tables, but I have seen people using LEFT JOIN or any other join to accomplish this, whats the difference ? which is fast ? which puts more load on server ? any quick explanation ?

Yet Another Tree Problem
assume you have a table categories (f1, f2, f3, title) representing a tree structure like this.

1,0,0, T1
1,1,0, T1.1
1,1,1, T1.1.1
1,2,0, T1.2
1,2,1, T1.2.1

How to convert this structure into a a conventional id-parentid-system?

Category Tree
I need to create a query that will grab the top level category and list any sub-category it has under it and that in turn will have to list all the categories under that subcategory. I cannot wrap my head around the query I need for it though. Here is a mock up of my DB:

Id
boxName
boxLevel

So I could have something like:

Box 1
---->Sub Box 1
---->Sub Box 2
-------------->Sub Sub Box 1
----------------------------->Sub Sub Sub Box 1
---->Sub Box 3
Box 2
Box 3

Tree Structure
So I know you can't outright make a simple tree structure in SQL, but I
could sure use some help figuring out how get around it. I wanted to develop
a simple picture gallery where you put picture in categories, and each
category can have subcatergories, and each subcategory can have
subcategories...and so on. I want to try and keep the processing minimal
since this is just a simple picture gallery. Anyone have any suggestions?

Forum Tree
I am trying to find an elegant solution for a forum tree, but I don't know enough about MySql.

Let me try and explain what I try:

1. Someone writes a story (A)

2. Someone gives a reaction (A1)

3. Someone else gives a reaction (A2)

4. Someone reacts to A1 (A1a)

5. Someone reacts to A1a (A1aI)

etc. etc.

I would like the order on the page to be the logical one, ie:
A
  A1
     A1a
          A1aI
  A2
  A3
     A3a
  A4
etc. etc.

What fields would I have to add to be able to fetch the entire forum in the logical order with only one query? Do I add a 'Parent' field plus an index? Or other fields? What would the query look like?

Maybe you think I should work it out by myself, and you would be right there, but I have been breaking my head over this for quite some time already.

Tree Traversal
I am new to MySQL .I want to keep a tree structure as table in mysql and also want to add nodes,delete nodes and retrieve datas I need all these dynamically.I have some vague ideas about it.I don't want recursion method concept.

Tree Structure
I don't know if this is possible, without iterating through every returned record. I have a table of categories. Each category has an id and a parent_id. If the parent is set to 0, then the category is top level. I need to be able to return all of the sub-categories below a parent category, regardless of the level. So if the parent is 1, and 2,3,4 are children, and 5,6,7 are children of 4, then i need to return 1,2,3,4,5,6 and 7.
Is this possible?
Ultimately, i need to be able to return all the products within those categories.

Using B-tree Indexing
If I use a b-tree index on a memory table, then the index will work with column comparisons such as <= and >= and <>? I am just trying to make sure, because memory with hash indexing, only make use of the index when using = and <=>.

Familly Tree Schema
~ to create my familly tree in a database, the basic schema I'm designing is something like this, can you please let me know if this is ok, or are there any other ways to make it better?
table: persons (to hold person's personal info, profile..)
- id
- name
- email
- photo
- etc.
table: relatives
- id (unique index)
- person (id from table persons)
- relative (id from table persons)
- relationship (how does relative relate to person: father, mother, bro, sis, wife, son, daughter)

Quering A Heirachical Tree
I have a database where each user has a unique ID and a referrer ID.

If Mr. A refers Mr. B, Mr B. has Mr. A's ID in his referrer field.

It is easy enough to build a query to work out who has been referred by who, but what I want to do is find out who referred who down to five levels.

So, the second level would be, everyone who was referred by those who were referred by Mr A.

Handling TREE Structure
I need some advice on how to handle SELECT query for TREE like structured object, whereby each node on the tree can be having unlimited child nodes.

What is the best way to query a node with all of it's subordinate nodes ?

The simplified table structure will look like as follows:
nodeID | node_name | parentnodeID

Is using Recursive tehcnique recommended for this case ? (I'm afradi this will consume a lot of server resources by using this technique)

MySQL Or Directory Tree?
I'm working on a project where I have about 5 different data structures (objects) consisting of about 15-30 fields each. The size of each object is about 2-4 kB.
The maximum number of objects will be in the range 5000-100000 for each object.

I need possibility to search, add, delete, update and get these objects by ID, NAME, DATE and VERSION only. I have no need for the individual fields of the objects only complete objects as binary files.

When I work with the objects I need access to roughly 5-10% of the objects at a time.
Right now I have all these files in a directory tree under Windows XP with ID, NAME, DATE and VERSION included in the file name. I can do all I need but it becomes too slow sometimes.

Now to my question:
Can I benefit something from moving to a MySQL database when it comes to performance and speed and how should I set up the datbase to make it as fast as possible?

Tree Display Of Categories
I have a question which has been puzzling me for quite some time now, and
my SQL skills are not good enough to come up with a proper solution.

I have a product catalogue which should be able to display anything with a certain letter (A,B,C)
in it on a page. So it should display Categories and their products on that one page like so:

(All starting with say the letter 'C')

Category 1
- Product 1
- Product 2

Category 2
- Product 3
- Product 4

the page it should display this information on will also need
to have pagination (ie. back, [1][2][3], next). How on earth can I get 1 recordset that would contain that information so it can be paginated properly ?

I'm using PHP and MySQL 3.23.x (upgrading not possible at the moment).
If the above is not possible I might be able to settle for the option below.

(All starting with say the letter 'C')

Category 1
Category 2
- Product 1
- Product 2
- Product 3
- Product 4

CELKO'S NESTED TREE SET
Just made the move from using the adjacency list model (id, parent) to using the nested set model (id, lft, rgt).

Currently running MySQL 4.17

My goal is to create a virtual directory.

The question I have is:

Is it possible to limit the results of a SELECT to only return the next level in the tree.

Complex (tree Based) Db Design
Firstly, here is the data structure...

Account
User
Site
Notes
Note0
Menus
Menu0
MenuEntry0
MenuEntry1
MenuEntry2
Pages
Page0
Paragraph0
Image0
Paragraph1
etc...
As you can see, the data is essentially a tree structure. Hence I've decided to use an MPTT design to index the data.
Each type of item is stored in a different table, i.e. table_paragraphs, table_images, table_menus, table_menu_entries etc.
My problem is that I'm not sure as to the best way to link the tree nodes to the different tables. Should I include the object type in the tree nodes and use my server side language to extrapolate the query from that or... ?

Counting Elements Of A Tree-like Structure
I have a table (tblRows) that holds a tree structure of rows by using a parentid and sortorder like this:

id___parent___sortorder_______________resulting_tree____________index

______________________________________root
14____0________0______________________|-14________________________1
15____0________1______________________|-15________________________2
16____15_______0______________________|__|-16_____________________3
17____15_______1______________________|__|-17_____________________4
18____0________2______________________|-18________________________5
19____0________3______________________|-19________________________6
20____19_______0______________________|__|-20_____________________7
21____20_______0______________________|____|-21___________________8
22____0________4______________________|-22________________________9

at some point I would like to know the "row index" of a specific row without having to select all rows and counting. However, I can't figure out the SQL that does so - can anybody help me ?

I suppose I would have to select "all items placed above in the tree" and then just count these, but I cant see how to select that. I think I could do it with nested selects, but thats not an option as the system is currently running on a mysql 4.0 server.

Establishing A Relationship In A Family Tree
I have a table which describes every person in a family and another table that has 2 foreign keys from the main person table and an id of a relationship b/w them. i need a stored procedure to insert data into this table ,that can extract the primary id from the main table twice and the relation id from another type table. am stuck and need some direction regarding this.

Building From The Develepment Tree (windows)
I'm trying to build mysql from the development sources of mysql 4.1.,
retreived using bitkeeper under win2k using cygwin.

I have made "bk -r edit", and now I'm having a problem
running aclocal:

aclocal: configure.in: 450: macro `AM_PROG_AS' not found in library

When I make "automake --version" and "aclocal --version" it prints me
1.4-p5.

Does anyone use cygwin for this, any solutions and advices ?

Step By Step Approach To Manage Dual-master Set Up
I am thinking of having a dual master setup where two database servers
A and B are slaves to each other.

Could someone please point me to some documentation that details what
exactly I should do if A or B goes down?

Find Root Node In Tree Structure
Can anyone give me a hint, how this problem is best implemented:

I have a table of users (see below), where every user has one "superior user" (= parent node), this should be a fully unambigous tree structure. The root node can have whatever value you prefer, I suppose NULL would be good for a start. What I want to do is finding
the way from an arbitrary node in the tree.

Example:

Pre Order Tree Structure Reverse Path Lookup
Can this be done in one query?

We only have the titles of the categories to search by.

$titleX_left simply stands for the left_ value obtained from the previous query, same for the right_ value.

SELECT * FROM category WHERE title='Title1'
SELECT * FROM category WHERE title='Title2' AND left BETWEEN $title1_left AND $title1_right;
SELECT * FROM category WHERE title='Title3' AND left BETWEEN $title2_left AND $title2_right;

Keep in mind titles could be duplicated in sub trees.

So we could have:
->Cars
-->2door
->Trucks
-->2door

Thats why simply looking for ƈdoor' will not work (or in the query example above, 'Title3'). We must start at the highest level node and work our way down the tree because there could be "same names". There is an unlimited potential of levels here so if there is a way to do this with 1 query let me know.

Moving A Node In A Preorder Tree Traversal Table
Im going crazy here...how do I move a node in a modified preorder tree traversal table?

The database:
-id
-name
-lft
-rgt

SQL

LOCK TABLE sys_resources WRITE;
SELECT @myLeft := lft, @myRight := rgt FROM sys_resources WHERE id = $id;
SELECT @parentLeft := lft, @parentRight := rgt FROM sys_resources WHERE id = $parentId;
UPDATE sys_resources SET lft = lft + (@myLeft-@parentLeft), rgt = rgt + (@myRight-@parentRight) WHERE lft BETWEEN @parentLeft AND @parentRight;
UPDATE sys_resources SET lft = lft - (@myLeft-@parentLeft), rgt = rgt - (@myRight-@parentRight) WHERE lft BETWEEN @myLeft AND @myRight;
UNLOCK TABLES;
The $id and $parentId are correct.
The problem with this code is that the first UPDATE effects the secund ones WHERE.

Selecting All Records In A Parent/child Tree Structure
I have a tree menu and I am filtering it so that it shows only nodes in the tree that have products against them.

I have two tables:

1. Tree table Name: 'shopCategory'
Fields:
cat_pk
category_text
parent_pk
url

2. shop items Name: 'shopItems'

many fields but key field of TDCategoryID links to cat_pk in the shopCategory table

I have got as far as:

SELECT * from shopCategory WHERE cat_pk in (Select TDCategoryID from shopItems);

This gives me all the nodes that have items against it:

+--------+--------------------------+-----------+-------------------------------
| cat_pk | category_text | parent_pk | url
+--------+--------------------------+-----------+-------------------------------
| 89 | Food and drink | 1 | *****
| 93 | Vitamins and supplements | 89 | *****
| 94 | Wine | 89 | *****
| 100 | Other wine | 94 | *****
| 121 | Home and garden | 1 | *****
+--------+--------------------------+-----------+-------------------------------

What I need to do now is follow the parent/child relationship and return all the records to the recordset until parent_pk is '0' i.e. the top of the tree. THe problem is the length of the tree varies so it may be folder|items or folder|folder|items is this possible?