rgb192
asked on
create table statement with indexes
I am not asking for a query that determines if staff3 was ever the sender of a message to a given profile_id (because that is another question).
I am asking how to modify the create table statement to include indexes because with many rows of data (more than I have shown), there is a long query run time.
I am asking how to modify the create table statement to include indexes because with many rows of data (more than I have shown), there is a long query run time.
abl: has a message from staff3
swa: has a message from staff3
dtw: has NO messages from staff3
fre: has NO messages from staff3
gam: has NO messages from staff3
h1a: has a message from staff3
nyh: has NO messages from staff3
big: has a message from staff3
CREATE TABLE `a_messages2` (
`a_messages_id` INT (11) auto_increment primary key
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
);
INSERT INTO a_messages2
(`profile_id`, `sender`, `message_id`, `this_user`)
VALUES
('abl', 'staff3', 3662615602, 'staff3'),
('abl', 'abl', 3660687080, 'staff3'),
('abl', 'staff3', 3658803944, 'staff3'),
('abl', 'staff3', 3649952930, 'staff3'),
('abl', 'staff3', 3644250249, 'staff3'),
('abl', 'staff3', 3614235515, 'staff3'),
('abl', 'abl', 3614140442, 'staff3'),
('abl', 'staff3', 3613984751, 'staff3'),
('abl', 'staff3', 3613936332, 'staff3'),
('abl', 'abl', 3613469574, 'staff3'),
('abl', 'staff3', 3612970481, 'staff3'),
('abl', 'abl', 3609276901, 'staff3'),
('abl', 'abl', 3609276896, 'staff3'),
('abl', 'staff3', 3866086756, 'staff3'),
('abl', 'staff3', 3866079064, 'staff3'),
('abl', 'abl', 3856964132, 'staff3'),
('abl', 'staff3', 3844779912, 'staff3'),
('abl', 'staff3', 3916948647, 'staff3'),
('abl', 'staff3', 3880367425, 'staff3'),
('abl', 'abl', 3977088222, 'staff3'),
('abl', 'staff3', 3974513295, 'staff3'),
('abl', 'staff3', 3973285282, 'staff3'),
('abl', 'abl', 3960907806, 'staff3'),
('abl', 'staff3', 4003664468, 'staff3'),
('abl', 'abl', 4002246444, 'staff3'),
('abl', 'staff3', 3996089944, 'staff3'),
('abl', 'staff3', 4024726432, 'staff3'),
('abl', 'abl', 4021516823, 'staff3'),
('abl', 'staff3', 4018957318, 'staff3'),
('abl', 'abl', 4050609080, 'staff3'),
('abl', 'staff3', 4052368867, 'staff3'),
('abl', 'abl', 4052424820, 'staff3'),
('abl', 'staff3', 4053183569, 'staff3'),
('swa','swa','4153199171','staff3'),
('swa','staff3','4150323049','staff3'),
('swa','swa','4146362776','staff3'),
('swa','staff3','4142884979','staff3'),
('swa','staff3','4142884976','staff3'),
('DTw', 'DTw', '3658976250', 'staff3'),
('DTw', 'DTw', '3658422687', 'staff3'),
('DTw', 'DTw', '3653399588', 'staff3'),
('DTw', 'DTw', '3653174764', 'staff3'),
('DTw', 'DTw', '3546098525', 'staff3'),
('DTw', 'DTw', '3545571594', 'staff3'),
('DTw', 'DTw', '3545571593', 'staff3'),
('DTw', 'DTw', '3540574071', 'staff3'),
('DTw', 'DTw', '3540454344', 'staff3'),
('DTw', 'DTw', '3540376883', 'staff3'),
('fre', 'fre', '3544889093', 'staff3'),
('gam', 'gam', '3955910171', 'staff3'),
('gam', 'gam', '3955910165', 'staff3'),
('gam', 'gam', '3649458031', 'staff3'),
('gam', 'gam', '3648816297', 'staff3'),
('gam', 'gam', '3648783076', 'staff3'),
('gam', 'gam', '3648516109', 'staff3'),
('gam', 'gam', '3647758123', 'staff3'),
('gam', 'gam', '3554774961', 'staff3'),
('gam', 'gam', '3540425395', 'staff3'),
('gam', 'gam', '3540357159', 'staff3'),
('gam', 'gam', '3540345288', 'staff3'),
('h1a', 'staff3', '3700557963', 'staff3'),
('h1a', 'h1a', '3693946789', 'staff3'),
('h1a', 'h1a', '3693806133', 'staff3'),
('h1a', 'staff3', '3684800156', 'staff3'),
('h1a', 'h1a', '3684173904', 'staff3'),
('h1a', 'h1a', '3682095987', 'staff3'),
('h1a', 'h1a', '3682095983', 'staff3'),
('h1a', 'h1a', '3542388954', 'staff3'),
('nyh', 'nyh', '3554817690', 'staff3'),
('nyh', 'nyh', '3554796334', 'staff3'),
('nyh', 'nyh', '3554701760', 'staff3'),
('nyh', 'nyh', '3554689575', 'staff3'),
('nyh', 'nyh', '3541014726', 'staff3'),
('nyh', 'nyh', '3541012663', 'staff3'),
('nyh', 'nyh', '3540619436', 'staff3'),
('nyh', 'nyh', '3540474531', 'staff3'),
('big', 'big', '3947506235', 'staff3'),
('big', 'staff3', '3947496223', 'staff3'),
('big', 'big', '3947489941', 'staff3'),
('big', 'staff3', '3938366539', 'staff3'),
('big', 'staff3', '3647107151', 'staff3'),
('big', 'big', '3587867444', 'staff3'),
('big', 'staff3', '3542393433', 'staff3')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so are you saying I can just add index to every column and this will reduce select times
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so what is the command to add index to all columns
ALTER TABLE `a_messages2` ADD INDEX ( `a_messages_id`,`profile_i d`, `sender`, `message_id`, `this_user` )
ALTER TABLE `a_messages2` ADD INDEX ( `a_messages_id`,`profile_i
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
add index to all columnsThis is an ambiguous question. Let me try to explain why.
You can have an index on a single column. You can have an individual index on each of several columns. You can also have an index that spans two or more columns, or even the entire table, taking all columns into account simultaneously. So would it be better to have one index that covers all of the columns at once? Or should you have one index for each column? The choice of the exact kind of index you need is something that will be dependent on the queries you expect to optimize. You may want to write these queries and use EXPLAIN SELECT to see what the SQL engine is doing with your queries. Then you will know what kinds of indexes will be best
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
explain
SELECT
profile_id,this_user
FROM a_messages2
WHERE (
( /* here we determine who originated the profile */
message_id IN (SELECT min(message_id)
FROM a_messages2 GROUP BY profile_id)
AND
profile_id <> sender /* originated by staff */
)
OR
( /* here we determine who has sent latest message */
message_id IN (SELECT max(message_id)
FROM a_messages2 GROUP BY profile_id)
AND
profile_id = sender /* needs a response by staff */
)
)
GROUP BY profile_id
HAVING count(*) = 2
and these are results
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,a_messages2,ALL,NULL,NULL,NULL,NULL,9707,"Using where; Using temporary; Using filesort"
3,"DEPENDENT SUBQUERY",a_messages2,ALL,NULL,NULL,NULL,NULL,9707,"Using temporary; Using filesort"
2,"DEPENDENT SUBQUERY",a_messages2,ALL,NULL,NULL,NULL,NULL,9707,"Using temporary; Using filesort"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the point for this query?
Presumably you have one table (working) where questions are open and are being actively monitored. here you need only look whether staff needs to respond.
When the user is satisfied with the response they received, they would presumably close the issue as resolved at which point the issue will be moved to an archive table and out of the working table.
Similarly for the staff originated question, when the user has responded adequately, the question is closed and moved out of this queue.
Presumably you have one table (working) where questions are open and are being actively monitored. here you need only look whether staff needs to respond.
When the user is satisfied with the response they received, they would presumably close the issue as resolved at which point the issue will be moved to an archive table and out of the working table.
Similarly for the staff originated question, when the user has responded adequately, the question is closed and moved out of this queue.
ASKER
skullnobrains:
I ran your query and it takes a long time to run (which is similar to the original query that was made for me
should I run explain select on your query or will you edit now that you know of unique key?
arnold:
you're hiding some information : there is a primary key and it is actually used. i'd assume that it is similar to the compound index i was suggestingyes there is a unique key
CREATE TABLE `a_messages2` (
`a_messages_id` int(11) NOT NULL auto_increment,
`conversation_id` bigint(20) default NULL,
`profile_id` varchar(20) default NULL,
`sender` varchar(20) default NULL,
`message_id` bigint(20) default NULL,
`message_text` varchar(1000) default NULL,
`dateAgo` varchar(20) default NULL,
`message_read` tinyint(4) default NULL,
`this_user` varchar(20) default NULL,
PRIMARY KEY (`a_messages_id`),
UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9969 DEFAULT CHARSET=utf8;
select profile_id,this_user
FROM
a_messages2
WHERE
profile_id = sender /* needs a response by staff */
and message_id IN
(
SELECT
max(message_id)
FROM
a_messages2
WHERE
profile_id IN
(
SELECT
profile_id
FROM a_messages2
WHERE
message_id IN (SELECT min(message_id) FROM a_messages2 GROUP BY profile_id)
AND profile_id <> sender /* originated by staff */
)
GROUP BY profile_id
)
I ran your query and it takes a long time to run (which is similar to the original query that was made for me
should I run explain select on your query or will you edit now that you know of unique key?
arnold:
What is the point for this query?I think the boss needs this for reporting
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
(profile_id,message_id) will likely be much more usefull as mysql will be able to retrieve the min message_ids for each profile_id very fast, while it currently requires a full table scan, a temporary table and a sort
I think I agree but I do not fully understand the query because I did not write the query
So how would you change the query.
I do not understand the other data you wrote
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
modifying skullnobrains code
https://www.experts-exchange.com/questions/28318003/create-table-statement-with-indexes.html?anchorAnswerId=39726770#a39726770
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' INDEX profile_id (profile_id) , INDEX profile_id_and_message_id (profile_i' at line 8
https://www.experts-exchange.com/questions/28318003/create-table-statement-with-indexes.html?anchorAnswerId=39726770#a39726770
CREATE TABLE `a_messages3` (
`a_messages_id` INT (11) auto_increment primary key
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
, PRIMARY KEY messages_id
, INDEX profile_id (profile_id)
, INDEX profile_id_and_message_id (profile_id,message_id)
);
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' INDEX profile_id (profile_id) , INDEX profile_id_and_message_id (profile_i' at line 8
i forgot a couple of brackets
PRIMARY KEY messages_id --> PRIMARY KEY (messages_id)
sorry for the typo, but on the other hand, i would assume you could debug that much by reading the manual http://dev.mysql.com/doc/refman/5.1/en/create-table.html
PRIMARY KEY messages_id --> PRIMARY KEY (messages_id)
sorry for the typo, but on the other hand, i would assume you could debug that much by reading the manual http://dev.mysql.com/doc/refman/5.1/en/create-table.html
ASKER
CREATE TABLE `a_messages3` (
`a_messages_id` INT (11) auto_increment primary key
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
, PRIMARY KEY (a_messages_id)
, INDEX profile_id (profile_id)
, INDEX profile_id_and_message_id (profile_id,message_id)
);
Error Code: 1068. Multiple primary key defined
but this worked
CREATE TABLE `a_messages3` (
`a_messages_id` INT (11) auto_increment
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
, PRIMARY KEY (a_messages_id)
, INDEX profile_id (profile_id)
, INDEX profile_id_and_message_id (profile_id,message_id)
);
is this correct?
note: a_messages_id is just a primary key column with no data other than an auto increment number
message_id is a big int of the message which is an important value
`a_messages_id` INT (11) auto_increment primary key
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
, PRIMARY KEY (a_messages_id)
, INDEX profile_id (profile_id)
, INDEX profile_id_and_message_id (profile_id,message_id)
);
Error Code: 1068. Multiple primary key defined
but this worked
CREATE TABLE `a_messages3` (
`a_messages_id` INT (11) auto_increment
, `profile_id` VARCHAR(20)
, `sender` VARCHAR(20)
, `message_id` BIGINT (20)
, `this_user` VARCHAR(20)
, PRIMARY KEY (a_messages_id)
, INDEX profile_id (profile_id)
, INDEX profile_id_and_message_id (profile_id,message_id)
);
is this correct?
note: a_messages_id is just a primary key column with no data other than an auto increment number
message_id is a big int of the message which is an important value
yes. there are 2 ways to define primary keys and you're not supposed to use both at the same time. either would be fine, i just forgot to remove one.
i hardly understand why you ask since you obviously figured it out.
---
btw, if you're not using the primary key, you're not required to have one. when using myisam tables, defining a useless PK is a loss of space and write performance. when using innodb, it won't make a difference since innodb would create a hidden auto-increment column and use it as the primary key if you don't declare one.
other than that, if a column such as message_id is unique, most likely it should be declared as the primary key.
---
did you do any tests ?
i hardly understand why you ask since you obviously figured it out.
---
btw, if you're not using the primary key, you're not required to have one. when using myisam tables, defining a useless PK is a loss of space and write performance. when using innodb, it won't make a difference since innodb would create a hidden auto-increment column and use it as the primary key if you don't declare one.
other than that, if a column such as message_id is unique, most likely it should be declared as the primary key.
---
did you do any tests ?
ASKER
I changed a_messages2 to a_messages3
a_messages3 has only needed columns
Error Code: 1317 Query execution was interrupted
problem is query still will not run in under 10 minutes
I need to extend 10 minutes and delete all other users than staff1 (I still did not do command delete from a_messages where this_user!='staff1') just to run the query which is the same problem i had previously
So is the query working better? I am not sure because I have difficulty with new and previous queries.
a_messages3 has only needed columns
CREATE TABLE `a_messages3` (
`a_messages_id` int(11) NOT NULL auto_increment,
`profile_id` varchar(20) default NULL,
`sender` varchar(20) default NULL,
`message_id` bigint(20) default NULL,
`this_user` varchar(20) default NULL,
PRIMARY KEY (`a_messages_id`),
KEY `profile_id` (`profile_id`),
KEY `profile_id_and_message_id` (`profile_id`,`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9708 DEFAULT CHARSET=utf8;
select profile_id,this_user
FROM
a_messages3
WHERE
profile_id = sender /* needs a response by staff */
and message_id IN
(
SELECT
max(message_id)
FROM
a_messages3
WHERE
profile_id IN
(
SELECT
profile_id
FROM a_messages3
WHERE
message_id IN (SELECT min(message_id) FROM a_messages3 GROUP BY profile_id)
AND profile_id <> sender /* originated by staff */
)
GROUP BY profile_id
)
Error Code: 1317 Query execution was interrupted
problem is query still will not run in under 10 minutes
I need to extend 10 minutes and delete all other users than staff1 (I still did not do command delete from a_messages where this_user!='staff1') just to run the query which is the same problem i had previously
So is the query working better? I am not sure because I have difficulty with new and previous queries.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I do not fully understand yet, so I will need to do database tutorials. Queries work but I do not understand why slow. I need to learn more about indexes and mysql reporting.
thanks for the correct direction.
thanks for the correct direction.
After the table is created (and if possible, after the table is loaded) you can use something like this:
Open in new window