1

I've a coldfusion application that currently contains just under 5,000 records on one table. I've a query that selects around 3800 records from that table. There are several queries that retrieve over 1500 records at a time The table grows by about 600-700 records per year. At present, selecting 3800 records takes about 30 seconds.

MySQL is running on Windows server with IIS, CF10, MySQL 8.0.25. The Server is 10900X CPU, 32GB 3200CL16 DDR4, 150TB storage, connected over the network via 10Gbe infrastructure.

The select is basic

select * from games where gtype='#dtype#'

I could select fewer columns, but most of them are displayed on the resultant output

Is there anything I can do to speed up queries? Soon I suspect I will start getting "time exceeded" errors.

Improve this question
6
  • 2
    do you have index on gtype column ? if not create the one & problem is solved.
    – JYOTI RAJAI
    Aug 4 at 6:54
  • 2
    Please post the output of explain select * from games where gtype='#dtype#' and show create table games
    – Ergest Basha
    Aug 4 at 10:34
  • For MySQL, you should check if an INDEX is present on the gtype column, if not, add it as this could be an easy win but... you should also monitor the IO usages of your storage servers, as even without an INDEX, 3800 records seems very low. Is the storage SSD/NVMe/... ? RAID configuration? You should probably check if there is a bottleneck on your hardware, as my MySQL is able to select 16k+ rows in less than 2 seconds without using INDEX...
    – DarkCenobyte
    Aug 4 at 16:18
  • "3800 records takes about 30 seconds" -- No way! Something is interfering with this activity? There is something strange about this table? The "#" means something? Please provide SHOW TABLE STATUS.
    – Rick James
    Aug 4 at 19:11
  • Please post TEXT results of SHOW FULL PROCESSLIST; while your query is running.
    – Wilson Hauck
    Aug 4 at 23:21

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.