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…)
3
Answers
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 :)
The mysql documenation says
MariaDB documentation Force Index here says this
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
If that is really the query, then it needs an
INDEX
starting with the value tested in theWHERE
:Or, to make a "covering index":
However, I suspect that most of your accesses involve
pair
? If so, then other queries may needespecially 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.