Reg table design and query performances

[Devrishi Shandilya]

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', '[e-mail]');
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 ;
------------------------------------------------------------------------------