Please Help Me Optimize a Table

Hi,

I've MySQL Db with single table with following fields:

userid: int (data is in format: 447520)
query: varchar (300) (general web queries)
datetime varchar (20) (format: 5/18/2006 9:21)
position: int (format: 425)
clickurl: varchar (100) (format: web urls)

There are 37.5 million rows in the table and it is tanking 3-5 minutes to execute simple select queries. Please let me know what changes should I do to the table and/or DB to make it execute queries as fast as possible.

Also please let me know how should the data in the given format be inserted into table so as to make sense or considered as date/time.

Thanks.

please do a SHOW CREATE TABLE so that we can see the indexes

please show example of query along with the EXPLAIN output

Hi r937, currently there is no index in the table and I want suggestion related to that. Simple select query:


SELECT query from data_table where userid = '43211';


Thanks.

for that query, you would want to have an index on userid

I also need to run query like:


Select * from datatable where query = 'time to go crazy';

OR

Select * from datatable where query like '%hot cakes%';


Since query field is too big how can I add index to it?

Thanks.

300 characters isn't too big to add an index to the column.

Any searches with % at the beginning of a search term won't use an index.

change your varchar type for your datetime column to an actual datetime type, note you are best to create a new column, reformat the data into the new column. Without doing that you can't run any date or time functions on that column. that will slow you down.

Hi Guelphdad, can you please help me with the code to create a table with all that, including conversion to date/time, thanks for your reply.

First run an ALTER TABLE statement to change the table, adding the new column:



now run an update



verify by looking at your data to see that worked correctly, if so drop the old column:

Guelphdad I will try your code and let you know of the outcome, thanks.

Just to clarify, it will use an index but it won't use it in a good way since it needs to scan and compare all records.

But then again, scanning an index that only contains the data of one column is usually much better than scanning the entire table that contains the data of all columns.