Ajax Chat Queries locking.. Help

Hi,

I'm trying to optimize my online chat and this query show up because in the log becuase it is constantly locking the table.

SELECT
                                                userID,
                                                userName,
                                                userRole,
                                                channel,
                                                UNIX_TIMESTAMP(dateTime) AS timeStamp,
                                                ip
                                        FROM
                                                ajax_chat_online
                                        ORDER BY
                                                userName;

Code (markup):

Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ajax_chat_online ALL NULL NULL NULL NULL 6 Using filesort

I tried to add a key to userName but it is still using filesort which is locking the table.

How would you optimize this query/table?

 

hey, what is the data size? As in how many columns the table has and how many rows?

Also, I have noticed no filter option in your query. Do you need to read all the data in table and display?

 

Usually the table contains about 5-20 rows. It is the table that hold the users who are online at the time. When someone is in chat this query is run reguarly to show who is still online. It is locking contantly so I am trying to optimize. I tried adding userName as an index but that did not help.

 

With just 20 rows, chances are very very less that table will get locked. However, checking following points would help:

1. Is table used in JOIN query anywhere in app?
2. Does table have constant select / insert / update / delete queries running over it?
3. Did you see process list when the table gets locked? There could be something else which is locking table. May be a heavy process using most of resources or something like that.

 

That query shouldn't lock the table unless there are inserts waiting to be committed. It would normally require some pretty extreme load to actually see a delay.

What engine are you using? How many users are online at the same time.

 

thanks for all the replies. I think that the problem is either mysql is'nt configured very well or there are other queries running before it and locking things. I am using MyISAM, the forum has about 60 members logged in on average.

How would I find the query that is causing other queries to lock up?