CODEWITHSUNDEEP
mysqljoinlimit
DarkMaze
DarkMaze

Reputation: 263

MYSQL - Join three big tables

I suppose to execute this query in MySQL:

SELECT `id`, `state`, `city`, `company`, `manager`, `district`, `group`, `subgroup` FROM `data_table` WHERE `id` IN ( 
SELECT DISTINCT `dataID` FROM `tags_data` WHERE `table` = 'data_table' AND `tagID` IN ( 
    SELECT `id` FROM `tags` WHERE `tag` LIKE '%fruit%' 
) LIMIT 0, 20 );

But MySQL 5.5 doesn't support IN and LIMIT commands together!

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

so i have to use INNER JOIN or LEFT JOIN and merge these tables in order to use LIMIT

but my problem is these there table are BIG TABLES and this query may be used several times per every page! So cost of query's time is important for me!

I know this question may be repetitive but I have been confused.

Upvotes: 0

Views: 218

Answers (2)

DarkMaze
DarkMaze

Reputation: 263

thanx to M Khalid Junaid , i used query like this:

SELECT dataTable.`id`, dataTable.`state`, dataTable.`city`, dataTable.`company`, dataTable.`manager`, dataTable.`district`, dataTable.`group`, dataTable.`subgroup`
    FROM `data_table` AS dataTable
    JOIN `tags_data`
    ON dataTable.`id`=`tags_data`.`dataID`
    JOIN `tags`
    ON `tags_data`.`tagID`=`tags`.`id`
    WHERE `tags`.`tag` LIKE '%friut%' AND `tags_data`.`table`='data_table'
    LIMIT 0, 20;

and the performance was acceptable.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can get rid of your sub queries and use join subquery will run run for each row so it might cost you in place of them ,and then use proper indexes ,select only the columns you need

SELECT 
DISTINCT
  d.`id`,
  d.`state`,
  d.`city`,
  d.`company`,
  d.`manager`,
  d.`district`,
  d.`group`,
  d.`subgroup` 
FROM
  `data_table`  d
  JOIN `tags_data` td ON (d.id =td.dataID) 
  JOIN  `tags`  t ON (t.id =td.`tagID`)
  WHERE td.`table` = 'data_table' AND t.`tag` LIKE '%fruit%'
  LIMIT 0, 20

Indexes

ALTER TABLE `tags_data` ADD INDEX `indexname1` (`dataID`); 
ALTER TABLE `tags_data` ADD INDEX `indexname2` (`tagID`);

or you can join your tables with limit like so and also the where condition WHERE td.table= 'data_table' can be used in join query 

JOIN 
(select dataID from `tags_data` WHERE `table` = 'data_table' limit 0,20)
 td ON (d.id =td.dataID)

Upvotes: 2

Related Questions