SQL problem. Large table = slow query ?

Question

The ONLY slow query is
SELECT * FROM video WHERE username = 'XXXXX';

I need that query because i need to count the rows, therefore be able to count how many videos a user uploaded.

the member XXXXX has 117738 videos in total...

Any help?

PS:
The server is a private server..
512 MB RAM and 1GHZ CPU


Here is the slow queries log

Time: 091129 19:37:34
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
use lucky_XXXXX;
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:37:45
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:38:06
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:38:42
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:39:00
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:39:04
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:39:25
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:39:34
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:39:55
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:40:20
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:40:51
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:41:29
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:43:14
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:43:25
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:43:50
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';
# Time: 091129 19:44:00
# User@Host: lucky_fb[lucky_fb] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 117733 Rows_examined: 117738
SELECT * FROM video WHERE username = 'XXXXX';

Answer 1

The slowness is in your 'WHERE' clause. String comparison is very slow because it needs to match character by character, row per row.



If possible, add a 'user_id' field where the ID is a numeric data type (e.g.: integer) then change your query so that it does a look up on the user ID instead of username. This will improve the speed significantly.



Another way to improve the speed is to create an index on the field in your 'WHERE' clause.



Also, if you just need to count how many rows there are, you can just do "SELECT COUNT(*)" instead of "SELECT *". With "SELECT *" the database server will need to retrieve and return you all rows, so there's more I/O and memory usage required.

Answer 2

I believe some flavors of SQL support a COUNT statement.



It looks like your query right now is pulling back all the the info in each row so that it can do a simple count of the number of rows.



The first thing to do would be to look to see if you can use a COUNT (or similar) SQL statement. If you can't, instead of SELECT *, try SELECT Id, or some other miniscule piece of information rather than pulling the entire row.



If that is still too slow, you may have to have to add a MovieCount column to your user table.

Answer 3

Robin gives excellent advice - the sole caveat there is that, if you declare an index, it will speed up your searches but also will slow down updates (since the index will also need maintaining)...whether it slows down updates enough to be a concern all depends on the volatility of your data.

Answer 4

You could try SELECT * FROM video; and then count if username == XXXXX.



but you're dealing with such a large table, it's bound to take a little while.

Answer 5

try: decide on * from worker the place top(employee_name) like 'JOHN' the top key-word variations the emplyee_name to uppercase in the previous comparing it to JOHN, this supply you john, John and JOHN and so on. If this does not artwork seem on your sq. documentation for the top function