MySQL Forums :: Performance :: Reg table design and query performances


Advanced Search

Reg table design and query performances
Posted by: Devrishi Shandilya ()
Date: April 26, 2016 03:02AM

Hi Friends,

I have created a table to store users and their attribute (firstname, email, role, location ... etc). Attribute could be up to 10-100 for a single user. User_id is userid and name is attribute name and value is attribute value.

i.e. user_id is 1 and name is location and value is Newyork City.

I have filled the table with 3500000 entries, with per user 7 attributes (Firstname, Lastname, email, role, extension, location, designation), so users are 500000 and in attribute table entries for those users are 3500000.

My issue is when i querying for complex search like to find users of those criteria like :

Find all users where department=’Sales’ and Role !=’Broker’ and Location=’Seattle’, query is taking much time then expected as huge data scanned for role!='broker'. Query is used :

select a.user_id,a.value as department,b.value as role,c.value as location from
((select user_id,value from attribute_value where name='department' and value='Sales') a
join (select user_id,value from attribute_value where name='role' and value!='broker') b on a.user_id=b.user_id
join (select user_id, value from attribute_value where name='location' and value='Seattle') c on a.user_id=c.user_id );

I have already created index on (name,value);

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


Kindly suggest for best table design for the same, if there is any issue with current design.

CREATE TABLE `attribute_value` (
`user_id` bigint(20) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`,`name`),
CONSTRAINT `FKb2f2gwh8prxl9nhd40hobb30r` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-----------------------------------------------------------------------------
stored procedure used to populate data into table :

DELIMITER $$

USE `pip`$$

DROP PROCEDURE IF EXISTS `testfill`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `testfill`()
BEGIN
DECLARE i INT unsigned DEFAULT 1;
WHILE (i <= 500000) DO
insert into user (id) values (i);
insert into attribute_value (user_id, name, value) values (i, 'First Name', 'Bhavesh');
insert into attribute_value (user_id, name, value) values (i, 'Last Name', 'Upaghyay');
insert into attribute_value (user_id, name, value) values (i, 'Email', 'bhavesh@xyz.com');
insert into attribute_value (user_id, name, value) values (i, 'Department', 'Mainframe');
insert into attribute_value (user_id, name, value) values (i, 'Extension', '4567');
insert into attribute_value (user_id, name, value) values (i, 'Location', 'NewYork City');
insert into attribute_value (user_id, name, value) values (i, 'Role', 'Trader');
SET i = i+1;
END WHILE;
update `attribute_value` set value=floor(rand()*10000) where name='Extension';
update `attribute_value` set value=CONCAT("ipctest", RAND() * 100 , '@xyz.com') where name='email';
UPDATE `attribute_value` SET value = ELT(1 + FLOOR(RAND()*8), 'Mainframe', 'Testing', 'DBA', 'Network', 'Development', 'Finance' , 'HR', 'Sales') where name='Department';
UPDATE `attribute_value` SET value = ELT(1 + FLOOR(RAND()*10), 'NewYork City', 'San Jose', 'Murray Hills', 'Fairfax', 'San Francisco', 'Miami' , 'Las Vegas', 'London','Seattle','Redwood City') where name='Location';
UPDATE `attribute_value` SET value = ELT(1 + FLOOR(RAND()*8), 'Trainee', 'Trade Analyst','Broker','Financial Analyst','QE Engineer','Developer','Team Leader','Seller') where name='Role';
update `attribute_value` set value=CONCAT(
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25))),
CHAR( FLOOR(65 + (RAND() * 25)))
) where name in ('First Name','Last Name');
END$$

DELIMITER ;
------------------------------------------------------------------------------

Options: ReplyQuote


Subject Views Written By Posted
Reg table design and query performances 695 Devrishi Shandilya 04/26/2016 03:02AM
Re: Reg table design and query performances 373 Øystein Grøvlen 04/26/2016 06:02AM
Re: Reg table design and query performances 493 Devrishi Shandilya 04/26/2016 06:16AM
Re: Reg table design and query performances 369 Øystein Grøvlen 04/26/2016 06:35AM
Re: Reg table design and query performances 394 Devrishi Shandilya 04/26/2016 06:50AM
Re: Reg table design and query performances 367 Øystein Grøvlen 04/26/2016 07:04AM
Re: Reg table design and query performances 377 Rick James 04/30/2016 11:46PM
Re: Reg table design and query performances 328 Øystein Grøvlen 05/02/2016 01:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.