Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table structure as follows:

    --
-- Table structure for table `emails`
--

CREATE TABLE IF NOT EXISTS `emails` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `sent` smallint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5062 ;

-- --------------------------------------------------------

--
-- Table structure for table `ips`
--

CREATE TABLE IF NOT EXISTS `ips` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7534 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_items`
--

CREATE TABLE IF NOT EXISTS `user_items` (
  `ID` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Allows sorting by last added..',
  `name` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `folder` int(10) NOT NULL,
  `version` int(5) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `name` (`name`),
  KEY `folder` (`folder`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10431 ;

-- --------------------------------------------------------

--
-- Table structure for table `data`
--

CREATE TABLE IF NOT EXISTS `data` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `inserted` varchar(255) NOT NULL,
  `version` int(5) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `inserted` (`inserted`),
  KEY `version` (`version`),
  KEY `name_version` (`name`,`version`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7207 ;

-- --------------------------------------------------------

--
-- Table structure for table `data_emails`
--

CREATE TABLE IF NOT EXISTS `data_emails` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `email_id` int(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  `version` int(5) NOT NULL,
  `time` int(255) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `version` (`version`),
  KEY `name_version` (`name`,`version`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9849 ;

-- --------------------------------------------------------

--
-- Table structure for table `data_ips`
--

CREATE TABLE IF NOT EXISTS `data_ips` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `ns_id` int(5) NOT NULL,
  `name` varchar(255) NOT NULL,
  `version` int(5) NOT NULL,
  `time` int(255) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `version` (`version`),
  KEY `name_version` (`name`,`version`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17988 ;

What i need to achieve is as follows. I need to get each user_item and get the data, emails, and ips associated with it. user_items links to data, data_emails, and data_ips by the columns 'name' and version.

data_emails and data_nameservers link to emails and ips respectively with email_id/ip_id = ID

Because multiple joins result in row multiplication I have had to use nested subqueries. Because a user can have multiple emails and ips associated with an item I have used group_concat to group all that particular row.. I then explode this column in my PHP - this in itself seems inefficient, but I see no other way of doing it?

Now I am appreciative that I could speed this up with proper indexes (which I am getting my head fully around atm), but I am doubtful that there would be decent speed improvements. Others have likewise been doubtful.

Without nested subqueries and group_concat noone on the Internet seems to be able to propose another way of doing this..

I ideally want to be able to do this in a SQL query as I want to integrate search functionality whereby a user for example can search for items he has which have a given email associated with them. This would be incredibly complex/resource intensive if the tables are not joined.

Does ANYONE have any proposals as to how a SQL join such as that described could be implemented without huge load times?

Thanks

EDIT

Requested that I add my current query and explain what I want better.

Essentially i want each row to be returned from user_items. Alongside this I want to return the item data, any emails associated with the item and any ips associated. There can be multiple emails and ips for each item, and i want everything either returned in one row or returned in an easily parsable way without any duplication of data associated with multiple joins.

    SELECT user_items.ID,user_items.name,data.*,x.emails,x.e_status,y.ns,x.email_counts,y.ns_counts
FROM user_items

LEFT JOIN data AS data ON (data.name = user_items.name AND data.version = user_items.version)

LEFT JOIN (
    SELECT  data_emails.name,data_emails.version, GROUP_CONCAT( b.email SEPARATOR ',' ) as emails, GROUP_CONCAT( b.sent SEPARATOR ',' ) as e_status, GROUP_CONCAT( b.email_count SEPARATOR ',' ) as email_counts
    FROM data_emails 
    LEFT JOIN (
        SELECT emails.ID,emails.email,emails.sent,data_emails.name,data_emails.version,data_emails.email_id,COUNT(data_emails.ID) as email_count
        FROM data_emails 
        LEFT JOIN emails ON (data_emails.email_id = emails.ID) GROUP BY data_emails.email_id) b
    ON (data_emails.email_id = b.ID) 
    GROUP BY data_emails.name 

 ) x ON (data.name = x.name AND x.version = user_items.version) 





LEFT JOIN (
    SELECT data_ips.name,data_ips.version, GROUP_CONCAT( c.ip SEPARATOR ',' ) as ns, GROUP_CONCAT( c.ips_count SEPARATOR ',' ) as ns_counts
    FROM data_ips 
    LEFT JOIN (
        SELECT ips.ID,ips.ip,data_ips.name,data_ips.version,data_ips.ns_id,COUNT(data_ips.ID) as ips_count
        FROM data_ips 
        LEFT JOIN ips ON (data_ips.ns_id = ips.ID) GROUP BY data_ips.ns_id) c 
    ON (data_ips.ns_id = c.ID) 
    GROUP BY data_ips.name

) y ON (data.name = y.name AND y.version = user_items.version)



WHERE user_items.folder = '2' AND user_items.owner='old' GROUP BY user_items.name LIMIT 100
share|improve this question
    
Have you REALLY asked everyone on the internet? –  Max Vernon Apr 17 '13 at 19:10
    
I'm quite curious what reference have you found for "I could speed this up with proper indexes (which I am getting my head fully around atm), but I am doubtful that there would be decent speed improvements.". –  Marian Apr 17 '13 at 19:16
    
Well.. im over the impression that indexes will at least help but I agree not enough.. and of course I have not asked everyone on the Internet but i have spent nigh on 40 hours researching this and found no answer. –  Thomas Clowes Apr 18 '13 at 9:32
    
It would be better if you added the query you have tried. Also, if you had foreign key constraints defined (or at least drawn on a diagram), would help others understand your design. –  ypercube Apr 22 '13 at 12:38
    
I have added the query I have tried. To clarify it works - returning exactly what i want but is just incredibly slow. –  Thomas Clowes Apr 22 '13 at 12:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.