MySQL indexing has no speed effect through PHP but does on PhpMyAdmin

user_10293907 0
Viewed 60

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...)

mariadb indexing mysql
3 Answers

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.

user_17668310

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
user_47794720

I ended up creating another index (just for the time column) and it did the trick, running at ~0.002s now. Setting the LIMIT clause had no effect since I was always getting 423 rows (for 423 coin pairs).

Bottom line, I probably needed a more specific index, although the weird part is that the first index worked great on PMA but not through PHP, but the second one now applies to both approaches.

Thank you all for the kind replies :)

user_102939070
Related