0

We have a SaaS Application site, where we would have over 2M records in a table.

Server version: 8.0.19 - MySQL Community Server - GPL
Protocol version: 10

Even with the indexes these queries are slow.

My Question:

  1. Our Database query response in very slow in our PHP User Table Page. takes more than 4-5 mins to fetch the data. How can we improve the response time.
  2. Our login page also respond slow to authenticate the user? How can we improve the performance?
  3. Isn't our server good enough. Here is our specs (Dedicated Server XL6 managed. CPU. AMD Hexa-Core. 6 Cores x 2.8 GHz. (3.3 GHz Turbo Core). RAM. 16 GB. DDR3 ECC. HDD. 1,000 GB (2 x 1,000 SATA))

4.Would separating this table/data unto another database on a server that has enough ram to store this data in memory would this speed up these queries? Is there anything in anyway that the tables/indexes are set up that we can improve upon to make these queries faster?

Table Structure

MySQL Table Information

Data    102.9   MiB
Index   10.2    MiB
Overhead    380 B
Effective   113.1   MiB
Total   113.1   MiB

Row statistics
Format  dynamic
Collation   latin1_swedish_ci
Rows    1,034,964
Row length  104 B
Row size    115 B
Improve this question
8
  • 1
    Show table's structure as complete CREATE TABLE script, not as screenshot. Show problematic query text and its execution plan. Specify precise MySQL version.
    – Akina
    Sep 9, 2021 at 10:03
  • where we would have over 2M records in a table, but later Rows 1,034,964 ???
    – Akina
    Sep 9, 2021 at 10:05
  • @Akina - Will Add Create Table script and in the same table we would have 2M records, right we imported 1,034,964 records and stopped as the response time is slow.
    – GISKeeda
    Sep 9, 2021 at 10:11
  • How do your users authenticate(username, userid or other column)? Is that column indexed? What response times do you expect and how many rows do you expect to return?
    – user953
    Sep 9, 2021 at 10:26
  • 1
    1. "select ": Never use this in Production code. 2. "select * from user_tbl": What is your "dashboard" going to do with *2M rows?? It would take ages just to display it and nobody in their right mind is going to scroll through that many rows, looking for a particular User. You need to rethink this. 3. "select * from user_tbl where ...": Are email_id and mobile indexed? Please tell me you're /not/ storing password in plain text! You are wide open to SQL Injection attacks. Read up about PDO's Prepared Statements.
    – Phill W.
    Sep 9, 2021 at 14:26

1 Answer 1

Reset to default
0

OR is un-optimizable.

SELECT*FROM user_tbl
    where  (EMAIL_ID = '$username'
              OR  MOBILE = '$username'
           )
      AND  PWD = '$pwd'

-->

SELECT * FROM user_tbl
    where  EMAIL_ID = '$username'
      AND  PWD = '$pwd' 
UNION DISTINCT
SELECT * FROM user_tbl
    where  MOBILE = '$username'
      AND  PWD = '$pwd'

and have these two separate indexes:

INDEX(email_id),
INDEX(mobile)

Meanwhile, SELECT * FROM user_tbl cannot be sped up. Do you really need to show all the 1,034,964 rows and show all the columns? Elaborate! (See Phill's Comment.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.