There are three tables

link table

CREATE TABLE IF NOT EXISTS `tags_map` ( `ID` int(255) NOT NULL AUTO_INCREMENT, `OBJECT_ID` int(255) NOT NULL, `TAG_ID` int(255) NOT NULL, `OBJECT_TYPE` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `CHILD_OBJECT` varchar(255) COLLATE utf8_unicode_ci NOT NULL, KEY `ID` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=190 ;

tag list

 CREATE TABLE IF NOT EXISTS `tags_list` ( `ID` int(255) NOT NULL AUTO_INCREMENT, `TAG` varchar(255) COLLATE utf8_unicode_ci NOT NULL, UNIQUE KEY `ID` (`ID`), UNIQUE KEY `TAG` (`TAG`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=495 ;

users

 CREATE TABLE IF NOT EXISTS `user_list` ( `ID` int(255) NOT NULL AUTO_INCREMENT, `USER_ID` int(255) NOT NULL DEFAULT '0', `USER_CODE` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `FILMS` text COLLATE utf8_unicode_ci NOT NULL, `GAME` text COLLATE utf8_unicode_ci NOT NULL, `TRAVEL` text COLLATE utf8_unicode_ci NOT NULL, `MUSIC` text COLLATE utf8_unicode_ci NOT NULL, `HOBBI` text COLLATE utf8_unicode_ci NOT NULL, `SPORT` text COLLATE utf8_unicode_ci NOT NULL, `JOB` text COLLATE utf8_unicode_ci NOT NULL, `BOOK` text COLLATE utf8_unicode_ci NOT NULL, `NAME` text COLLATE utf8_unicode_ci NOT NULL, `SURNAME` text COLLATE utf8_unicode_ci NOT NULL, `EDIT_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `ID` (`ID`), UNIQUE KEY `USER_ID` (`USER_ID`,`EMAIL`), UNIQUE KEY `EMAIL` (`EMAIL`), KEY `FAMILY_STATUS` (`FAMILY_STATUS`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=13 ;

I want requests to display all user tags with an indication of what type they are (OBJECT_TYPE)

The request displays one entry.

 SELECT tl.*, tm.* FROM tags_map tm, user_list ul, tags_list tl WHERE tm.OBJECT_TYPE = '$object_type' AND tm.OBJECT_ID = $object_id GROUP BY tl.ID AND tm.ID

the query displays all the tags but marks that they are all of the MUSIC type, ie taking the data from the tags table_map continues to use them for each result

 SELECT tl.*, tm.* FROM tags_map tm, user_list ul, tags_list tl WHERE tm.OBJECT_TYPE = '$object_type' AND tm.OBJECT_ID = $object_id GROUP BY tl.ID

Help build the right query :)

    1 answer 1

    You did not link the tables in the query, you have a Cartesian product that you masked with the help of group by , and you are surprised that the data is not returned true. If I understand correctly, tags_map.object_id = user_list.id , right? Otherwise, change the connection to what you need:

     select ul.`name`, tl.`tag` from `tags_map` tm join `user_list` ul on tm.`object_id` = ul.`id` join `tags_list` tl on tm.`tag_id` = tl.`id` where tm.`object_id` = 1 and tm.`object_type` = 'not music'

    The request should be read as follows: For each tagmep, we take a description of the user, take a description of the tag and limit the selection by object id (user) and by tag type.

    Now by structure. You do not have primary keys in the tables. It is good that there are unique, but primary ones are necessary. The primary key is a record identifier, it is also unique and creates a clustered index (the records are physically ordered according to it), which is generally faster than a nonclustered index.

    Further. Indexes are required for the link table. Specifically for this request:

    • composite ( object_id , object_type ), weak link - string object_type.
    • considering varchar for object_type , it may be better to just index on object_id . it will be smaller and faster most likely.