How to build index in MYSQL

Databases
1

Index in mysql.

I have a table in mysql database which has a very large number of rows and can be expanded. Currently 300,000 rows and can reach million records in the future. users perform a searche on the table from a php page. I update it with new records once a week roughly and I also delete and add more records so it can reach very large number of records. Currently I am not using indexes and it is very slow when a search is made. I have a query that perform the search and it has a few conditions. I want to use indexes to optimise performance. below is the query style. I am just showing the structure using an example as the true values are not shown:

select value1, value2, value3,value4 from tablename where objectID like %searchstring%
If valuex == ‘x’
{sql = ‘AND valuex = $variablevalue’

else if valuey == y
{sql = ‘AND valuey = $variablevalue’

etc

To create an index would be on the where clause as the search is made on the objectID. correct?

but users can select other options from the drop down values as valuex or valuey. so what is the best way to create an index to speed up the sql search?

  1. As I will be updating the table once a week, how do i rebuild the indexes?

thanks in advance

2

a LIKE string with a leading wildcard will always result in a slow query

you can try to create an index on objectID and hope that this gives you an index scan instead of a table scan, but doing before & after EXPLAINs will soon resolve that question

the only other thing that might improve things is an index on valuex and/or valuey

3

do i create a separte index on ech of them? I mean create an index on ObjectID and then another index on valux etc?

What do you mean by EXPLAIN?

Thanks

4

an index on ObjectID, and a separate index on valuex, or alternatively a separate index on (valuex,valuey), or possible an additional separate index on valuey

–> EXPLAIN

5

Sounds a good option

Thanks

6

, I will be updating and deleting records weekly. How do I drop and rebuid the indexes:?
Is there a syntax for that? .

Secondly, is there a way of checking whether indexes are being used ? and how they improve perfromance?

thanks

7

Rudy linked up EXPLAIN syntax, you should read what it says on that link. They explained it all there.