[Resolved] Query Takes 115 Seconds To Execute! Help!

az_jassal

Hello there,

I have a MySQL table named 'mails', there are just over 12,000 records in this table.

One of my PHP scripts is taking absolutely forever to execute, upon going through the script, I found that all the MySQL queries are processed in under a second, except for this one:

SELECT *
FROM mails
WHERE mail_from = 'someone@somewhere.com'
ORDER BY mail_id DESC

When I run this query in phpMyAdmin, it takes over 115 seconds to execute. When this query is removed from my script,the remaining PHP script executes in just over a second.

Could someone explain to me what the cause of this can be please?

The database server is MYSQL 4.0.20 on a RedHat 9 install, Apache 2 with PHP 5. There are 23 tables in this database, 'mails' holds the most records and another table holds 10,000 records, although executing a WHERE query using that table is done in normal speed.

The server is a AMD Athlon 2100+ with 256MB RAM with 2 40GB 5200rpm hard disks across a RAID0 array.

devinemke

have you indexed the mail_from field?

az_jassal

Wow that was fast!

Umm.. I'm sorry, I'm not familiar with indexing? The field has not been indexed, although I do see the option in phpMyAdmin to index it. The table does have a 'Primary Key' which is mail_id.

Shall I proceed to index 'mail_from' ?

devinemke

read up on indexes

az_jassal

Excellent, query time from 115 seconds now only 0.5 second!

Many thanks!

Also, I have the following query, on the same table, which takes almost 22 seconds to execute, do you have any ideas why so long? Many thanks again!

SELECT count(DISTINCT(mail_from)) FROM mails WHERE folder_id = '1' AND read != 'y' AND mail_from != '' ORDER BY mail_id DESC

devinemke

re-read my first post

nemonoman

You will speed up processing if you set your columns as NOT NULL.

If folder_id is a numeric column, then your processing will be much faster with
folder_id=1
than with
folder_id='1'

If the column type and the search type are different (1 is not the same as '1'), then the db converts the entire column to a text field before beginning the search, then does a text search. This is very much slower, and will continue to be, even if the folder_id column is indexed.

nemonoman

[deleted]

az_jassal

Thank you for all of your help.

Through phpMyAdmin I 'optomized' the table, the query now takes about 0.4 seconds to execute. Many thanks again.