[[SOLVED] WHERE optimization: group uniques/indexes together first, or go by column order?]

Thanks, will try the Explain.

[[SOLVED] WHERE optimization: group uniques/indexes together first, or go by column order?]

Let's say my table has lots of columns, and every 5th column is an index.

Then let's say I need results from severl indexes, and also from column #2 (which is not an index).

 

Is it better to group all the indexes at teh front of my WHERE, or should I just go in order of the table's columns?

To illustrate:

 

WHERE index_column_1 = whatever

AND index_column_5 = whatever

AND index_column_10 = whatever

AND column_2 = whatever

 

versus:

 

WHERE index_column_1 = whatever

AND column_2 = whatever

AND index_column_5 = whatever

AND index_column_10 = whatever

 

Thanks

[Are these two INNER JOINS the same?]

I'm exploring inner joins. This guy's wordpress plugin code has an inner join like this:

FROM (((($wpdb->posts INNER JOIN $wpdb->term_relationships ON $wpdb->posts.ID = $wpdb->term_relationships.object_id)
INNER JOIN $wpdb->term_taxonomy ON $wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
INNER JOIN $wpdb->terms ON $wpdb->term_taxonomy.term_id = $wpdb->terms.term_id))

 

So I'm wondering if that's the same as:

FROM $wpdb->posts
INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)

 

thx

[Please point in the right direction regarding comment text in a blog]

That's really close - however the formatting shows up along with the text - stuff like line breaks, new paragraph symbol, etc. Is there a way to do this while allowing the html to render?

 

Thanks

[Please point in the right direction regarding comment text in a blog]

Sometimes a blog post's comment will be too long. I want to enforce a maximum character limit, and if the comment exceeds this then it is presented with a slider so you can still read the whole comment, but the comment won't take up too much space. You know, like how your browser deals with long webpages.

 

I don't think iframes are suitable here, so how could I do this? I don't need specifics, just the general concept and proper buzzwords. I can look it up myself.

Thanks

 

On re-reading this, I realize this is not necessarily a php question (although my site is written in php). Sorry about that.

[[SOLVED] What's: WHERE 1=1 for?]

Thanks fen

[[SOLVED] What's: WHERE 1=1 for?]

So why bother?

Why not just SELECT * FROM TABLE?

[[SOLVED] What's: WHERE 1=1 for?]

Sometimes in practice I see SELECT * FROM TABLE WHERE 1=1

What's that 1=1 for?

thx

 

[Faster: count(*) or count(column)?]

When getting a count of all entries that fulfill whatever criteria, which is faster:

select count(*) or select count(column_name_that_is_a_primary_index)

 

Thanks

[[SOLVED] How to use %s ("string placeholder")]

Thanks DW

[[SOLVED] How to use %s ("string placeholder")]

What does it mean when an example has %s and they say the %s is a string placeholder?

Is that an actual php function or are they just using some kind of literary shorthand?

If it is a php thing, how do I use it?

thanks

[Best method to check if a column exists, and if not, add it]

I want to add a column to an existing table, but first I have to check if the column exists. I can think of several ways to check:

CASE WHEN EXISTS

SHOW COLUMNS

mysql_list_fields

mysql_num_fields

mysql_field_name

 

But I was wondering in your experience, what is the simplest most efficient way to check if a column exists, if not, then add it to the table?

thanks

[Inserting the wrong number in a column of specific numeric type..]

My concern is whether or not there is a setting that will halt all rendering and output a mysql error message to the visitor. Because if that's a possiblity, then I'll have to include the code to manually limit the counter from incrementing if it's at 255 already.

 

 

[[SOLVED] Example of what the number in parenthesis after data type?]

Alright ty

[[SOLVED] Example of what the number in parenthesis after data type?]

When learning creating tables, I see stuff like INT(11)... what's that paranthesis'd number do? I looked it up on mysql's site but I don't understand:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.

 

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

 

That last sentence especially is what I don't understand. Could someone show some examples? Thanks

[Inserting the wrong number in a column of specific numeric type..]

Okay, I got a tinyint unsigned column, which means the highest number you could put in there is 255.

 

Is there a 'mode' that would throw an error on the screen if your php tries to insert a value higher than 255?

 

This thing that I'm writing has a counter which I don't want to able to run up indefinitely. It's not difficult to include the extra code to manually limit the count, but I don't want to if I don't have to, since there is already a built-in sort of limiter by using specific numeric types.

 

I plan on giving my code to a lot of people for their sites, so it'll be too hard to find out what mysql mode everybody's host is set on.

 

thanks

 

 

[[SOLVED] Best data type to store a 1 or a 0 in a field?]

Enum is neat! Thanks

[ALTER TABLE, ORDER BY,.. keep all columns intact except 1...]

I'm still learning mysql and the hard part is wrapping my head around all the nuances - every practice problem is so specific to the given example story that if I want to try variations, I'm lost because I don't know mysql's limitations and abilities.

[[SOLVED] Best data type to store a 1 or a 0 in a field?]

In my rows there's an on/off field. If data for that row is to be considered "On" or enabled, then it's cell in the 'On' column gets a value of 1. If it's "off" then it gets a value of 0.

 

Whats the best data type to store these 1's and 0's? I have it on tinyint, but even that is still too big.

thanks

[ALTER TABLE, ORDER BY,.. keep all columns intact except 1...]

Let's say:

Primary Key | UID's | Additional Info 1 |  Additional Info 2

1 | 5 | foo5 | bar5

2 | 3 | foo3 | bar3

3 | 4 | foo4 | bar4

 

Primary Key is autoincrement

UID is non-sequential, but unique numbers

 

How do I sort this table so that UID is sequential again (lowest to highest) while keeping their associations with additional info 1,2, and keeping the primary keys where they are?

1 | 3 | foo3 | bar3

2 | 4 | foo4 | bar4

3 | 5 | foo5 | bar5

 

Thanks

[[SOLVED] Filling in row gaps while autoincremented]

Okay guys, I'll add another column and make it an index.

[[SOLVED] Filling in row gaps while autoincremented]

Why would I need another index?

[[SOLVED] Filling in row gaps while autoincremented]

My primary key is unique id numbers, but because of the nature of my php scripts, they get inserted out of order. I assume that this slows down queries, since the engine has to search the entire db to find a specific primary key. But if it was autoincremented, it would know exactly where to look... Right?

 

But my problem is that not every sequential number gets used... there's lots of gaps because the script will not re-use id numbers that belong to the data entries that get deleted before making it into the db.

 

So if I have lets say, 10 entries in the db, # 1-10, and then I need to add a new row at the end of the table whose id is 15... will mysql with autoincrementing turned on, create rows #11-14? Or do I have to do it myself?

 

If i have to do it myself, do I do a select on rows 11-14, to see if they exist yet? Or is there something more efficient?

thanks

[[SOLVED] updating the same column in multiple rows...]

Thanks

[[SOLVED] updating the same column in multiple rows...]

Lets say I want to increment column "totals" in several different rows:

 

UPDATE mytable SET totals=totals+1 WHERE id=1 OR id=3 OR id=7 OR (dozens more...)

 

Is this the fastest syntax? Or is CASE WHEN THEN better? Or is there an even better way?

 

Thanks

 

By the way, I can't update every row's 'totals' column, only specific rows.