0

I have a database table with 9254 rows in it.

These rows are all searched primarily using the "name" column on a website using php.

I was thinking of using an index on the name column to reduce load but I also have a "views" column that is updated whenever a name is searched on the front end.

So if someone does a SQL query of:

SELECT * FROM namestable WHERE name = "John Smith"

then an immediate second query is run:

UPDATE namestable SET views = views + 1 where name = "John Smith"

But also, on the result page for that query. This is then run as a seperate php file call (MySQL Full Text Search):

$sql = "SELECT name, id,
           MATCH (name,description) AGAINST ('$nameplus') AS score
      FROM namestable
      WHERE MATCH(name, description) AGAINST('$nameplus')
        AND name != '$name'
      group by name
      order by score desc LIMIT 8";

Would this directly impact indexing the name table as I read you aren't supposed to do it on rows that are frequently updated or that use MySQL Full Text Search.

I also have a page that calls almost every column in the database (to be used as front-end filters for results). So I'm honestly not sure if I should index each one of these columns as most of the content in them will remain static once set. The only one that updates constantly is the views column.

EDIT: Added more info.

2
  • 1
    Short answer: Yes May 4, 2018 at 19:33
  • it will help the first query, but you probably want some other type of index for the full-text search
    – Jasen
    May 4, 2018 at 23:33

0

Your Answer

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

Browse other questions tagged or ask your own question.