MySQL Forums
Forum List  »  Performance

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
806
April 26, 2016 03:02AM


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.