MySQL speed question..

I have a table that usually uses a unique ID to select data (several entries).



Quite simple. However from time to time the ID isn't present and I can use a word witch is stored as CHAR (length 22) to get the same data.

When I get the data that way the query can be something like this:


witch will return the same list.

Or I could use 2 queries:


The thing here is it uses 2 queries to do the same but it selects data using an ID (int) index. So whats prefered, using 2 queries (one to get the (int) ID and the other to select data using the ID) or simply use one query where the data is matched string-vise (the CHAR values isn't indexed).

If the first query using the word gets you the same row there is no reason to run the second. One thing to speed up the query is to do an index on the word field.

There isn't one row (or it can be), but more common would be 5-40. As for indexes it uses 2 already (for the two most common functions), can many indexes slow it down or would you say that it is ok to have more than 2 indexes in the same table?

If I am understanding correctly I would think you could always just do one query. In the cases where you have to lookup the id first, instead of doing two queries, how come you just couldn't write a modified query where you combine two tables (if you need to), or just change the query if its using the same table to search by keyword instead of id. All you need to do is use an if else statement to run different queries for if the ID is missing.

The main downside to indexes in my opinion would be if you are constantly writing to the database and changing values that would affect that index, versus reading from the database. Indexes speed up the retrieval of the information, but slow down inserts and deletes, as well as updates of values in the indexed columns. So as long as you are reading from the database considerably more than writing to it, indexes are generally a good thing if used correctly.

Sorry for all the guess work. I should have posted this directly, anyhow the table is structured like this:



Usually I use 'u_id' to grab information and its also common that I grab data using the 'unique_id', but now and then I have to grab it using the word thing(for usability). Several posts can have the same u_id (these posts also shares the same word), to be clearer: u_id stands for user_id.

The thing I was thinking about was, lets say we populate this list. Is it faster to extract just one u_id using the word thing and then match the remaining records using the u_id thing than to get them all with word to word matching?

The reason for my question is that I've heard that string matching is very slow compared to int (number) matching. However I guess there is also some processing in taking one value and then extract it and then go back in the database to get the rest, also thanks for the input. : )

If you are referring to creating a SQL statement that finds an ID by matching with a word, this is no different than finding all the lines that match that word. When you run the SQL statement that tries and match by that word it would have to search all of the records anyway to return you the list of all the u_id's that match that word. So it wouldn't make sense to parse just one of the values from the returned results when you already have them all available to you.

Not sure if I am missing something here, but if you searched by the word wouldn't you be getting a list of all the results you wanted anyway?

My bad - I have been hard to read/understand with my explanation, I was thinking something like this:



That should get just one value and then stop if I understand correctly. And then I could go on with u_id for selection.

Anyhow, I may have over thought this problem. Since the database is still not active enough to bring any delay to speak about using any of the techniques.

I guess there is nothing wrong with trying to foresee problems but maybe I should put focus else and get back and bump this if problems actually arise. Hmm.

Using the LIMIT of 1 may return one result, but the query itself is doing the same amount of work even as if you had no limit. To prove this I did a SELECT statement on one of my tables in my database:



and that returns one result from the table which has thousands and thousand of entries. Now if I do:



The result is:

id: 1
select_type: simple
table: table
type: ALL
possible keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 35416

The important part there is what it says for rows. The query still had to search through all 35416 records to return that one result. So that shows that using a LIMIT doesn't actually make the query anymore efficient.

wow, that's funny. I Guess from a performance aspect using a single query is the right thing to do then - thank you!