Filter System Performance


Gen Table

id | catID | name

1 | 1 | Bill
2 | 8 | John
3 | 9 | Jason
4 | 10 | Bob

Services table

id | name

1 | Music
2 | Video Recorder
3 | Dancer
4 | Head Bobber
5 | Photography
6 | Photoshopping
7 | Video Effects

Gen_Services_Join table

id | GenID | Type

1 | 1 | 1,2,7 // Bill can do Music, Video, Video Effects
2 | 3 | 5,6 // John can do Photography and Photoshopping

Here’s the questions

If it better for me to do following ?

id | GenID | Type

1 | 1 | 1
2 | 1 | 2
3 | 1 | 7
4 | 3 | 5
5 | 3 | 6

Should i try to get all that processed on under 1 SQL Statement 1 Item could have either 1 service or 100 different services. So i didn’t want to try to Left Join, Join and do a bunch of SQL statement which could lead up to 50+ joins.

Or I can process a Basic SQL Statement create a class to convert everything into an array and do Array_Filter ? then i was thinking what if i have 1000 results, would that slow down the performance of the php if i’m doing array_filter on 1000 results?

Overall project goal is to create a search based on category and then filter the results based on services (i’m looking for someone that do Photography and Video Editing)

Option 2 Would look like this


Array
(
    [Me Make You Happy Long Time] => Array
        (
            [City] => Miami
            [State] => Florida
            [Category] => Music
            [Features] => Array
                (
                    [Head Bobber] => 1
                    [Disc Jockey] => 1
                    [Music Stuff] => 1
                )

        )

    [Brooklyn Music Editors] => Array
        (
            [City] => Brooklyn
            [State] => New York
            [Category] => Music
            [Features] => Array
                (
                    [Head Bobber] => 1
                    [Disc Jockey] => 1
                    [Sound Effects] => 1
                    [Music Stuff] => 1
                )

        )

    [Atlas Music Guy] => Array
        (
            [City] => Miami
            [State] => Florida
            [Category] => Video
            [Features] => Array
                (
                    [Video Editing] => 1
                    [Recording ] => 1
                    [Visual Effects] => 1
                    [VHS to DVD] => 1
                )

        )

)


You should use a lookup table to match people to services.

Not sure what the Cat_ID is for in your Gen table so Ive ignored it for simplicity

Gen_t

ID | Name
1 | John
2 | George
3 | Ringo
4 | Paul

Service_t

ID | Service
1 | Bass
2 | Lead
3 | Accoustic
4 | Piano
5 | Harmonica
6 | Drums

Gen_Ser_t

Gen_ID | Service_ID ------------------------note no need to have an autoinc ID on this table
1 | 2 -----------------------------------Make the table unique (ie no one record can match another)
1 | 4
1 | 3
2 | 2
3 | 6
3 | 4
4 | 1
etc | etc

I dont understand why you think you will have 50+ joins, youve only got 3 tables !!