SQL query takes a lot of time

Question

I have a table called Ads that contains ~ 441,000 rows and 21 columns.

I am trying to run the following query:

SELECT Ads.* FROM Ads WHERE Ads.countries_CountryId = 'FR'

I put the index in the countries_CountryId field of type char(2), but when I run the request above, it takes about 5-8 seconds. This amount of time is very large for me, for such a medium-sized table and such a simple SQL query. Where should I look for a problem? Or is it normal when such a request takes so long?

I also tried to execute the EXPLAINabove query and get the following results, but I don't know how to decrypt it: (

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  Ads     ref     countries_CountryId     countries_CountryId     2   const   24368   Using where

EDIT1: (response to Seth McClaine)

I tried your suggestion, as you suggested, returns an error, but if I run it SELECT count(*) FROM Ads WHERE Ads.countries_CountryId = 'FR', it will work much faster: 0.0052490234375

But the problem is that I don’t print anything, I use php to run the requests and calculate their execution time at the moment, and in the end the requests and the time they took are displayed:

 Array
(
[0] => Array
    (
        [query] => SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'
        [duration] => 0.00481009483337
    )

[1] => Array
    (
        [query] => SELECT * FROM Countries WHERE NameFormatted LIKE '%FRANCE%'
        [duration] => 0.00234889984131
    )

[2] => Array
    (
        [query] => SELECT Ads.* FROM Ads WHERE Ads.countries_CountryId = 'FR'
        [duration] => 4.71820402145
    )

[3] => Array
    (
        [query] => SELECT COUNT(*) FROM Ads WHERE Ads.countries_CountryId = 'FR'
        [duration] => 0.0052490234375
    )
)

Here is also a snippet of code that runs queries:

 public function query($query, $cacheit=true) {
    if (!$this->isConnected()) {
        $this->throwError("Querying <b>".$query."</b> failed, because MySQL is not connected!", self::MYSQL_NOT_CONNECTED, false);
        return false;
    }

    $qstart=microtime(true);
    $result=@mysql_query($query, $this->conn_resource);
    $qduration=microtime(true) - $qstart;

    if ($result===FALSE) $this->throwError("Querying <b>".$query."</b> failed!", self::MYSQL_QUERY_ERR);

    $this->numQueries++;
    $this->executedQueries[] = array(
        'query'     =>  $query,
        'duration'  =>  $qduration
    );

    return $result;
}

Answer 1

Have you tried to execute the request in read-only mode? As described here in the mysql documentation. You can try to start your request with

START TRANSACTION READ ONLY

Alternatively, you can try using the explanation instruction to get more information about your request.

EXPLAIN SELECT Ads.* FROM ....

, . .