I am trying to speed up a simple SELECT query on a table that has around 2 million entries, in a MariaDB MySQL database. It took over 1.5s until I created an index for the columns that I need, and running it through PhpMyAdmin showed a significant boost in speed (now takes around 0.09s).

The problem is, when I run it through my PHP server (mysqli), the execution time does not change at all. I'm logging my execution time by running microtime() before and after the query, and it takes ~1.5s to run it, regardless of having the index or not (tried removing/readding it to see the difference).

Query example:

SELECT pair, price, time FROM live_prices FORCE INDEX (pairPriceTime) WHERE time = '2022-08-07 03:01:59';

Index created: ALTER TABLE live_prices ADD INDEX pairPriceTime (pair, price, time);

Any thoughts on this? Does PHP PDO ignore indexes? Do I need to restart the server in order for it to "acknowledge" that there is a new index? (Which is a problem since I'm using a shared hosting service...)

Thanks in advance.

CodePudding user response：

The mysql documenation says

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

MariaDB documentation Force Index here says this

FORCE INDEX works by only considering the given indexes (like with USE_INDEX) but in addition, it tells the optimizer to regard a table scan as something very expensive. However, if none of the 'forced' indexes can be used, then a table scan will be used anyway.

Use of the index is not mandatory. Since you have only specified one condition - the time, it can choose to use some other index for the fetch. I would suggest that you use another condition for the select in the where clause or add an order by

order by  pair, price, time

CodePudding user response：

If that is really the query, then it needs an INDEX starting with the value tested in the WHERE:

INDEX(time)

Or, to make a "covering index":

INDEX(time, pair, price)

However, I suspect that most of your accesses involve pair? If so, then other queries may need

INDEX(pair, time)

especially if you as for a range of times.

To discuss various options further, please provide EXPLAIN SELECT ...

PDO, mysqli, phpmyadmin -- These all work the same way. (A possible exception deals with an implicit LIMIT on phpmyadmin.)

Try hard to avoid the use of FORCE INDEX -- what helps on today's query and dataset may hurt on tomorrow's.

When you see puzzling anomalies in timings, run the query twice. Caching may be the explanation.