query speed

[nitinpatel1117]

Hi

I have a mysql query which seems to execute slowly.
I was wondering if there was a way to speed it up a bit.

the query is;

select count(*) as emailCount from mailing_list where user like 'joel@bloggs.com' and list_id = '47'

this table has a primary key called 'email_id'
so I was wondering if

select count(email_id) as emailCount from mailing_list where user like 'joel@bloggs.com' and list_id = '47'

would execute faster, but it still seems to be roughly the same amount of time.


I have about 1 million records in this table, and the query takes about 2 seconds to execute. I would like to reduce this time as much as possible, as i have a few other queries that get executed later on in my coding. therefore the overall speed will seems even slower.

thanks in advance

[code green]

Replace the LIKE with equals (=) if you do not really need the LIKE.
And remove the quotes from numeric values

select count(email_id) as emailCount from mailing_list where user = 'joel@bloggs.com' and list_id = 47

[mwasif]

Creating indexes on user and list_id will make the query much faster. You can use EXPLAIN to measure the query performance.

[nitinpatel1117]

Hi code green,

thanks for that,

i have two more questions though.

firstly
I've read that having indexes in the where part of the sql query will increase speed further. i will mainly be doing selects than inserts, so this seems logical. However there is one thing that i don't quite get. Using my above example can i use 'user' field or 'list_id' field as an index, these fields are not unique, so will indexes still work or bring about an improvement in speed.

Second
Does count(email_id) execute alot faster than count(*), if so its probably worth me doing this for all my queries where i may using this.

thanks

[nitinpatel1117]

I checked my table type, it is InnoDB.

If I were to change this to MyISAM, would this have any adverse effects on my table, and in particluar the data stored in the table?