Be seen. Boost your question’s priority for more expert views and faster solutions
Solved
create table statement with indexes
Posted on 2013-12-13
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')
-
10
7
4- +2
26 Comments
if you want ot include an index or multiple indices during table creation, this is the manual page:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
if you want to add one or more indices to an existing table, then this is the manual page:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
Adding indices to the table may reduce response time for selects, but will have the opposite effect for inserts and deletes; and somtimes for updates, because each index has to be updated for modification operations.
Cd&
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
if you want to add one or more indices to an existing table, then this is the manual page:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
Adding indices to the table may reduce response time for selects, but will have the opposite effect for inserts and deletes; and somtimes for updates, because each index has to be updated for modification operations.
Cd&
Active today
Use mysql workbench:query browser
explain your sql query
This will suggest/point to which index you may need.
You can/should consider adding phpmyadmin that will access the mysql statistics and can/will provide you with suggestions on how to improve performance.
Not sure what you are trying to do. If the table is in place, use alter table /create index to add the index you need.
Then you can issue the show create table and it will reflect the create table instruction with the index directive,
explain your sql query
This will suggest/point to which index you may need.
You can/should consider adding phpmyadmin that will access the mysql statistics and can/will provide you with suggestions on how to improve performance.
Not sure what you are trying to do. If the table is in place, use alter table /create index to add the index you need.
Then you can issue the show create table and it will reflect the create table instruction with the index directive,
@rgb192: you can post your questions in more than one Zone. For example, you can post questions like this in both the PHP and MySQL Zones.
After the table is created (and if possible, after the table is loaded) you can use something like this:
After the table is created (and if possible, after the table is loaded) you can use something like this:
$mysqli->query("ALTER TABLE `a_messages2` ADD INDEX ( `profile_id` )" );
Active today
so are you saying I can just add index to every column and this will reduce select times
Yes, it will dramatically reduce the SELECT times. You may not need an index on every column. I would recommend an index on any column that is used in WHERE, JOIN, ORDER, HAVING, GROUP. I also recommend a LIMIT clause unless your query absolutely must have a complete table scan.
The note from COBOLdinosaur is correct about increasing the time needed for INSERT and DELETE queries. If the table structure is fairly simple and straightforward, I would expect that the time for UPDATE queries may actually be reduced since the SQL engine can find the rows faster.
You might want to do some experimentation with the use of indexes. This article has a simple data base load with a lot of rows. You could try with and without indexing and see what the times are like in your environment.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3437-IP-Address-to-Country-in-PHP.html
The note from COBOLdinosaur is correct about increasing the time needed for INSERT and DELETE queries. If the table structure is fairly simple and straightforward, I would expect that the time for UPDATE queries may actually be reduced since the SQL engine can find the rows faster.
You might want to do some experimentation with the use of indexes. This article has a simple data base load with a lot of rows. You could try with and without indexing and see what the times are like in your environment.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3437-IP-Address-to-Country-in-PHP.html
Active today
so what is the command to add index to all columns
ALTER TABLE `a_messages2` ADD INDEX ( `a_messages_id`,`profile_i
ALTER TABLE `a_messages2` ADD INDEX ( `a_messages_id`,`profile_i
Active today
You need to run an alter table or create index per column.
An index has to define what its basis are.
You can have an index based on two columns.
Alter table tablename add index indexname using hash column1,column3 unique
An index has to define what its basis are.
You can have an index based on two columns.
Alter table tablename add index indexname using hash column1,column3 unique
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
Active today
so are you saying I can just add index to every column and this will reduce select times
don't do this, ever : most of them will likely be useless, inserts will be painfully slow when the table gets bigger, and compound indexes might be required for the queries you run most often
give an example of a slow select, and possibly the associated explaiin, and we'll be able to determine which indexes are usefull
--
beware that the alter table statement will be awfully slow in mysql, and lock the whole table while it runs
--
back to your original question :
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)
, PRIMARY KEY messages_id
, INDEX profile_id (profile_id)
, INDEX profile_and_sender (profile_id,sender)
);
will for example create the same table with message_id as a primary key (probably something you want) and an index on profile_id (also probably something you want), and a compount index named profile_and_sender containing the profile_id and the sender (which is only there for demonstration)
---
in compound indexes the order in which you specify the columns is important
for example if i have a compound index on two columns named type and timestamp
- i CAN use this index to query all the records of type X between 2 timestamps using an index range (and it will be very efficient)
- i CANNOT use this index to recover all the lines in the same range without specifying their types
---
in myisam tables, specifying a unique index or a primary key is the same thing. the table data is stored in natural order and the indexes are in a separate files
in innodb tables, the primary key determines the order in which the data is stored. the size of the primary key index is the size of the whole data since it actually contains the table's data. the other indexes are stored separately. creating adequate primary keys in innodb is very important both in terms of insert performance and read performance. the length of the records and an idea of the queries that will be run are important in making the decision
There is nothing to explain in select * from a_messages2. The query is going to retrieve all of the columns in all of the rows, right? There is nothing that can be done to optimize that query. You would need to change the query.
Active today
if you can, run explain in the command line mysql tool, this way the content is text which you can copy and paste here (above comment)
For indexing, make sure to use where, order by, group by, join etc.
Install phpmyadmin on the system it too will provide suggestions based on the statistics mysql maintains.
For indexing, make sure to use where, order by, group by, join etc.
Install phpmyadmin on the system it too will provide suggestions based on the statistics mysql maintains.
Active today
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"
Active today
indexes will probably help a little, (at least try a compound index using profile_id,message_id) should help with the subqueries
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 suggesting
this kind of queries is pretty slow in sql as a general rule so you most likely should consider rewriting part of the query or even the code. notice that you currently go through ALL the rows in the table once, in the ouside query and then perform the subqueries which is suboptimal to ay the least
maybe try this rewrite
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
)
most likely, if you have at least one index starting with message_id and one starting wit profile_id, it will be reasonably fast.
reverse the logic if you have many more threads that need a response than threads that are originated by staff
you may also try to rewrite the above using joins rather than subqueries and let the mysql optimiser pick the best choice for you
if you need more help, an idea of the number of rows, number of distinct profile_ids, number of expected rows would be welcome, together with the list of existing indexes and explain of what query you tried
other options include the following
use a stored procedure or possibly external program that will simply go through the whole table sorted by profile_id,message_id (which will be fast if the corresponding compound index exist) and just pull the needed threads by comparing the profile_id and the sender for the first and last message_id relative to each profile_id
the same algorythm as the above can also be achieved in plain sql using group_concat : something like this :
select profile_id,this_user,group
from a_messages2 GROUP BY profile_id HAVING ids LIKE CONCAT ('%,',profile_id) and ids not like concat(profile_id,',%')
a better way would probably be to create an external table with data such as profile_id,created_by,last
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 suggesting
this kind of queries is pretty slow in sql as a general rule so you most likely should consider rewriting part of the query or even the code. notice that you currently go through ALL the rows in the table once, in the ouside query and then perform the subqueries which is suboptimal to ay the least
maybe try this rewrite
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
)
most likely, if you have at least one index starting with message_id and one starting wit profile_id, it will be reasonably fast.
reverse the logic if you have many more threads that need a response than threads that are originated by staff
you may also try to rewrite the above using joins rather than subqueries and let the mysql optimiser pick the best choice for you
if you need more help, an idea of the number of rows, number of distinct profile_ids, number of expected rows would be welcome, together with the list of existing indexes and explain of what query you tried
other options include the following
use a stored procedure or possibly external program that will simply go through the whole table sorted by profile_id,message_id (which will be fast if the corresponding compound index exist) and just pull the needed threads by comparing the profile_id and the sender for the first and last message_id relative to each profile_id
the same algorythm as the above can also be achieved in plain sql using group_concat : something like this :
select profile_id,this_user,group
from a_messages2 GROUP BY profile_id HAVING ids LIKE CONCAT ('%,',profile_id) and ids not like concat(profile_id,',%')
a better way would probably be to create an external table with data such as profile_id,created_by,last
Active today
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.
Active today
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
Active today
either query will require you to index profile_id at least. the index may be a compound as long as it starts with profile_id.
for example
(profile_id,sender) can be of some use since mysql will be able to deal with some of the where clauses ( profile_id = or <> sender ) directly using the index without doing useless lookups in the table.
(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
what i'm expecting is mysql to start with the innermost query which currently requires to read the whole table (much less work if you create the latter compound index), and then to bubble through the other queries using indexes every time
if it is slow, yes please provide the explain. (after you created the index)
other useful data include
- number of rows in the table
- number of distinct profile_ids
- timing for the innermost query. if it is fast go on with the 2 innermost...
currently indexing and query rewrite is your best course of action because the explain of your current query clearly shows you are rereading all the table data multiple times.
for example
(profile_id,sender) can be of some use since mysql will be able to deal with some of the where clauses ( profile_id = or <> sender ) directly using the index without doing useless lookups in the table.
(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
what i'm expecting is mysql to start with the innermost query which currently requires to read the whole table (much less work if you create the latter compound index), and then to bubble through the other queries using indexes every time
if it is slow, yes please provide the explain. (after you created the index)
other useful data include
- number of rows in the table
- number of distinct profile_ids
- timing for the innermost query. if it is fast go on with the 2 innermost...
currently indexing and query rewrite is your best course of action because the explain of your current query clearly shows you are rereading all the table data multiple times.
Active today
(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
Active today
- create the above-mentioned index : (profile_id,message_id)
- try your initial query
- if it is slow, try mine and see if it is better
- post results and what other "useful data" i asked for in my previous post
i can't help you unless you actually start doing something
- try your initial query
- if it is slow, try mine and see if it is better
- post results and what other "useful data" i asked for in my previous post
i can't help you unless you actually start doing something
Active today
modifying skullnobrains code
http://www.experts-exchange.com/Database/MySQL/Q_28318003.html#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
http://www.experts-exchange.com/Database/MySQL/Q_28318003.html#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
Active today
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
Active today
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
Active today
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 ?
Active today
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.
Active today
- create the above-mentioned index : (profile_id,message_id)
- try your initial query
- if it is slow, try mine and see if it is better
- post results and what other "useful data" i asked for in my previous post
i can't help you unless you actually start doing something
Featured Post
Your question, your audience. Choose who sees your identity—and your question—with question security.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Creating and Managing Databases with phpMyAdmin in cPanel.
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 17 hours left to enroll
606 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.