Our server gets slow, "To get data from mysql database". So I search for it on google. They told me, "Use INDEX for the select query to get data from the database it becomes more fastest execution".
Asked
Active
Viewed 1,863 times
-2
-
completely true. Thanks for the information. – Conffusion Sep 05 '19 at 09:12
-
enable slow_log, run explain on the queries and make sensable indexes for those queries or maybe a query rewite is even needed.. But keep in mind adding a index does not force MySQL into using it as the optimizer is costs based.. Don't just blindly add indexes as redundent indexes might confuse the optimizer into using a wrong index and worse performance.. – Raymond Nijland Sep 05 '19 at 09:25
-
maybe u can have a look on this https://stackoverflow.com/a/29842947/11930311 – MannersW Sep 05 '19 at 09:31
-
I have executed this query "CREATE INDEX identifier_idx ON room_info(identifier);" in my local system, [ it's affected 0 rows ]. But does not create any INDEX on it. – sharath3599201 Sep 05 '19 at 09:39
1 Answers
0
Index is a small copy of a database table sorted by key values.
U need to create index first.
CREATE INDEX index_name ON table_name(column_name)
Then:
SELECT * FROM table_name
USE INDEX (index_name)
WHERE condition;
MannersW
- 93
- 9
-
Seams like SQL Server (MSSQL) code as `WITH (INDEX(index_name))` is not valid MySQL – Raymond Nijland Sep 05 '19 at 09:18
-
@MannersW Thank you. Is it work for the existing table in MySQL? – sharath3599201 Sep 05 '19 at 09:20
-
@sharath3599201 the second query in his answer will not work in MySQL as that seams to be SQL Server (MSSQL) syntax – Raymond Nijland Sep 05 '19 at 09:22
-
`USE INDEX (index_name)` forcing MySQL into using a index is generally a bad idea.. – Raymond Nijland Sep 05 '19 at 09:24
-
Sorry for that, I doesn't notice is MySQL, I had UPDATE my answer for MySQL. You can have a try on it. – MannersW Sep 05 '19 at 09:24
-
@Raymond Nijland is it "CREATE INDEX" query works for the existing table in MYSQL? – sharath3599201 Sep 05 '19 at 09:26